Oracle向MySQL迁移的注意点整理

1. 表结构
* 数据库设计工具: 连接Oracle拉取表字段信息, 调整数据库类型后, CM创建MySQL版本的创建表语句
* https://github.com/hepengju/ExcelVBAUtils
2. 表数据
* 数据迁移工具: 配置好数据库的来源和目标, 配置好需要同步的表, 直接JDBC查出来set进入, 1000条提交一次
* https://github.com/hepengju/datasync
3. 关键字段
* describe, specific 等关键字段需要加入转义符 `
4. SQL语句修改
* 日期格式化与解析
TO_CHAR(create_time , 'yyyy-mm-dd hh24:mi:ss') ==> DATE_FORMAT(create_time ,'%Y-%m-%d %H:%i:%s')
TO_DATE('2017-01-06 10:20:30', 'yyyy-mm-dd hh24:mi:ss') ==> STR_TO_DATE('2017-01-06 10:20:30','%Y-%m-%d %H:%i:%s')
* 其他相关函数
|| ==> concat()
wm_concat ==> group_concat
to_number ==> cast(xx as unsigned int)
sys_guid() ==> replace(uuid(),'-','')
nvl ==> ifnull
trunc ==> truncate
decode ==> case when
trim(to_char(a.LIS_PRICE,'9,999,999,999,999,990.99')) ==> format(a.LIS_PRICE,2)

add_months( sysdate, xxxNum) ==> adddate(current_timestamp, interval xxxNum month)
insert all ==> insert values (),(),()
select 'x' from dual ==> select 'x' /* MySQL8已经支持from dual, 无需修改 */
with xxx as (select ... ) ==> 普通子查询 /* MySQL8已经支持CTE(with语句) */

　　 * rownum, row_number() over()的处理
where rownum = 1 ==> limit 1
/* oracle */
SELECT AR_TITLE AS "arTitle"
, AR_DESC AS "arDesc"
, ROWNUM AS "rownum"
FROM CTC_SHOP_ARTICLE
WHERE CAT_AR_ID = #{AID}
/* mysql */
SELECT AR_TITLE AS "arTitle"
, AR_DESC AS "arDesc"
, @ROWNUM := @ROWNUM + 1 AS "rownum"
FROM CTC_SHOP_ARTICLE E, (SELECT @ROWNUM := 0) R
WHERE CAT_AR_ID = #{AID}

/* oracle */
SELECT ROW_NUMBER() OVER(PARTITION BY PROC_INST_ID_ ORDER BY START_TIME_ DESC ) RN
, ASSIGNEE_, OWNER_, NAME_, PROC_INST_ID_, ID_, START_TIME_, END_TIME_
FROM ACT_HI_TASKINST
/* mysql */
SELECT @rn:= case when @pid = PROC_INST_ID_ then @rn + 1 else 1 end as rn
, @pid:=PROC_INST_ID_
, ASSIGNEE_, OWNER_, NAME_, PROC_INST_ID_, ID_, START_TIME_, END_TIME_
FROM ACT_HI_TASKINST t, (select @pid:='', @rn:=0) as a
ORDER BY PROC_INST_ID_,START_TIME_ DESC
```
* 其他注意点
```
MySQL的字段默认值不支持函数, 比如在Oracle中设置某列默认值为to_char(sysdate,'yyyy-mm-dd HH24:mi:ss'), 则MySQL中需要在Java中设置
sql返回值的大小写问题: Oracle返回的都是大写, MySQL返回的是实际写法. 如果在mybatis的xml中使用Map进行接收的请注意修改

You can't specify target table 'xxx' for update in FROM clause
MySQL数据库的特有问题, 可以多嵌套一层查询处理
```