Oracle 和 Mysql 数据库转换

发表于 2019-12-30 阅读次数：阅读次数：本文字数： 7.1k 阅读时长 ≈ 6 分钟

前言

我们的项目一直使用的是 SSH 框架 + mysql+tomcat 服务器，但是最近一个新项目对数据库的使用和 web 服务器提出了硬性要求，按照他们的要求，需要使用 Oracle 数据库，并采用 Weblogic 进行服务部署。本文针对本次数据库转换（Mysql 转换 Oracle）过程中遇到的一些问题做记录，对于部署服务器的更换（tomcat 更换为 Weblogic）另一文章将继续分享。

问题分析

首先，我们系统的架构情况如下：

开发环境：
MAVEN:3.5
JDK:1.8
Spring: 4.3.9.RELEASE
hibernate： 4.3.11.Final
Shiro：1.4.0

开发工具:
MySql: 5.7.x
ORACLE: 11.2.0.1.0
Tomcat: 9.0
EZDML:2.06 (表结构设计器)

分析了一下部分代码，发现 Mysql 转换至 Oracle，整体框架无需大的改动，由于使用了 hibernate 框架做持久层，业务操作基本都是 HQL, 所以无需做单独处理，需要变动的主要涉及到数据库结构的转换和一些公共自定义 sql 的兼容性处理，明确了解决问题的要点，现在开始着手。

数据库转换

数据库结构

首先，使用 EZDML 表结构设计器进行数据库的整体转换

1、打开 EZDML，点击模型，选择导入数据库：

2、点击确定，选择需要转换的表名，确定之后就可以看到对应的表结构模型；
3、在左边单击表明，就可以看到详细的表结构设计，选择生成，就可以看到不同类型数据库对应的建表 DDL。

然后，对表结构进行微调

Mysql 和 Oracle 之间的数据类型转换

MySQL Data Type	Oracle Data Type
BIGINT	NUMBER(19, 0)
BIT	RAW
BLOB	BLOB, RAW
CHAR	CHAR
DATE	DATE
DATETIME	DATE
DECIMAL	FLOAT (24)
DOUBLE	FLOAT (24)
DOUBLE PRECISION	FLOAT (24)
ENUM	VARCHAR2
FLOAT	FLOAT
INT	NUMBER(10, 0)
INTEGER	NUMBER(10, 0)
LONGBLOB	BLOB, RAW
LONGTEXT	CLOB, RAW
MEDIUMBLOB	BLOB, RAW
MEDIUMINT	NUMBER(7, 0)
MEDIUMTEXT	CLOB, RAW
NUMERIC	NUMBER
REAL	FLOAT (24)
SET	VARCHAR2
SMALLINT	NUMBER(5, 0)
TEXT	VARCHAR2, CLOB
TIME	DATE
TIMESTAMP	DATE
TINYBLOB	RAW
TINYINT	NUMBER(3, 0)
TINYTEXT	VARCHAR2
VARCHAR	VARCHAR2, CLOB
YEAR	NUMBER

自增序列处理

MySQL Data Type	Oracle Data Type
有自动增长的数据类型，插入记录时不用操作此字段，会自动获得数据值	ORACLE 没有自动增长的数据类型，需要建立一个自动增长的序列号，插入记录时要把序列号的下一个值赋于此字段

Mysql 自动增长的数据类型

create table table_name
(
    ID int auto_increment -- 自增长
        primary key
);

Oracle 自动增长的数据类型

create sequence table_name_pk
minvalue 1
maxvalue 9999999999999999999999999999
start with 447
increment by 1
cache 20;

create table table_name
(
	ID int generated as identity
		constraint table_name_pk
			primary key
)
/

长字符串处理

在 ORACLE 中，进行 INSERT 和 UPDATE 时，最大可操作的字符串长度必须小于等于 4000 个单字节。如果超出这个长度，要插入更长的字符串数据，可以考虑使用 CLOB 类型。在本项目转换过程中，使用到了指纹数据的 16 进制字符串的存取，长度超过 4000，在 Mysql 中使用 BLOB 类型存储没问题，但是 ORACLE 下不可以。BLOB 全称为二进制大型对象（Binary Large Object)，它用于存储数据库中的大型二进制对象，原来在 ORACLE 下，对二进制对象有严格要求，所以采用 CLOB 类型存储。

日期字段处理

在 MYSQL 中，日期字段可以分 DATE（包含年月日）和 DATETIME（包含年月日时分秒）两种类型，而 ORACLE 日期字段只有 DATE（包含年月日时分秒）一种类型。所以在实际业务使用过程中需要进行格式转换。

比较项	MySQL Data Type	Oracle Data Type
数据类型	DATE（包含年月日）、 DATETIME（包含年月日时分秒）	DATE（包含年月日时分秒）
日期格式	% Y：代表 4 位的年份 % y：代表 2 为的年份 % m：代表月，格式为 (01……12) % c：代表月，格式为 (1……12) % d：代表月份中的天数，格式为 (00……31) % e：代表月份中的天数，格式为 (0……31) % H：代表小时，格式为 (00……23) % k：代表小时，格式为 (0……23) % h：代表小时，格式为 (01……12) % I：代表小时，格式为 (01……12) % l ：代表小时，格式为 (1……12) % i：代表分钟，格式为 (00……59) % r：代表时间，格式为 12 小时 (hh:mm:ss [AP] M) % T：代表时间，格式为 24 小时 (hh:mm:ss) % S：代表秒，格式为 (00……59) % s：代表秒，格式为 (00……59)	YYYY、YYY、YY 分别代表 4 位、3 位、2 位的数字年 MM 数字月 DD 数字日 AM 表示上午或者下午 HH24、HH12 代表 24 小时制或 12 小时制 MI 分钟 SS 秒钟
当前日期	sysdate()、current_date、current_time	sysdate、current_date、current_timestamp
日期和字符互转	DATE_FORMAT 例如：DATE_FORMAT (sysdate (),’% Y-% m-% d % H:% i:% s’) STR_TO_DATE 例如：STR_TO_DATE (‘2019-12-30 19:25:34’,’% Y-% m-% d % H:% i:% s’)	TO_CHAR 例如：TO_CHAR (sysdate,’YYYY-MM-DD HH24:MI:SS’) TO_DATE 例如：TO_DATE (‘2019-12-30 19:25:34’,’YYYY-MM-DD HH24:MI:SS’)
日期 / 时间增减	增减一小时： date_sub(createDate, interval -1 hour) date_sub(createDate, interval 1 hour) 增减一天： date_sub(createDate, interval -1 day) date_sub(createDate, interval 1 day) 增减一月： date_sub(createDate, interval -1 month) date_sub(createDate, interval 1 month) 增减一季度： date_sub(createDate, interval -3 month) date_sub(createDate, interval 3 month) 增减一年： date_sub(createDate, interval -1 year) date_sub(createDate, interval 1 year)	增减一小时： createDate+1/24 createDate-1/24 增减一天： createDate+1 createDate-1 增减一月： add_months(createDate, 1) add_months(createDate, -1) 增减一季度： add_months(createDate, 3) add_months(createDate, -3) 增减一年： add_months(createDate, 12) add_months(createDate, -12)
日期 / 时间比较	①直接比较 ②转成 unix 时间戳比较 ③转换为日期类型比较	①直接比较 ②转换为日期类型比较

日期 / 时间比较

-- Mysql
-- 直接比较（此种方式不走索引，一定程度上会降低性能）
select sysdate() from dual where '2019-12-30 19:39:05' > '2019-12-30 17:39:05';
-- 用unix_timestamp函数，将字符型的时间，转成unix时间戳
select sysdate() from dual where unix_timestamp('2019-12-30 19:39:05') > unix_timestamp('2019-12-30 17:39:05');
-- 将字符串转换为相同格式相同进制的日期类型
select sysdate() from dual where str_to_date('2019-12-30 19:39:05','%Y-%m-%d %H:%i:%s') > str_to_date('2019-12-30 17:39:05','%Y-%m-%d %H:%i:%s');

-- Oracle
-- 直接比较
select sysdate from dual where '2019-12-30 19:39:05' > '2019-12-30 17:39:05';
-- 将字符串转换为相同格式相同进制的日期类型
select  sysdate from dual where to_date('2019-12-30 19:39:05','yyyy-mm-dd hh24:mi:ss') > to_date('2019-12-30 17:39:05','yyyy-mm-dd hh24:mi:ss');

返回 Map 列名大小写问题

转换过程发现，使用 Hibernate 返回实体类型的数据我们可以不用关注列名大小写问题，但是返回 Map 类型的数据集合时，对 Mysql 和 Oracle 返回的 Map 的 key 大小写是不一致的，也就是返回列名大小写不统一，对于两数据库不同的系统环境列名大小写情况如下：

系统	MySQL Data Type	Oracle Data Type
Windows	默认都不区分大小写，可通过修改配置来区分大小写： lower_case_table_names = 0（0：区分大小写，1：不区分大小写）	1、在 Oracle 中，如果字段名称被双引号（””）包裹，Oracle 会区分大小写； 2、如果字段名称没有被双引号（””）包裹，则全部转换成大写来执行。 3、如果表结构设计时，字段名称使用了数据库的保留字，SQL 中的字段名称必须用双引号（””）包裹，以避免 SQL 语句执行出错。
Linux	1、数据库名与表名是严格区分大小写的； 2、表的别名是严格区分大小写的； 3、列名与列的别名在所有的情况下均是忽略大小写的； 4、变量名也是严格区分大小写的；	同 Windows

根据以上，可以看出，我们要解决大小写问题，需要对返回列表的字段名加引号（””），这种做法工作量过大，于是查看了一下返回 Map 集合的接口实现，如下：

public List<Map> findMapResultBySql(String sql) {
    SQLQuery query = this.getCurrentSession().createSQLQuery(sql);
    query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
    return query.list();
}

很容易可以看出，处理结果集返回的关键是 Transformers.ALIAS_TO_ENTITY_MAP，查看源码

AliasToEntityMapResultTransformer.java

public class AliasToEntityMapResultTransformer extends AliasedTupleSubsetResultTransformer {

	public static final AliasToEntityMapResultTransformer INSTANCE = new AliasToEntityMapResultTransformer();

	/**
	 * Disallow instantiation of AliasToEntityMapResultTransformer.
	 */
	private AliasToEntityMapResultTransformer() {
	}

	@Override
	public Object transformTuple(Object[] tuple, String[] aliases) {
		Map result = new HashMap(tuple.length);
		for ( int i=0; i<tuple.length; i++ ) {
			String alias = aliases[i];
			if ( alias!=null ) {
				result.put( alias, tuple[i] );
			}
		}
		return result;
	}

	@Override
	public boolean isTransformedValueATupleElement(String[] aliases, int tupleLength) {
		return false;
	}

	/**
	 * Serialization hook for ensuring singleton uniqueing.
	 *
	 * @return The singleton instance : {@link #INSTANCE}
	 */
	private Object readResolve() {
		return INSTANCE;
	}
}

可以看出，AliasToEntityMapResultTransformer 类的 transformTuple 方法即是解决问题的关键，于是自定义自己的返回结果集处理工具，如下：

/**
 * 返回map统一小写
 * @author sunys
 */
public class CustomResultTransformer extends AliasedTupleSubsetResultTransformer {
    public static final CustomResultTransformer INSTANCE = new CustomResultTransformer();
    private CustomResultTransformer() {

    @Override
    public Object transformTuple(Object[] tuple, String[] aliases) {
        Map result = new HashMap(tuple.length);
        for ( int i=0; i<tuple.length; i++ ) {
            String alias = aliases[i];
            if ( alias!=null ) {
                //将Map的key转为小写返回
                result.put( alias.toLowerCase(), tuple[i] );
            }
        }
        return result;
    }
    @Override
    public boolean isTransformedValueATupleElement(String[] aliases, int tupleLength) {
        return false;
    }
    private Object readResolve() {
        return INSTANCE;
    }
}

然后设置 SQLQuery 的 ResultTransformer，如下：


public static final CustomResultTransformer ALIAS_TO_ENTITY_LOWERCASE_MAP =
			CustomResultTransformer.INSTANCE;

public List<Map> findMapResultBySql(String sql) {
    SQLQuery query = this.getCurrentSession().createSQLQuery(sql);
    query.setResultTransformer(ALIAS_TO_ENTITY_LOWERCASE_MAP);
    return query.list();
}

至此，Oracle 和 Mysql 数据库返回 Map 列名统一大小写问题得以解决。