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数据库的特有问题, 可以多嵌套一层查询处理 
        ```