SpringBoot实现华为openGauss数据库替换MySQL数据库

前言

在现代的数据库技术中，国产的华为的openGauss数据库以其强大的性能和灵活的扩展性逐渐受到越来越多开发者的青睐。本文将分享如何部署openGauss数据库，并介绍如何将现有的MySQL数据库迁移至openGauss。

部署openGauss数据库

首先，确保你的环境已经准备好，包括服务器的基本配置和操作系统的兼容性。在这里，我将分享如何快速部署openGauss数据库的步骤。

1. 编辑docker-compose文件：
   在部署openGauss时，我们通常会使用docker-compose来管理容器。你可以参考如下的docker-compose.yaml文件示例，并根据你的需求进行适当的修改，这里以enmotech/opengauss:5.0.0镜像部署为例。

  #opengauss数据库
  opengauss:
    image: enmotech/opengauss:5.0.0
    restart: always
    privileged: true
    container_name: yiliao-opengauss-5.0.0
    environment:
      - GS_NODENAME=node1
      - GS_USERNAME=user
      - GS_PASSWORD=user123456
      - GS_CLUSTER_NAME=node1
    ports:
      - '5432:5432'
    volumes:
      - '/home/opengauss:/var/lib/opengauss'

3. 启动容器：
   进入存放docker-compose.yaml文件的目录，然后运行以下命令以启动容器：

4. 测试连接：
   使用数据库管理工具（如Navicat）连接数据库，默认端口为5432。测试数据库是否已成功部署。

MySQL数据库迁移至openGauss

在某些场景下，你可能需要将现有的MySQL数据库迁移到openGauss。虽然这看起来可能会比较复杂，但通过合适的工具和策略，这个过程可以变得相对简单。以下是一个基础的MySQL到openGauss的迁移方法。

1. 使用Java编写SQL适配拦截器

原文来自网站：https://lcjhts.cn/
原文作者：Augety
在MySQL迁移到openGauss的过程中，一些SQL语法可能会不兼容。为了处理这些问题，我们可以编写一个SQL适配拦截器，以自动转换不兼容的SQL语法。

package com.lcj.interceptor;

import com.dnk.common.exception.MyException;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;

import org.apache.ibatis.mapping.SqlSource;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.springframework.stereotype.Component;

import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @description: Kingbase数据库适配拦截器
 * @author: laichuanju
 */

@Intercepts({
        @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
        @Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class})
})
@Component
@Slf4j
public class OpenGaussAdaptInterceptor implements Interceptor {

    @Override
    public Object intercept(Invocation invocation) throws Throwable {

        Object[] args = invocation.getArgs();
        //获取MappedStatement对象
        MappedStatement ms = (MappedStatement) args[0];
        //获取传入sql语句的参数对象
        Object parameterObject = args[1];

        BoundSql boundSql = ms.getBoundSql(parameterObject);
        //获取到拥有占位符的sql语句
        String sql = boundSql.getSql();
        String originSql = sql + "\n";
        //是否开启适配
        if(true){
            //1、替换自定义函数funGetDayNum
            sql = sql.replaceAll("funGetDayNum\\((.*?),(.*?)\\)", "(FLOOR((UNIX_TIMESTAMP($1) - UNIX_TIMESTAMP($2))/(60*60*24)))");

            //2、where子句字符串双引号替换为单引号
            sql = replaceWhereDoubleQuotes(sql);

            //3、替换SQL中的空字符串""为单引号
            sql = sql.replaceAll("\"\"", "''");
            sql = sql.replaceAll("\" \"", "' '");

            //4、group_concat处理
            sql = replaceGroupConcat(sql);

            //5、select-from之间的列名如果没加双引号，增加双引号
            sql = replaceColumnName(sql);

            //6、替换MySQL日期加减函数
            sql = replaceDateOperation(sql);

            //重新生成一个BoundSql对象
            BoundSql bs = new BoundSql(ms.getConfiguration(),sql,boundSql.getParameterMappings(),parameterObject);
            //解决MyBatis拦截器导致的foreach参数失效
            copyBoundSqlProperties(boundSql, bs);
            //重新生成一个MappedStatement对象
            MappedStatement newMs = copyMappedStatement(ms, new BoundSqlSqlSource(bs));
            //赋回给实际执行方法所需的参数中
            args[0] = newMs;
        }
        Object proceed = null;
        try {
            proceed = invocation.proceed();
        } catch (Exception e) {
            //在这里发现不兼容的SQL，发现异常后进行优化改进
            log.error("兼容SQL执行异常，原SQL：\n{}", originSql);
            log.error("\n转换SQL：\n{}", sql);
            throw e;
        }

        return proceed;
    }

    /**
     * 替换date_add和date_sub
     * @param sql
     * @return
     */
    private static String replaceDateOperation(String sql) {
        sql = sql.replace("now()","CURRENT_TIMESTAMP").replace("NOW()","CURRENT_TIMESTAMP");
        String patternString = "(DATE_ADD|DATE_SUB)\\(\\s*(.+)\\s*,\\s*INTERVAL\\s*(.+)\\s*\\)";
        Pattern pattern = Pattern.compile(patternString, Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(sql);

        while (matcher.find()) {
            String oldSql = matcher.group(0);
            String op = matcher.group(1);
            String match2 = matcher.group(2);
            String match3 = matcher.group(3);

            if ("date_sub".equals(op.toLowerCase())) {
                sql = sql.replace(oldSql, match2 + " - INTERVAL '" + match3 + "'");
            } else if ("date_add".equals(op.toLowerCase())){
                sql = sql.replace(oldSql, match2 + " + INTERVAL '" + match3 + "'");
            }

        }
        return sql;
    }

    /**
     * 取别名如果是单引号，替换为双引号
     * @param sql
     * @return
     */
    private static String replaceColumnName(String sql) {
        String pattern = "(\\S+)\\s*AS\\s+([A-Za-z0-9_\'\".]+)";

        Pattern regex = Pattern.compile(pattern, Pattern.CASE_INSENSITIVE);
        Matcher matcher = regex.matcher(sql);
        while (matcher.find()) {
            String oldSql = matcher.group(0);
            String frontAs = matcher.group(1);
            String colName = matcher.group(2);
            System.out.println("处理SQL段： "+oldSql);

            //取别名如果是单引号，替换为双引号
            if (!colName.contains("\"")) {
                String replaceStr = colName.replace("\'", "");
                String replaceSql = oldSql.replaceFirst(colName, "\"" + replaceStr + "\"");
                System.out.println("替换为： " + replaceSql);
                sql = sql.replace(oldSql, replaceSql);
            }

            //如果as前面为字符串，替换双引号为单引号，单引号转义\'
            if (frontAs.startsWith("\"") && frontAs.endsWith("\"")) {
                String replaceFrontAs = frontAs.substring(1, frontAs.length()-1);
                replaceFrontAs = replaceFrontAs.replace("\\\'", "'");
                replaceFrontAs = replaceFrontAs.replace("'", "\\\'");
                replaceFrontAs = "'" + replaceFrontAs + "'";
                sql = sql.replace(frontAs, replaceFrontAs);
            }
        }
        return sql;
    }

    /**
     * 将group_concat替换为string_agg
     * @param sql
     * @return
     */
    private static String replaceGroupConcat(String sql) {
        if ((sql.contains("GROUP_CONCAT") || sql.contains("group_concat")) && (sql.contains("SEPARATOR") || sql.contains("separator"))) {
            String pattern = "GROUP_CONCAT\\((.*?)\\s+SEPARATOR\\s+(.*?)\\)";

            Pattern regex = Pattern.compile(pattern, Pattern.CASE_INSENSITIVE);
            Matcher matcher = regex.matcher(sql);
            while (matcher.find()) {
                String oldSql = matcher.group(0);
                String firstString = matcher.group(1);
                String separator = matcher.group(2);
                sql = sql.replace(oldSql, "string_agg(" + firstString + "," + separator + ")");
            }
        }
        return sql;
    }

    /**
     * 将where子句中的双引号替换为单引号
     * @param sql
     * @return
     */
    private static String replaceWhereDoubleQuotes(String sql) {

        String pattern = "(\\w+_\\w+?)\\s*=\\s*(\"\\S+\")";
        String pattern2 = "IFNULL\\s*\\(\\s*(.*?)\\s*,\\s*(.*?)\\s*\\)";
        Pattern regex = Pattern.compile(pattern);
        Pattern regex2 = Pattern.compile(pattern2);
        Matcher matcher = regex.matcher(sql);
        while (matcher.find()) {
            String matchedText = matcher.group(2);
            if (matchedText != null) {
                String replaceText = matchedText.replaceAll("\"", "'");
                sql = sql.replace(matchedText, replaceText);
            }
        }
        matcher = regex2.matcher(sql);
        while (matcher.find()) {
            String matchedText = matcher.group(2);
            if (matchedText != null) {
                String replaceText = matchedText.replaceAll("\"", "'");
                sql = sql.replace(matchedText, replaceText);
            }
        }
        return sql;
    }

    public static void main(String[] args) {
        //5、select-from之间的列名如果没加双引号，增加双引号
        String sql = "DATE_SUB(NOW(), INTERVAL -5 MINUTE)";
        sql = replaceDateOperation(sql);
        System.out.println(sql);
    }

    /**
     * 添加数据库库名(模式名)
     * @param sql
     * @return
     */
    private static String addSchemaName(String sql, String defaultSchema) {
        Pattern pattern = Pattern.compile("( FROM|INSERT INTO|JOIN|UPDATE)\\s+([a-z0-9_\\.]+)", Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(sql);

        StringBuffer result = new StringBuffer();
        while (matcher.find()) {
            String keyword = matcher.group(1);
            String matchedText = matcher.group(2);
            //System.out.println("  keyword: " + keyword + "  matchedText： " + matchedText);
            //如果 matchedText 不包含点，则添加默认数据库名前缀
            if (!matchedText.contains(".")) {
                matchedText = defaultSchema + "." + matchedText;
                //重建原始语句
                matcher.appendReplacement(result, " " + keyword + " " + matchedText);
            }
        }
        matcher.appendTail(result);
        sql = result.toString();
        return sql;
    }

    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    @Override
    public void setProperties(Properties properties) {
    }

    /**
     * 使用反射设置BoundSql的属性
     * @param boundSql
     * @param bs
     */
    private void copyBoundSqlProperties(BoundSql boundSql, BoundSql bs) {
        if (ReflectionsUtil.getFieldValue(boundSql, "metaParameters") != null) {
            MetaObject metaObject = (MetaObject) ReflectionsUtil.getFieldValue(boundSql, "metaParameters");
            ReflectionsUtil.setFieldValue(bs, "metaParameters", metaObject);
        }
        if (ReflectionsUtil.getFieldValue(boundSql, "additionalParameters") != null) {
            Object metaObject = ReflectionsUtil.getFieldValue(boundSql, "additionalParameters");
            ReflectionsUtil.setFieldValue(bs, "additionalParameters", metaObject);
        }
    }

    /***
     * 复制一个新的MappedStatement
     * @param ms
     * @param newSqlSource
     * @return
     */
    private MappedStatement copyMappedStatement (MappedStatement ms, SqlSource newSqlSource) {
        MappedStatement.Builder builder = new MappedStatement.Builder(ms.getConfiguration(), ms.getId(), newSqlSource, ms.getSqlCommandType());

        builder.resource(ms.getResource());
        builder.fetchSize(ms.getFetchSize());
        builder.statementType(ms.getStatementType());
        builder.keyGenerator(ms.getKeyGenerator());
        if (ms.getKeyProperties() != null && ms.getKeyProperties().length > 0) {
            builder.keyProperty(String.join(",",ms.getKeyProperties()));
        }
        builder.timeout(ms.getTimeout());
        builder.parameterMap(ms.getParameterMap());
        builder.resultMaps(ms.getResultMaps());
        builder.resultSetType(ms.getResultSetType());
        builder.cache(ms.getCache());
        builder.flushCacheRequired(ms.isFlushCacheRequired());
        builder.useCache(ms.isUseCache());
        return builder.build();
    }

    /***
     * MappedStatement构造器接受的是SqlSource
     * 实现SqlSource接口，将BoundSql封装进去
     */
    public static class BoundSqlSqlSource implements SqlSource {
        private BoundSql boundSql;
        public BoundSqlSqlSource(BoundSql boundSql) {
            this.boundSql = boundSql;
        }
        @Override
        public BoundSql getBoundSql(Object parameterObject) {
            return boundSql;
        }
    }

}

2. OpenGauss由PostgreSQL开发改进而来，连接JDBC驱动、方式同PostgreSQL

3. 至此，数据库替换完毕，同样的，如果是其他国产数据库，可以使用相同的方法动态替换SQL，实现不修改原SQL代码的情况下数据库的适配