数据库迁移-从Oracle到MySQL

背景介绍

近期项目要从Oracle数据库向MySQL数据库迁移，本文档整理了项目中常规用到的SQL，及在Oracle与MySQL环境下的SQL语言差异，并对如何解决差异性问题，进行了探索。

本文档所比较的数据库版本，Oracle为 11g，MySQL为5.5。

差异比对

MySQL与Oracle的差异性，详列如下。

SQL基本语法差异

MySQL的SQL语句中，要求表名、表的别名必须区分大小写（可通过修改mysql配置文件my.cnf，增加lower_case_table_names=1来忽略大小写，从而与Oracle尽量保持一致），尤其是表的别名。建议通过组内开发规范统一起来，以消除差异性。

MySQL的SQL语句，允许子查询，但From处的子查询语句必须有别名。例如：

select t.r from (select now() r) AS t;

MySQL的Insert语句，支持ignore语法忽略主键冲突报错，如：insert ignore into table_name...；支持insert into ... on duplicate key update...的用法，此时要求表必须有主键或唯一性约束字段。

与insert语句类似，MySQL还支持replace into语句，若插入表中已存在数据时，则用新数据记录替换旧的记录。

MySQL的 Update 和 Delete语句，支持表关联查询，并同时更新多张表的数据。例如：

update table tab_a a, tab_b b set a.val = ?, b.val = ?
where a.id = b.id and b.id <=3;

delete a, b from tab_a a, tab_b b
where a.id = b.id and b.id=3;

建表语句差异

创建表时，MySQL不支持 create or replace，但可以通过drop table if exists tbl_name来重新创建表。

表和字段的comment语句存在差异。Oracle的comment语句：

comment on table table_name is '';
comment on column table_name.column_name is '';

但MySQL的Comment没有从建表语句中剥离出来，如下所示：

alter table table_name comment '';
alter table table_name modify column_name varchar(200) not null comment '';

字段modify时，需要带上改字段在建表时的完整的类型定义。

另外，在建表语句、建索引语句中，MySQL不支持tablespace后缀。此时，可以将Oracle导出的DDL语句中，tablespace部分注释掉；同时，在导入 MySQL数据库时，先执行set @@sql_mode='oracle'以尽量保持与Oracle语法一致。具体sql_mode，可参考MySQL官方手册等文档。

除以上外，建表语句中的字段类型，如clob, timestamp, date，varchar2，相应的在导入MySQL时，其处理方式也应该注意调整，在此不再赘述。

内置函数差异

在日常项目用到的SQL中，需要考虑如下差异。

差异项	Oracle & PLSQL	MySQL
字符串转日期	`to_date('2017-1-1','yyyy-mm-dd')`	`str_to_date('2017-1-1', '%Y-%m-%d')`
日期转字符串	`to_char(sysdate, 'yyyy-mm-dd')`	`date_format(sysdate(), '%Y-%m-%d')`
当前日期	`sysdate`	`now(), sysdate()`
当前时间戳	`current_timestamp, systimestamp`	`current_timestamp`
字符串拼接	`concat('A', 'B') = 'AB'`	`concat('A', 'B', 'C') = 'ABC'`
管道符拼接字符串	`'A' \|\| 'B' \|\| 'C' = 'ABC'`	默认不支持，但可通过设置`set SQL_MODE = 'pipes_as_concat' 或直接 set SQL_MODE='oracle'`启用此功能
数值截断	`trunc(-1.221) = -1`	`cast( -1.221 as SIGNED INT) = -1`
日期截断	`trunc(sysdate) = 2017-06-12`	`date(sysdate()) = 2017-06-12`
间隔天数	`date1 - date2`	`datediff(date1, date2)`
日期加减	`date1 + 1`	`date_add(date1, interval 1 day)` 类似函数还有`add_date, date_sub, sub_date`
月份加减	`add_months(date1, 1)`	`date_add(date1, interval 1 month)`
间隔月份	`months_between(date1,date2)`	无
空值	`null, ''` 都表示空值	`null` 不同于 `''`， `IS NULL` 不能处理`''`数据
空值转换	`nvl(col, 'new')`	`case when col is null or col = '' then 'new' else col end`；`ifnull()`只能判断`null`，不能判断空字符串`''`
表数据融合	`merge into`此处merge语句功能丰富	`insert into ... on duplicate key update ...`用法比较固定
枚举条件判断	`decode()`	`if()`, 建议全部通过 `case when`语句改写，以维持一致性

另外，MySQL的函数在使用时，注意函数名和括号之间不能有空格；且比较运算符>=之间也不能有空格（如> =将执行出错）。

表主键取值差异

MySQL不支持Sequence，在表中可设置自增主键。如与Oracle兼容，可考虑统一采用 function来封装主键取值差异。

存储过程差异

对于存储过程而言，Oracle 的PLSQL与 MySQL也是有较大不同的，比如，MySQL不支持包、不支持常量定义、不支持dbms_output调试……具体细节以后另外整理。

触发器差异

略，在项目中应尽量避免使用触发器。

视图差异

MySQL的视图from语句中不允许存在子查询，可以通过将子查询创建为新的视图的方式解决。Oracle的物化视图相关概念，在MySQL中也不存在。

相同点

得益于SQL语言的工业标准化，各类数据库在一般SQL用法方面都相差不大，比如常规的数学函数（如abs, sign, max, min, sum等）。

对于MySQL与Oracle，以下这些内置函数也是相同的。

函数名	功能
replace	字符串替换
length	字符串长度（注意字符集差异）
instr	字符串子串匹配（两个参数）
lpad	字符串左侧补齐
rpad	字符串右侧补齐
trim	字符串去空格
cast	参数类型转换（注意类型本身，不同数据库的差异）

迁移问题

数据迁移

对于新上线项目，数据迁移的成本很小，基本上修改DDL语句即可。但对于已经运行一段时间，或保有存量数据的情况，甚至是涉及大数据量的数据迁移的情况。目前似乎没有比较合适的即时可用的工具。以下两种方案供参考（如有更好的、代价更小的方案，随时欢迎分享讨论）：

1、采用宏命令直接替换Oracle导出的DML语句为适用于 MySQL的版本，这种方法简单粗暴，可以在数据量较小的情况下尝试。采用UltraEdit的宏命令来处理DDL、DML语句的差异性，原理比较简单，但对于表和字段的comment语句稍微麻烦，需要通过修改comment语句为function的方法来实现；

2、采用ETL工具，比如Kettle，这种方式适于对已经存在表结构的数据的定期增量数据同步和临时的全量数据同步。后续将整理相关用法和总结。

程序改造

对于代码而言，需要首先清楚目标，是否需要兼容两套数据库，还是完全过渡到MySQL数据库。

从Oracle迁移到MySQL，需要针对当前项目的SQL进行梳理，并通过建立同名的 function来覆盖一定的差异性，但这种方式无法完全消除差异。比较常见的做法是，首先将项目中的所有SQL提取到一个配置文件中，然后再为不同数据库维护不同的配置文件。

当然，具体迁移过程中需要考虑的问题，不仅仅是语言差异的问题，在性能优化、运维方案等方面，都有较大不同。待后续完善。

秒客网