背景

现在信创是搞得如火如荼，在这个浪潮下，数据库也是从之前熟悉的Mysql换到了某国产数据库。

该数据库我倒是想吐槽吐槽，它是基于Postgre 9.x的基础上改的，至于改了啥，我也没去详细了解，当初的数据库POC测试和后续的选型没太参与，但对于我一个开发人员的角度来说，它给我带来的不便主要是客户端GUI工具这块。

我们读写数据库，程序这块还好，CURD代码用到的语法，基本是sql标准兼容的那些，没用多少mysql的特殊语法，所以这块没啥感觉。

客户端GUI这块，pg的客户端软件目前知道几个：

• navicat，公司没采购正版，用不了，替代软件是开源的dbeaver
• pgAdmin，pg官方客户端，结果不知道这个国产化过程中改了啥，用pgAdmin连上就各种报错，放弃
• dbeaver，这个倒是可以用，就是我感觉操作太麻烦了，太繁琐

基于以上原因，一直用dbeaver来着，之前两次把mysql项目的表结构换成pg，一次是写了个乱七八糟的代码来做建表语句转换，一次是用dbeaver建的，太繁琐了。

这次又来了个项目，我就换回了我熟悉的sqlyog（一款mysql客户端），几下就把表建好了（mysql版本），然后写了个工具代码，来把mysql的DDL转换成pg的。

下面简单介绍下这个转换代码。

技术选型

以前写这种代码，都是各种字符串操作（正则、匹配、替换等等），反正代码最终是非常难以维护。这次就先去网上查了下，发现有人有类似需求，还发了文章：https://zhuanlan.zhihu.com/p/314069540

我发现其中利用了一个java库，JSqlParser（https://github.com/JSQLParser/JSqlParser），我在网上也找了下其他的库，java这块没有更好的了，遥遥领先。

其官方说明：

“JSqlParser parses an SQL statement and translate it into a hierarchy of Java classes. ”

它支持解析sql语句这种非结构化文本为结构化数据，比如，针对如下的一个建库sql：

CREATE TABLE `xxl_job_log_report` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `trigger_day` datetime DEFAULT NULL COMMENT '调度-时间',
  `running_count` int(11) NOT NULL DEFAULT '0' COMMENT '运行中-日志数量',
  `suc_count` int(11) NOT NULL DEFAULT '0' COMMENT '执行成功-日志数量',
  `fail_count` int(11) NOT NULL DEFAULT '0' COMMENT '执行失败-日志数量',
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

可以解析为如下的类及属性：

image-20230902221909233

如上就包含了索引、列定义、建表选项等等。

我们接下来就只需要根据这些字段，获取数据并转换为对应的Postgre的语法即可。

转换效果

源码：https://github.com/cctvckl/convertMysqlDdlToPostgre.git

对于以上的类，给大家看看转换效果：

CREATE TABLE xxl_job_log_report (
    id serial PRIMARY KEY,
    trigger_day timestamp NULL,
    running_count int NOT NULL DEFAULT '0',
    suc_count int NOT NULL DEFAULT '0',
    fail_count int NOT NULL DEFAULT '0',
    update_time timestamp NULL
);
COMMENT ON COLUMN xxl_job_log_report.trigger_day IS '调度-时间';
COMMENT ON COLUMN xxl_job_log_report.running_count IS '运行中-日志数量';
COMMENT ON COLUMN xxl_job_log_report.suc_count IS '执行成功-日志数量';
COMMENT ON COLUMN xxl_job_log_report.fail_count IS '执行失败-日志数量';

这个sql，基本都满足我们的要求了。

当然，我这个工具类，还没特别完善，对于索引这块，只支持了主键索引，其他索引类型，后面空了我补一下。

支持的DDL类型，目前仅限于create table和drop table，目前能满足我个人需求了，反正mysqldump那些导出来的sql结构基本就这样。

暂不支持DML，如insert那些。

代码要点

整体逻辑

Statements statements = CCJSqlParserUtil.parseStatements(sqlContent);
for (Statement statement : statements.getStatements()) {
    if (statement instanceof CreateTable) {
        String sql = ProcessSingleCreateTable.process((CreateTable) statement);
        totalSql.append(sql).append("\n");
    } else if (statement instanceof Drop) {
        String sql = ProcessSingleDropTable.process((Drop) statement);
        totalSql.append(sql).append("\n");
    } else {
        throw new UnsupportedOperationException();
    }
}

如上，CCJSqlParserUtil 是 JSqlParser 的工具类，将我们的sql转换为一个一个的statement（即sql语句），我这边利用instanceof检查属于哪种DDL，再调用对应的代码进行处理，设计模式也懒得弄，if else写起来多快。

数据准备：表注释

List<String> tableOptionsStrings = createTable.getTableOptionsStrings();
String tableCommentSql = null;
int commentIndex = tableOptionsStrings.indexOf("COMMENT");
if (commentIndex != -1) {
    tableCommentSql = String.format("COMMENT ON TABLE %s IS %s;", tableFullyQualifiedName,tableOptionsStrings.get(commentIndex + 2));
}

解析出的表的相关属性，全都被放在一个list中，我们根据COMMENT关键字定位索引，然后找后两个，即是表注释具体值。

数据准备：列注释

由于我是直接在作者基础上改的，https://zhuanlan.zhihu.com/p/314069540，所以也是像他那样，复用了其代码，提取每一列的注释，逻辑也是根据COMMENT关键字找到index，然后index+1就是注释值。

image-20230902224226311

提取出来后，格式化为pg语法：

String.format("COMMENT ON COLUMN %s.%s IS %s;", table, column, commentValue);

数据准备：提取主键

Index primaryKey = createTable.getIndexes().stream()
    .filter((Index index) -> Objects.equals("PRIMARY KEY", index.getType()))
    .findFirst().orElse(null);

组装sql：建表第一行

String createTableFirstLine = String.format("CREATE TABLE %s (", tableFullyQualifiedName);

组装sql：主键列

这里涉及数据类型转换，如mysql中的bigint，在pg中，使用bigserial即可：

String dataType = primaryKeyColumnDefinition.getColDataType().getDataType();
if (Objects.equals("bigint", dataType)) {
    primaryKeyType = "bigserial";
} else if (Objects.equals("int", dataType)) {
    primaryKeyType = "serial";
} else if (Objects.equals("varchar", dataType)){
    primaryKeyType = primaryKeyColumnDefinition.getColDataType().toString();
}

String sql = String.format("%s %s PRIMARY KEY", primaryKeyColumnName, primaryKeyType);

组装sql：其他列

这部分有几块：

• 类型转换，mysql的类型，转换为pg的，我这边定义了一个map，大致如下：

image-20230902224901408

以上仅是部分，具体查看代码

默认值处理

这块也比较麻烦，比如mysql中的函数这种，如CURRENT_TIMESTAMP这种默认值，转换为pg中的对应函数，我大概定义了几个，满足当前需要：

static {
    MYSQL_DEFAULT_TO_POSTGRE_DEFAULT.put("NULL", "NULL");
    MYSQL_DEFAULT_TO_POSTGRE_DEFAULT.put("CURRENT_TIMESTAMP", "CURRENT_TIMESTAMP");
    MYSQL_DEFAULT_TO_POSTGRE_DEFAULT.put("CURRENT_DATE", "CURRENT_DATE");
    MYSQL_DEFAULT_TO_POSTGRE_DEFAULT.put("CURRENT_TIME", "CURRENT_TIME");
}

删除pg不支持的mysql语法

// postgre不支持unsigned
sourceSpec = sourceSpec.replaceAll("unsigned", "");
// postgre不支持ON UPDATE CURRENT_TIMESTAMP
sourceSpec = sourceSpec.replaceAll("ON UPDATE CURRENT_TIMESTAMP", "");

打印完整的pg语法sql

这块就不说了，上面效果展示部分有。

生成出来的sql，会在项目根路径下的target.sql文件中

总结

生成的target.sql文件，在idea中打开，如果有语法错误会飘红，如果大家有java开发能力，直接debug改就行，不行就提issue，我看到了空了就改；

我之前拿着有语法错误的sql就去dbeaver执行了，报错也不详细，看得一脸懵，idea还是厉害。

参考资料

mysql官方的迁移指南，里面包含了pg的各种类型对应到mysql的什么类型
https://dev.mysql.com/doc/workbench/en/wb-migration-database-postgresql-typemapping.html
mysql中的各种类型查阅
https://dev.mysql.com/doc/refman/8.0/en/data-types.html
pg中的各种类型查阅，我看得低版本的，谁让我们的信创数据库是基于pg 9版本的呢
https://www.postgresql.org/docs/11/datatype-numeric.html#DATATYPE-INT

这边直接贴一下吧，方便大家看：