oracle迁移mysql问题总结

Oracle和Mysql的常用字段转换关系如下表所示: 字段类型 Oracle字段 最大长度 以十进制格式存储，最大精度为38位，长度为0-22字节。 4000字节 占用空间7-11个字节，小数秒最高精度可达9位 2000字节 字段类型 tinyint 整数类型 smallint mediumint INT BIGINT 变长字符串 日期 定长字符串 大字符串 CHAR VARCHAR2 TIMESTAMP VARCHAR DATETIME NUMBER MySQL字段 最大长度 1字节，默认为tinyint(4) 2字节，默认为smallint(6) 3字节，默认为mediumint(9) 4字节，默认为int(11) 8字节，默认为bigint(20) 65533字节(可空时减1) 8字节，可表示日期和时间 支持[1000-01-01 00:00:00]至[9999-12-31 23:59:59] 255字节(可空时减1) 255字节 CHAR TINYTEXT CLOB 4G个数据块，数据块大小为2-32KB TEXT 65535字节 MEDIUMTEXT 16MB LONGTEXT TINYBLOB BLOB MEDIUMBLOB LONGBLOB YEAR FLOAT DOUBLE DECIMAL 4GB 255字节 65535字节 16MB 4GB 1字节，支持到1901-2155 4字节 8字节 MySQL 5.1引入，声明语法是DECIMAL(M,D)，占据M+2个字节，M取值1到65，默认是10 大二进BLOB 制文本 4G个数据块，数据块大小为2-32KB 年份 浮点数 无对应字段 BINARY_FLOAT BINARY_DOUBLE DECIMAL 用Number代替 Oracle10g后支持，可用Number代替 Oracle10g后支持，可用Number代替 底层使用Number实现 定点数，无精度损失 2. 主键自增问题

对于主键的自动增长，Oracle采用序列（Sequence）来实现，常见的使用语法如下： insert into test_table (ID,type,name,tips, value) values (seq_test_table.nextval, # type #, #name#, #tips#, # value #) ]]>

对于Mysql，主键的自增可以在需要自增的字段上设AUTO_INCREMENT属性来实现，在建表时可以指定该自增属性，语法如下：

CREATE TABLE `test_table` (

`ID` bigint(16) NOT NULL AUTO_INCREMENT COMMENT '主键', ……

PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=gbk COMMENT='中间账户配置表';

3. 函数兼容问题

Oracle和Mysql中的常用函数转换表如下： Oracle函数 函数名称 sysdate abs(x) floor(x) CEIL(x) ln(x) log(x,y) power(x,y) dbms_random.value(x,y) to_char(date,’format’) to_date(str,’formate’) date + n date1 – date2 nvl() 函数描述 系统当前时间 绝对值 返回小于或等于x的最大整数 返回大于或等于x的最小整数 返回自然对数 返回以x为底y的对数 X的y次方 X到y的随机数 时间格式化 字符串To时间 日期加n 日期间隔 判断null 函数名称 now() abs(x) floor(x) CEILING(x) log(x) log10(x) square(x) rand() MySQL函数 函数描述 系统当前时间 绝对值 返回小于或等于x的最大整数 返回大于或等于x的最小整数 返回自然对数 返回以10位底对数 平方 取0到1之间的随机数 date_format(date,’format’) 时间格式化 字符串to时间 str_to_date(str,’formate’) date_add(date, n) Datediff(date1,date2) ifnull 日期+n 获取日期间隔 判断null 4. 汉字排序问题

Oracle中针对汉字进行排序的函数nlssort，支持按照拼音、部首和笔画进行排序，具体使用方法如下：

拼音 ORDER BY NLSSORT(队名,'NLS_SORT = SCHINESE_PINYIN_M') 笔划 ORDER BY NLSSORT(队名,'NLS_SORT = SCHINESE_STROKE_M') 部首 ORDER BY NLSSORT(队名,'NLS_SORT = SCHINESE_RADICAL_M')

Mysql中如果采用GBK编码格式的话，默认对汉字采用按拼音的排序方式。

5. 分页查询兼容问题

Oracle的分页查询语句与mysql有很大区别，Oracle分页用到了rowid和rownum，rowid是物理存在的，表示记录在表空间中的唯一位置ID，在DB中是唯一的；rownum物理上不存在，是对查出来的结果集加上的一个序号，序号从1开始；

Oracle的分页查询形式如下：

select * from (select T1.*, rownum linenum from ( select * from tablename ) T1 where rownum <= #endRow#) T2 where linenum >= #startRow#

Mysql的分页查询形式如下：

select * from tablename limit startoffset,pagesize

需要注意的是，mysql的startoffset索引从0开始，oracle的rownum索引从1开始。

6. Mysql的读写库问题

6.1 数据访问延迟问题

在使用读写分离的mysql数据库集群的模式下，写操作发生在master主库中，读操作会根据特定的路由规则路由到相应的slaver库上。由于写到主库的数据同步到从库存在一定的延迟，因此在基于插入操作的基础上进行查询验证的操作会出现结果不确定的情况。如下面的测试代码所示：

//构造EbppInputFieldDO实例

EbppInputFieldDO ebppInputFieldDO = createEbppInputFieldDO(); EbppInputFieldDO ebppInputFieldDO_insert = null; //执行插入

ebppInputFieldDAO.insertField(ebppInputFieldDO);

//根据插入的(bizType, subBizType, chargeInst, chargeoffInst, fieldName)查询 ebppInputFieldDO_insert = ebppInputFieldDAO.selectFieldName(

ebppInputFieldDO.getBizType(), ebppInputFieldDO.getSubBizType(), ebppInputFieldDO.getChargeInst(),ebppInputFieldDO.getChargeoffInst(), ebppInputFieldDO.getFieldName()); //判断非空

assertNotNull(ebppInputFieldDO_insert);

assertNotNull(ebppInputFieldDAO.selectById(ebppInputFieldDO_insert.getId())); //执行删除

ebppInputFieldDAO.deleteField(ebppInputFieldDO_insert.getId());

//判断为空

assertNull(ebppInputFieldDAO.selectById(ebppInputFieldDO_insert.getId()));

该测试用例会出现时而通过时而不通过的情形，出现这种问题的原因就在于新插入的数据从主库被正确同步到从库会有一定的延迟，所以会出现插入成功但是查询不到数据、删除成功却还能查询到数据的情况。

6.2 插入操作返回自增主键的问题

根据业务的需要，数据库插入操作有时候需要返回插入数据的ID，并且执行插入的数据库表的主键是自增的；在Mysql中的实现方式是，在插入操作后再执行一次查询，Ibatis中的语法如下：

Insert into test_table (PARAM_NAME,PARAM_VALUE,PARAM_DESC) values

(#paramName#, #paramValue#, #paramDesc#)

]]>

该方案可以正常工作有两个前提：

(1) 插入和查询操作使用的是同一个数据库连接 (2) 连接的是同一个数据库 否则，返回的值始终是0。