Mysql迁移OpenGauss

公司要做信创的迁移，本文简单描述了一下Mysql迁移到OpenGauss的大概流程步骤

预言yy

5026人浏览 · 2023-03-10 09:39:40

预言yy · 2023-03-10 09:39:40 发布

一、数据库迁移

1.1Mysql、Oracle迁移OpenGauss

1.1.1使用navicat链接openGauss数据库

二、通用流程改造

2.1、application.yml的设置

2.1.1JDBC链接

2.1.2activiti的配置

2.2、databaseIdProvider多数据库配置

2.2.1得到OpenGauss数据库的ProductName

2.2.2增加数据源配置类添加多数据源配置映射

2.2.3Mysql语句转OpenGauss

一、数据库迁移

1.1Mysql、Oracle迁移OpenGauss

1.1.1使用navicat链接openGauss数据库

1.1.1.1选择PostgreSQL

OpenGauss可以使用PostgreSQL的驱动，所以直接选择PostgreSQL就可以。

前提条件：已经建好OpenGauss的初始数据库、模式

1.1.1.2输入连接信息

点击菜单栏工具-数据传输

选择源为Mysql或Oracle

一直下一步即可。

由于迁移后的表中所有字段没有默认值，需要对比Mysql数据库手动添加默认值。

二、通用流程改造

2.1、application.yml的设置

2.1.1JDBC链接

OpenGauss是基于postgresql开发的，可以使用org.postgresql.Driver也可以使用org.opengauss.Driver。

要注意schema的设置

url: jdbc:postgresql://127.0.0.1:5432/数据库名?currentSchema=模式名

spring:
  datasource:
    username: fy
    password: *****
    url: jdbc:postgresql://XXXXX:5432/fy?currentSchema=fy
    driver-class-name: org.postgresql.Driver
    hikari:
      connection-timeout: 30000
      idle-timeout: 60000
      max-lifetime: 1800000
      minimum-idle: 2
      maximum-pool-size: 5

2.1.2activiti的配置

要注意的是database-schema的设置

database-schema: 模式名

spring:
  activiti:
    db-history-used: true  #使用历史表，如果不配置，则工程启动后可以检查数据库，只建立了17张表，历史表没有建立
    history-level: full    #记录全部历史操作
    #自动建表
    #    flase： 默认值。activiti在启动时，会对比数据库表中保存的版本，如果没有表或者版本不匹配，将抛出异常。
    #    true： activiti会对数据库中所有表进行更新操作。如果表不存在，则自动创建。
    #    create_drop： 在activiti启动时创建表，在关闭时删除表（必须手动关闭引擎，才能删除表）。
    #    drop-create： 在activiti启动时删除原来的旧表，然后在创建新表（不需要手动关闭引擎
    check-process-definitions: false # 自动部署验证设置:true-开启（默认）、false-关闭  在resource目录下添加processes文件夹，并且文件夹不能为空
    #    main:
    #      allow-bean-definition-overriding: true #当遇到同样名字的时候，是否允许覆盖注册
    database-schema: fyjr_sunam  #置建表策略，如果没有表，自动创建表  修改这个地方为大写

2.2、databaseIdProvider多数据库配置

2.2.1得到OpenGauss数据库的ProductName

通过下放代码可以得到OpenGauss数据库的ProductName为：PostgreSQL

    @Test
    public void getDataBaseProductName() throws Exception {
        String driver = "org.opengauss.Driver";

        String url = "jdbc:opengauss://XXXX:5432/fy?currentSchema=fy";

        String username = "fy";

        String password = "";

        Class.forName(driver);

        Connection con = (Connection) DriverManager.getConnection(url,username,password);
        String dbVersion = con.getMetaData().getDatabaseProductVersion();

        DatabaseMetaData metaData = (DatabaseMetaData) con.getMetaData();

        System.out.println("数据库的产品名称:" + metaData.getDatabaseProductName()+" version "+dbVersion);

    }

2.2.2增加数据源配置类添加多数据源配置映射

得到opengauss的ProductName后将p.setProperty("PostgreSQL", "opengauss");加到配置中。

其中：

p.setProperty("PostgreSQL", "**");**随便写，**用于xxxMapper.xml中指定databaseId="**"，ProductName为PostgreSQL

@Configuration
@EnableTransactionManagement
@MapperScan({ "com.zzz.yyy.mapper", "com.zzz.yyy.mapper.extend" })
public class MybatisConfig {
    @Bean
    public DatabaseIdProvider getDatabaseIdProvider() {
        DatabaseIdProvider databaseIdProvider = new VendorDatabaseIdProvider();
        Properties p = new Properties();
        p.setProperty("MySQL", "mysql");
        p.setProperty("Oracle", "oracle");
        p.setProperty("PostgreSQL", "opengauss");
        p.setProperty("TiDB", "tidb");
        p.setProperty("DB2", "db2");
        p.setProperty("SQL Server", "sqlserver");
        databaseIdProvider.setProperties(p);
        return databaseIdProvider;
    }
}

2.2.3Mysql语句转OpenGauss

替换步骤：

在 xxxMapper.xml 中将所有sql语句上加入 databaseId="mysql"
复制一份mysql的sql（即将替换的opengauss语句）
在复制的sql上加入databaseId="opengauss"
找出mysql与opengauss语句区别，然后替换sql

凡是PostgreSQL的东西openGauss都兼容。而openGauss扩展了部分语法类似Oracle 的，反过来PostgreSQL无法用。所以为了统一和简化，都使用PostgreSQL的标准。

大小写
1. PostgreSQL的数据库内核对大小写敏感。数据库名，数据表名，列名区分大小写。
2. 在PostgreSQL中，执行SQL语句时，在不加双引号的情况下，会把所有表示关键字，库名，表名，列名的字符串转换成小写。所以又说PostgreSQL不区分大小写的。
3. 在书写SQL时，为了便于理解，默认：关键字大写，表名首字母大写，列名全部小写。
分页
1. 如果从第一条开始取记录 , PostgreSQL与mysql都支持 select * from tb limit A;
2. 当从至少第二条数据取记录时 , PostgreSQL仅支持 limit A offset B , 而 mysql 除了支持 pg 的分页之外 , 还支持 limit B,A
日期转字符串
1. Oracle : To_Char(DATE,'YYYY-MM-DD')
2. PostgreSQL : To_Char(DATE,'YYYY-MM-DD')，同上
3. MySQL : date_format(DATE,'%Y-%m-%d')
字符串拼接
1. Oracle : Field1 || '_' || Field2 || '_' ||...，也可以用CONCAT()
2. PostgreSQL : Field1 || '_' || Field2 || '_' ||...，也可以用CONCAT()，同上
3. MySQL : CONCAT(Field1,'_',Field2,'_',...)
字符串转日期
1. Oracle : select to_date('2022-06-01 23:45:59','yyyy-mm-dd hh24:mi:ss') from dual
2. PostgreSQL : select to_timestamp('2022-06-01 23:45:59','yyyy-mm-dd hh24:mi:ss')
3. MySQL : select str_to_date('2022-06-01 23:45:59','%Y-%m-%d %T')
PostgreSQL和OpenGauss中不能使用“ ` ”符号

以下代码自行修改

package com.zzz.yyy;

import org.apache.commons.lang3.StringUtils;
import org.junit.Test;
import java.io.*;
import java.util.*;

/**
 *  <p> mysql迁移opengauss 测试工具类 </p>
 *
 * @description :
 * @author : zhaoyunlong
 * @date : 2023/1/02 10:10
 */
public class MySQLToOracleTest {

    private final static String ORACLE_SQL = "    <!-- ====================================== ↓↓↓↓↓↓ opengauss ↓↓↓↓↓↓  ====================================== -->";

    @Test // 替换项目中的sql语句
    public void testSQL() throws Exception {//src/main/resources/mapper
        String path = System.getProperty("user.dir") + "\\src\\main\\resources\\mapper"; // TODO 这里替换为自己的项目路径
        File file = new File(path);
        HashMap<Object, Object> fileMap = new HashMap<>();
        getAllFileByRecursion(fileMap, file);

        fileMap.forEach((key, value) -> {
            String fileXmlName = (String) key;
            File fileXml = (File) value;
            String filePath = fileXml.getPath();

            if (!StringUtils.isEmpty(fileXmlName)) {
                System.out.println(filePath);
                try {
                    // 1、加入 databaseId="mysql"
                    addMysql(filePath);
                    // 2、复制一份oracle的sql
                    if (!checkHasOracle(filePath)) {
                        copyMysqlToOracle(filePath);
                    }
                    // 3、加入 databaseId="openss"
                    addOracle(filePath);
                    // 4、替换mybatis `xxxMapper.xml` 中的sql语句
                    repalceSQL(filePath);
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }

        });
        System.out.println(fileMap);
    }

    /**
     * 替换mybatis `xxxMapper.xml` 中的sql语句
     */
    private static void repalceSQL(String path) throws IOException {
        File file = new File(path);
        FileReader in = new FileReader(file);
        BufferedReader bufIn = new BufferedReader(in);
        // 内存流, 作为临时流
        CharArrayWriter tempStream = new CharArrayWriter();
        // 替换
        String line = null;
        int row = 0;
        int rowOracle = 0;
        while ((line = bufIn.readLine()) != null) {
            row++;
            if (line.contains(ORACLE_SQL)) {
                rowOracle = row;
            }

            if (rowOracle != 0 && row > rowOracle) {
                // ① 替换 `LIMIT` -> `AND ROWNUM <= 1` TODO 【注： 部分包含`ORDER BY` 关键字，需单独处理】

                // ② oracle中不能使用“ ` ”符号
                line = StringUtils.replace(line, "`", "");

                // ③ CONCAT('%', #{name}, '%')  ->     '%'||#{name}||'%' （Oracle中concat函数只能放两个参数）
                if (line.contains("concat")) {
//                    String newLine = line.substring(line.indexOf("concat(") + 7, line.lastIndexOf("'%')") + 3);
                    line = line.replaceAll(",", " || ");
                    line = line.replaceAll("concat", "");
                }
                if (line.contains("CONCAT")) {
//                    String newLine = line.substring(line.indexOf("CONCAT(") + 7, line.lastIndexOf("'%')") + 3);
                    line = line.replaceAll(",", " || ");
                    line = line.replaceAll("CONCAT", "");
                }

                // ④ `UNIX_TIMESTAMP` -> `ORACLE_TO_UNIX` date类型时间转10位时间戳
                line = line.replaceAll("UNIX_TIMESTAMP()", "floor(extract(epoch from now()))");

                // ⑤ 部分关键字需加上双引号 TODO 【注： 字段名大写，映射的别名需保存原本小写！】 `level -> "LEVEL"`  `user -> "USER"`      `number -> "NUMBER"`  `desc -> "DESC"`

                if (line.endsWith(" date") || line.endsWith(" date,") || line.endsWith(" 'date'") || line.endsWith(" 'DATE'") || line.endsWith("DATE")) {
                    line = line.replaceAll(" date", " \"date\"");
                    line = line.replaceAll(" date,", " \"date,\"");
                    line = line.replaceAll(" 'date'", " \"date\"");
                    line = line.replaceAll(" 'DATE'", " \"date\"");
                    line = line.replaceAll(" DATE", " \"date\"");
                }
                line = line.replaceAll(" date ", " \"date\" ");
                line = line.replaceAll(" DATE ", " \"date\" ");

                // ⑥ `IFNULL` -> `NVL`

                // ⑦ 时间 `str_to_date` -> `to_date`     `date_format` -> `to_char`
                // `%Y-%m-%d`  -> `yyyy-MM-dd`    `%Y-%m` -> `yyyy-MM`
                line = line.replaceAll("str_to_date", "TO_DATE");
                line = line.replaceAll("STR_TO_DATE", "TO_DATE");
                line = line.replaceAll("date_format", "TO_CHAR");
                line = line.replaceAll("DATE_FORMAT", "TO_CHAR");

                // 这里注意替换顺序问题，最长的应该放最前面！！！
                line = line.replaceAll("%Y-%m-%d %H:%i:%S", "yyyy-MM-dd HH24:mi:ss");
                line = line.replaceAll("%Y-%m-%d %H:%i:%s", "yyyy-MM-dd HH24:mi:ss");
                line = line.replaceAll("%Y-%m-%d %H:%i", "yyyy-MM-dd HH24:mi");
                line = line.replaceAll("%Y-%m-%d %H", "yyyy-MM-dd HH24");
                line = line.replaceAll("%Y-%m-%d %h", "yyyy-MM-dd HH");
                line = line.replaceAll("%Y-%m-%d", "yyyy-MM-dd");
                line = line.replaceAll("%Y-%m", "yyyy-MM");
                line = line.replaceAll("%Y", "yyyy");
                line = line.replaceAll("%H", "HH24");
                line = line.replaceAll("%k", "HH24");

                line = line.replaceAll("now\\(\\)", "(SELECT SYSDATE + 8/24 FROM DUAL)");
                line = line.replaceAll("NOW\\(\\)", "(SELECT SYSDATE + 8/24 FROM DUAL)");

                // ⑧ ...

                // 需手动处理的SQL 【 group by | 批量插入 | ... 】
            }
            // 将该行写入内存
            tempStream.write(line);
            // 添加换行符
            tempStream.append(System.getProperty("line.separator"));
        }
        // 关闭 输入流
        bufIn.close();
        // 将内存中的流 写入 文件
        FileWriter out = new FileWriter(file);
        tempStream.writeTo(out);
        out.close();
    }

    /**
     * 加入 databaseId="mysql"
     */
    private static void addMysql(String path) throws IOException {
        File file = new File(path);
        FileReader in = new FileReader(file);
        BufferedReader bufIn = new BufferedReader(in);
        // 内存流, 作为临时流
        CharArrayWriter tempStream = new CharArrayWriter();
        // 替换
        String line = null;
        while ((line = bufIn.readLine()) != null) {
            if ((line.contains("<select") || line.contains("<update") || line.contains("<insert") || line.contains("<delete")) && !line.contains("databaseId")) {
                if (line.endsWith(">")) {
                    line = line.replaceAll(">", " databaseId=\"mysql\">");
                } else {
                    line = line + " databaseId=\"mysql\"";
                }
            }
            // 将该行写入内存
            tempStream.write(line);
            // 添加换行符
            tempStream.append(System.getProperty("line.separator"));
        }
        // 关闭 输入流
        bufIn.close();
        // 将内存中的流 写入 文件
        FileWriter out = new FileWriter(file);
        tempStream.writeTo(out);
        out.close();
    }

    /**
     * 加入 databaseId="oracle"
     */
    private static void addOracle(String path) throws IOException {
        File file = new File(path);
        FileReader in = new FileReader(file);
        BufferedReader bufIn = new BufferedReader(in);
        // 内存流, 作为临时流
        CharArrayWriter tempStream = new CharArrayWriter();
        HashSet<String> lineSet = new HashSet<>();
        // 替换
        String line = null;
        while ((line = bufIn.readLine()) != null) {
            if (line.contains("databaseId=\"mysql\"")) {
                if (lineSet.contains(line)) {
                    line = line.replaceAll("databaseId=\"mysql\"", "databaseId=\"opengauss\"");
                }
                lineSet.add(line);
            }
            // 将该行写入内存
            tempStream.write(line);
            // 添加换行符
            tempStream.append(System.getProperty("line.separator"));
        }
        // 关闭 输入流
        bufIn.close();
        // 将内存中的流 写入 文件
        FileWriter out = new FileWriter(file);
        tempStream.writeTo(out);
        out.close();
    }

    /**
     * 复制一份oracle的sql
     */
    private static void copyMysqlToOracle(String path) throws IOException {
        File file = new File(path);
        FileReader in = new FileReader(file);
        BufferedReader bufIn = new BufferedReader(in);
        // 内存流, 作为临时流
        CharArrayWriter tempStream = new CharArrayWriter();
        // 替换
        String line = null;
        // 需要替换的行
        List<String> lineList = new LinkedList<>();

        int row = 0;
        int firstRow = 0;

        while ((line = bufIn.readLine()) != null) {
            row++;
            if (line.contains("<select") || line.contains("<update") || line.contains("<insert") || line.contains("<delete")) {
                firstRow = row;
            }

            // 添加替换内容
            if (firstRow != 0 && row >= firstRow && !line.contains("</mapper>") && !line.contains("<!-- ========") && !line.contains("oracle")) {
                lineList.add(line);
            }

            // 查询结束位置
            if (line.contains("<!-- =======")) {
                tempStream.append(System.getProperty("line.separator"));
                tempStream.write(ORACLE_SQL);
                tempStream.append(System.getProperty("line.separator"));
                tempStream.append(System.getProperty("line.separator"));

                lineList.forEach(lineValue -> {
                    // copy mysql 语句 转为oracle
                    try {
                        tempStream.write(lineValue);
                        tempStream.append(System.getProperty("line.separator"));
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                });
                tempStream.append(System.getProperty("line.separator"));
            }
            // 将该行写入内存
            tempStream.write(line);
            // 添加换行符
            tempStream.append(System.getProperty("line.separator"));
        }
        // 关闭 输入流
        bufIn.close();
        // 将内存中的流 写入 文件
        FileWriter out = new FileWriter(file);
        tempStream.writeTo(out);
        out.close();
    }

    /**
     * 检查是否已经复制SQL
     */
    private static boolean checkHasOracle(String path) throws IOException {
        File file = new File(path);
        FileReader in = new FileReader(file);
        BufferedReader bufIn = new BufferedReader(in);
        // 内存流, 作为临时流
        CharArrayWriter tempStream = new CharArrayWriter();
        // 替换
        String line = null;
        boolean result = false;
        while ((line = bufIn.readLine()) != null) {
            if (line.contains(ORACLE_SQL)) {
                result = true;
            }
            // 将该行写入内存
            tempStream.write(line);
            // 添加换行符
            tempStream.append(System.getProperty("line.separator"));
        }
        // 关闭 输入流
        bufIn.close();
        // 将内存中的流 写入 文件
        FileWriter out = new FileWriter(file);
        tempStream.writeTo(out);
        out.close();
        return result;
    }

    /**
     * 递归文件夹 -> 找到所有xml文件
     */
    private static void getAllFileByRecursion(HashMap<Object, Object> fileMap, File file) {
        File[] fs = file.listFiles();
        for (File f : fs) {
            String fileName = f.getName();
            if (f.isDirectory()) {
                // 若是目录则递归，否则打印该目录下的文件
                getAllFileByRecursion(fileMap, f);
            }
            if (f.isFile() && fileName.endsWith(".xml")) {
                fileMap.put(fileName, f);
            }
        }
    }

}

参考：

(68条消息) 数据库国产化——mybatis完成多数据源配置(兼容达梦、高斯)简记_mybatis多数据库兼容_shark-chili的博客-CSDN博客

(68条消息) 业务系统兼容数据库Oracle/PostgreSQL(openGauss)/MySQL的琐事_oracle goldendb_若苗瞬的博客-CSDN博客