MySql 迁移到 达梦8.0

首发于个人博客

使用dm迁移工具，将mysql数据库表迁移到达梦。库名，表名必须大写。
类型 DATETIEM ----转换----> TIMESTAMP

---

1.IF语句替换为DECODE 或 CASE WHEN

例：
mysql

SELECT
        count(IF(l.deploy_status = '0', 1, null)) AS LEAGUENUM_0,
        count(IF(l.deploy_status = '1', 1, null)) AS LEAGUENUM_1,
        sum((
            SELECT
                count( 1 )
            FROM
                crc_saas_origin.gb_league_node ln
                INNER JOIN crc_saas_origin.gb_agency_node_rel anr ON ln.id = anr.node_id
                INNER JOIN crc_saas_origin.gb_league_agency la ON la.id = anr.agency_id
                INNER JOIN crc_saas_origin.gb_group_agency_rel gar ON la.id = gar.agency_id
                INNER JOIN crc_saas_origin.gb_league_group lg ON lg.id = gar.group_id
                INNER JOIN crc_saas_origin.gb_league_group_rel lgr ON lg.id = lgr.group_id
                AND lgr.league_id = l.id
                AND ln.deploy_status = '1'
                AND ln.role_type = '1'
                )) AS NODENUM
        FROM
            crc_saas_origin.gb_league l
            LEFT JOIN crc_saas_origin.gb_league_user lu ON l.id = lu.league_id
        WHERE
        l.is_del = 0

达梦:

SELECT
        count(DECODE(l.deploy_status,'0', 1, null)) AS LEAGUENUM_0,
        count(DECODE(l.deploy_status,'1', 1, null)) AS LEAGUENUM_1,
        sum((
            SELECT
                count( 1 )
            FROM
                SAAS2.gb_league_node ln
                INNER JOIN SAAS2.gb_agency_node_rel anr ON ln.id = anr.node_id
                INNER JOIN SAAS2.gb_league_agency la ON la.id = anr.agency_id
                INNER JOIN SAAS2.gb_group_agency_rel gar ON la.id = gar.agency_id
                INNER JOIN SAAS2.gb_league_group lg ON lg.id = gar.group_id
                INNER JOIN SAAS2.gb_league_group_rel lgr ON lg.id = lgr.group_id
                AND lgr.league_id = l.id
                AND ln.deploy_status = '1'
                AND ln.role_type = '1'
                )) AS NODENUM
        FROM
            SAAS2.gb_league l
            LEFT JOIN SAAS2.gb_league_user lu ON l.id = lu.league_id
        WHERE
        l.is_del = 0

2.表名前加库名变量 ${datasource}

mysql

  <delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
    delete from gb_vote
    where id = #{id,jdbcType=BIGINT}
  </delete>

达梦

  <delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
    delete from ${datasource}.gb_vote
    where id = #{id,jdbcType=BIGINT}
  </delete>

3. 自增ID函数替换

mysql

  <insert id="insert" parameterType="com.helloworld.app.entity.Vote">
    <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Long">
      SELECT LAST_INSERT_ID()
    </selectKey>
    insert into gb_vote (invite_id, user_id, status, 
      create_by, create_time)
    values (#{inviteId,jdbcType=BIGINT}, #{userId,jdbcType=BIGINT}, #{status,jdbcType=INTEGER}, 
      #{createBy,jdbcType=BIGINT}, #{createTime,jdbcType=TIMESTAMP})
  </insert>

达梦

  <insert id="insert" parameterType="com.helloworld.app.entity.Vote" keyProperty="id" useGeneratedKeys="true"  keyColumn="id">
    insert into gb_vote (invite_id, user_id, status, 
      create_by, create_time)
    values (#{inviteId,jdbcType=BIGINT}, #{userId,jdbcType=BIGINT}, #{status,jdbcType=INTEGER}, 
      #{createBy,jdbcType=BIGINT}, #{createTime,jdbcType=TIMESTAMP})
  </insert>

vscode替换正则

>\n\s*<selectKey (KeyProperty="id") order="AFTER" resultType="java.lang.long">\n\s*(.*)\n\s*(.*)
 $1 useGeneratedKeys="true"  keyColumn="id">

4. 去除`符号

mysql

SELECT
      r.id,
      r.name,
      r.cn_name,
      r.file_path,
      r.create_time,
      u.username AS create_name,
      l.`name` as league_name

达梦

   SELECT
      r.id,
      r.name,
      r.cn_name,
      r.file_path,
      r.create_time,
      u.username AS create_name,
      l.name as league_name