Mybatis 插件： MySQL sql 语句转换为合法的达梦sql语句

yzp-

已于 2024-01-16 18:06:42 修改

阅读量5k

点赞数 5

文章标签： mybatis mysql sql

于 2023-05-12 23:51:20 首次发布

本文链接：https://blog.csdn.net/qq_38148090/article/details/130651854

版权

文章讲述了在项目从MySQL迁移到达梦数据库的过程中，如何通过Mybatis插件实现SQL改写自动化，包括类型一至三的SQL改写策略，并介绍了测试和优化的过程。使用了JSqlParser和Druid库来处理SQL解析，最终创建了一个在生产环境中使用的插件。

摘要由CSDN通过智能技术生成

问题

因为现在提倡使用国产化数据库，而且客户也有信创的要求，所以要把项目使用国产化数据库进行部署。项目已经运行了很多年了，里面有大量的 SQL 语句，底层数据库都是 MySQL，现在要迁移到达梦数据库，怎么办？一开始的解决方案是这样的，先拷贝一份 mapper xml 文件，再在上面改 SQL ，SQL 功能逻辑不变，可能就是小修小改， SQL 语句无非就是增删改查，而且大多数都是单表的增删改查，只是多费一些时间而已，但怎么知道这个 SQL 语句要不要改？怎么改？改了后怎么测试它是正确的？仅凭肉眼观察而不用实际运行一下？还是改一个 mapper 就测试一下？很多人都熟悉 MySQL 但不熟悉达梦，这时候就要开始去看别人踩过的坑，下载一份官方手册，对照着看，估计一下工作量。

粗略看了一下 mapper 文件的数量，整个项目 20 多个模块一共 600 多个mapper 文件，这些 mapper xml 都是不同的人不同时期写的，什么 “replace into”，“on duplicate key update ”，“insert ignore into”，第一次看到 MySQL 还能这样写，前两者在达梦中要转为 “merge into”，还有很多函数是 MySQL 里有但达梦没有，还需要找替换方法的，有些函数是 MySQL 和达梦都有但参数写法不同的，这些一开始就能想到的，还有一些看上去没问题，结果在 SQL 测试报错了才知道不行，算是暗坑，比如 “left join tblName where xxx” 在 MySQL 中没问题，但在达梦中就会报错，要改为 “left join tblName on true where xxx”，我之前都没见过更没想到还有 “left join tblName where xxx” 这种写法，还有 group by，having 不支持别名，“comment，ref”关键字报错等等问题。最后我考虑了一下就放弃把每个 mapper xml 文件都手动改，手动测的方案，改为用 Mybatis 插件的方式做，用代码的方式自动化调整 SQL 语句。把达梦数据库实例设置为兼容 MySQL 模式就可以兼容大部分的 MySQL 语法，减少工作量。

分析

Mybatis 拦截器有 4 个入口可以进行拦截，但我们拦截的目的也就是改写 SQL 字符串，在 Statement 层进行拦截就好了，这时候获取到的 SQL 字符串有 “?”，它已经处理完 <where>,<if>，<foreach> 这些动态标签了，此时的 SQL 字符串叫“静态 SQL ”。在这个过程中必须要保证原始 sql 中的每一个 “?” 和 ParameterMapping 对象位置顺序，数量一一对应，不能有任何的变动。如果你改写 SQL 后多加一个“?”，就必须在 ParameterMapping 列表和这个“?”的相同位置处添加一个代表这个“?” 暗示的ParameterMapping ，否则就会报错。这个和 Mybatis 中“?”和 ParameterMapping 列表的生成有关。在改写 SQL 字符串的时候，必须保证修改后的 SQL 和之前的 SQL 功能一样，而且不对其他不相干的 SQL 有影响，可以理解为其他不相干的 SQL 都是这个改写逻辑方法的测试用例。写完插件，测完代码后，我总结了需要改的 3 种类型：

类型一改写

第 1 种就是方法改写，比如 str_to_date() 方法要改为 date_format() 方法，它里面的参数位置顺序可能需要调整，类似的还有 pow() 改为 power()，里面的参数不需要调整；它只涉及到 sql 字符串的改写，和下面的第三种类型差不多，但因为MySQL和达梦有很多sql函数用法不一致，所以把它单独看成是一种类型。

@Override
    public String transfer(String sql) {
        MySqlStatementParser parser = new MySqlStatementParser(sql);
        SQLStatement sqlStatement = parser.parseStatement();
        sqlStatement.accept(new MySqlASTVisitorAdapter() {
            @Override
            public boolean visit(SQLMethodInvokeExpr expr) {
                if (expr.getMethodName().equalsIgnoreCase("pow")) {
                    expr.setMethodName("power");
                }
                return super.visit(expr);
            }
        });
        return SQLUtils.toMySqlString(sqlStatement);

    }

类型二改写

第 2 种就是插入，更新的写法转换，涉及到修改 List<ParameterMapping> parameterMappings 属性，它要连接数据库获取它的主键和自增列是什么，并跳过插入和修改自增列；比如 “replace into” 的写法改为 “merge into” 的写法；如果生成的 sql 过于庞大，超过了数据库 65535 个 “？” 占位符的限制，得切分为多个小批量的 sql 去执行。

replace into test(id,numbers,age) values(2,100,15)

要改为

merge into A.test
using (select 2 id,100 numbers,15 age from dual) t 
on(A.test.id = t.id) 
when matched then 
update set A.test.numbers=t.numbers,A.test.age=t.age
when not matched then 
insert (id,numbers,age) values(t.id,t.numbers,t.age)

类型三改写

第 3 种是 sql 字符串的改写，就是一些 MySQL 和达梦 sql 写法的区别和转换，不复杂，比如 group by ，having 别名改为原本的样子,

select count(*) cnt from tbl where xxx group by cnt
改为
select count(*) cnt from tbl where xxx group by count(*)

还有一些就是把 sql 里的关键字别名加上双引号。比如

select ref.id from tbl as ref 
改为 
select "ref".id from tbl as "ref"

这可以使用正则表达式去解决。

if (sql.contains("ref")) {
   sql = sql.replaceAll("\\.ref ", ".\"ref\" ")
         .replaceAll(",\\s*ref,", ",\"ref\",")
         .replaceAll(",\\s*ref\\s*\\)", ",\"ref\")")
         .replaceAll("\\s+ref\\s+", " \"ref\" ")
         .replaceAll("(,|\\s+)ref\\s*\\.", "$1\"ref\".");
}

这三种修改类型分别使用责任链设计模式可以得到很好处理。

for (FunctionTransfer functionTransfer : functionTransferList) {
    if (functionTransfer.isSupport(sql)) {
        sql = functionTransfer.transfer(sql);
    }
}

测试

通过反射 mapper 接口代理对象执行全部的接口方法，发现 SQL 报错，找到 SQL 报错原因，把修改后正确的逻辑自动化为代码，并做严格的限制，使它不影响到其他的代码的执行。有的长 SQL 有 500多行，空格，括号一层套一层，我们可以通过一些简单的算法提取出我们想要的字符串，并进行处理，在这个过程中用到了两个力扣上的算法，写过就会了。

算法

1，获取一个字符串列表，列表元素可能包含嵌套括号，以“,”作为分隔符。

public static List<String> getSegment(String sql, int left, int right) {
        List<String> list = new ArrayList<>();
        int leftFlag = 0, j = left, start = left;
        while (j < right) {
            if (sql.charAt(j) == ',' && leftFlag == 0) {
                list.add(sql.substring(start, j).trim());
                start = j + 1;
            }
            if (sql.charAt(j) == '(') {
                leftFlag++;
            }
            if (sql.charAt(j) == ')') {
                leftFlag--;
            }
            if (j == right - 1) {
                String substr = sql.substring(start, right).trim();
                if (substr.length() > 0) {
                    list.add(substr);
                }
            }
            j++;
        }
        return list;
    }

2，递归解决嵌套问题，把嵌套 if() 转为嵌套 case when。因为 if() 里面可能嵌套 if()，为什么要把 if() 转为 case when? 因为达梦中的 if() 使用受限，和 MySQL 中的 if() 不一样。另外只要有分支嵌套，都可以考虑使用递归的方式解决。还有一个地方也是使用这种方式解决嵌套问题，但还要考虑很多的其他原因。

public String getTrans(String sql) {
        int idx = sql.indexOf("if(");
        while (idx >= 0) {
            StringBuilder sb = new StringBuilder();
            Content expectContent = StrUtil.getExpectContent(sql, idx + 2);
            assert expectContent != null;
            String expectStr = expectContent.getExpectStr();
            int end = expectContent.getEnd();

            List<String> segmentList = StrUtil.getSegment(expectStr, 0, expectStr.length());
            String whenStr = segmentList.get(0);
            // if() 函数里的 whenStr，trueStr，falseStr 都可能继续有 if() 函数，所以需要递归执行，
            // 要把所有的嵌套 if() 都变为嵌套的 case when。在分支选择判断的地方最容易出现嵌套，比如 if(),case when，而
            // 其他的普通函数则很少有嵌套，即使有，也很容易通过迭代的方式遍历出来，因为普通函数就只有一个执行分支，比如
            // date_format(date_format('2012-12-12 12:12:12','%Y-%m-%m'),'%Y'),要么执行成功，要么执行失败。
            if (whenStr.contains("if(")) {
                whenStr = "(" + getTrans(whenStr) + ")";
            }
            String trueStr = segmentList.get(1);

            if (trueStr.contains("if(")) {
                trueStr = "(" + getTrans(trueStr) + ")";
                //  if(num1 != null,num2 != null,num3) 的解决方法
            } else if (trueStr.contains("!=") && trueStr.split("!=")[1].trim().equalsIgnoreCase("null")) {
                String[] split = trueStr.split("!=");
                trueStr = "ifnull(" + split[0] + ",0)";
            }
            String falseStr = segmentList.get(2);
            if (falseStr.contains("if(")) {
                falseStr = "(" + getTrans(falseStr) + ")";
            }
            sql = sb.append(sql, 0, idx).append(" case when ").append(whenStr).append(" then ").append(trueStr).append(" else ")
                    .append(falseStr).append(" end ").append(sql.substring(end)).toString();
            idx = sql.indexOf("if(", idx + 3);
        }
        return sql;
    }

总结

最耗时的部分在于测试，因为是老项目，里面的 SQL 很多都是自动生成的，很多 SQL 语句已经废弃了，都不删，也没有接口测试，我只能假定所有 SQL 都是正确，全都执行一遍，结果浪费了很多时间，而且有个模块有 180 多个文件，启动一次要花60多秒。发现报错，解决报错，写代码的时间反而不是很多。

这个插件核心代码大概 2 千多行，测试代码有1千多行。写的时候不知道有 SQL 解析库，很多字符串操作都是靠正则表达式来完成，后面才知道有 JSqlParser 这个强大的 SQL 解析库，通过访问者模式可以操作任意一个合法的 SQL ，因为它把 SQL 进行了语法解析和词法解析，事半功倍，真是磨刀不误砍柴工。使用 JSqlParser 库解析 sql，但很多正确的 sql JSqlParser 并不支持，无奈之下弃用 JSqlParser 库转用 druid 库，几乎完美支持 MySQL 的语法，但在解析的时候发现 SQLExpr 表达式的 toString() 方法并不好，只能通过递归的方法去获取正确的 toString() 方法，写完后就很放心了。

这个插件现在已经在生产项目中正式使用了，虽然它也并不完美，但它现在恰好能满足我们的需求，如果以后有 sql 报错了，它也能很快地定位到问题并解决，它就是一个修改 sql 字符串的小插件而已。

git 地址：mysqltodmsqltransfer: 一个 mybatis 插件，可以把 MySQL 的 sql 转为合法的达梦的 sql，主要用于信创达梦数据库迁移