美文网首页微服务架构和实践
MySQL建表语句转PostgreSQL建表语句全纪录

MySQL建表语句转PostgreSQL建表语句全纪录

作者: 周立_itmuch | 来源:发表于2018-12-30 21:46 被阅读8次

    个人习惯用MySQL workbench EER数据建模,然后生成SQL语句到数据库中执行,这样表之间的关系比较直观。

    像下面这样:

    • 画图

      MySQL workbench-1
    • 正向工程,生成DDL语句:

      MySQL workbench-2
    • 忽略生成外键,以及外键索引啥的:

      MySQL workbench-3
    • 生成的DDL语句:

      MySQL workbench-4
    • 到数据库执行。

    踩坑了

    最近团队微调,我被调整到另一个小团队。前两天接了个新需求,于是我依然使用MySQL workbench EER建模,结果好不容易建模完成了,却被告知这个项目用的数据库是PostgreSQL!

    于是就面临如下几种选择:

    • 重新找个支持导出PostgreSQL DDL语句的建模软件,再弄一遍。据我所知,macOS平台里没啥好的数据建模软件…
      • PowerDesigner用不了(除非装虚拟机,或者Wine);
      • Navicat太难用了(居然有人说Navicat是最好的数据库客户端,我只能给一个大写的服,在我看来,这货连IDEA自带数据库管理都比不上……这观点可能有点偏激,但现状是我做个查询,Navicat把查询按钮藏得很深);
      • IDEA宣布会开发类似功能,但一直没有动静;
      • 开源的PDMan,体验挺不错,但也得连个数据库控制版本。
    • 依然用MySQL workbench导出DDL,然后自己将MySQL DDL转换成PostgreSQL DDL。

    我选择了自己转换SQL语句。

    开源的DDL转换工具

    既然要转换SQL语句,我心想,业界肯定有相关的工具啊。于是上万能的GayHub搜了下,还真有,列出来:

    然而试用后,内心是崩溃的……生成出来的DDL要么有误,要么没有注释。

    自己开发工具

    考虑到我的诉求其实非常简单,只是个DDL语句转换而已,自己开发一个也不难。而且之前研读Mybatis通用Mapper源码时,知道Java世界里有个jsqlparser 的工具。

    花了10分钟简单了解了下jsqlparser 后,就开撸开发工具了……花了20分钟,初版写完了,然后和该项目的同事又花了20分钟验证了下,最终确定了如下的版本。代码贴出来:

    加依赖:

     <dependency>
        <groupId>com.github.jsqlparser</groupId>
        <artifactId>jsqlparser</artifactId>
        <version>1.2</version>
    </dependency>
    

    写代码:

    public class MysqlDdl2PgDdlUtil {
        public static void main(String[] args) throws IOException, JSQLParserException {
            // 你的MySQL DDL路径
            String mysqlDDLPath = "/Users/reno/Downloads/mysql.sql";
            String dDLs = FileUtils.readFileToString(new File(mysqlDDLPath));
    
            System.out.println(dDLs);
            System.out.println("++++++++++开始转换SQL语句+++++++++++++");
    
            Statements statements = CCJSqlParserUtil.parseStatements(dDLs);
    
            statements.getStatements()
                    .stream()
                    .map(statement -> (CreateTable) statement).forEach(ct -> {
                Table table = ct.getTable();
                List<ColumnDefinition> columnDefinitions = ct.getColumnDefinitions();
                List<String> comments = new ArrayList<>();
                List<ColumnDefinition> collect = columnDefinitions.stream()
                        .peek(columnDefinition -> {
                            List<String> columnSpecStrings = columnDefinition.getColumnSpecStrings();
    
                            int commentIndex = getCommentIndex(columnSpecStrings);
    
                            if (commentIndex != -1) {
                                int commentStringIndex = commentIndex + 1;
                                String commentString = columnSpecStrings.get(commentStringIndex);
    
                                String commentSql = genCommentSql(table.toString(), columnDefinition.getColumnName(), commentString);
                                comments.add(commentSql);
                                columnSpecStrings.remove(commentStringIndex);
                                columnSpecStrings.remove(commentIndex);
                            }
                            columnDefinition.setColumnSpecStrings(columnSpecStrings);
                        }).collect(Collectors.toList());
                ct.setColumnDefinitions(collect);
                String createSQL = ct.toString()
                        .replaceAll("`", "\"")
                        .replaceAll("BIGINT UNIQUE NOT NULL AUTO_INCREMENT", "BIGSERIAL PRIMARY KEY")
                        .replaceAll("BIGINT NULL AUTO_INCREMENT", "BIGSERIAL PRIMARY KEY")
                        .replaceAll("BIGINT NOT NULL AUTO_INCREMENT", "BIGSERIAL PRIMARY KEY")
                        .replaceAll("INT NOT NULL AUTO_INCREMENT", "BIGSERIAL PRIMARY KEY")
                        .replaceAll("INT NULL AUTO_INCREMENT", "BIGSERIAL PRIMARY KEY")
                        .replaceAll("IF NOT EXISTS", "")
                        .replaceAll("TINYINT", "SMALLINT")
                        .replaceAll("DATETIME", "TIMESTAMP")
                        .replaceAll(", PRIMARY KEY \\(\"id\"\\)", "");
    
                // 如果存在表注释
                if (createSQL.contains("COMMENT")) {
                    createSQL = createSQL.substring(0, createSQL.indexOf("COMMENT"));
                }
                System.out.println(createSQL + ";");
    
                comments.forEach(t -> System.out.println(t.replaceAll("`", "\"") + ";"));
            });
        }
    
        /**
         * 获得注释的下标
         *
         * @param columnSpecStrings columnSpecStrings
         * @return 下标
         */
        private static int getCommentIndex(List<String> columnSpecStrings) {
            for (int i = 0; i < columnSpecStrings.size(); i++) {
                if ("COMMENT".equalsIgnoreCase(columnSpecStrings.get(i))) {
                    return i;
                }
            }
            return -1;
        }
    
        /**
         * 生成COMMENT语句
         *
         * @param table        表名
         * @param column       字段名
         * @param commentValue 描述文字
         * @return COMMENT语句
         */
        private static String genCommentSql(String table, String column, String commentValue) {
            return String.format("COMMENT ON COLUMN %s.%s IS %s", table, column, commentValue);
        }
    }
    

    如代码所示,目前是借助jsqlparser 的SQL解析能力配合字符串替换的方式生成PostgreSQL的。

    效果演示

    转换前的DDL:

    -- -----------------------------------------------------
    -- Table `user`
    -- -----------------------------------------------------
    CREATE TABLE IF NOT EXISTS `user` (
      `id` INT NOT NULL AUTO_INCREMENT COMMENT 'id',
      `username` VARCHAR(16) NOT NULL COMMENT '用户名',
      `email` VARCHAR(255) NULL COMMENT '邮件',
      `password` VARCHAR(32) NOT NULL COMMENT '密码',
      `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      PRIMARY KEY (`id`));
    
    
    -- -----------------------------------------------------
    -- Table `movie`
    -- -----------------------------------------------------
    CREATE TABLE IF NOT EXISTS `movie` (
      `id` INT NOT NULL AUTO_INCREMENT COMMENT 'Id',
      `name` VARCHAR(255) NOT NULL COMMENT '名称',
      `user_id` INT NOT NULL COMMENT 'user.id',
      PRIMARY KEY (`id`))
    COMMENT = '电影表';
    

    转换后的DDL:

    CREATE TABLE "user"
    (
      "id"          BIGSERIAL PRIMARY KEY,
      "username"    VARCHAR(16)  NOT NULL,
      "email"       VARCHAR(255) NULL,
      "password"    VARCHAR(32)  NOT NULL,
      "create_time" TIMESTAMP    NULL DEFAULT CURRENT_TIMESTAMP
    );
    COMMENT ON COLUMN "user"."id" IS 'id';
    COMMENT ON COLUMN "user"."username" IS '用户名';
    COMMENT ON COLUMN "user"."email" IS '邮件';
    COMMENT ON COLUMN "user"."password" IS '密码';
    COMMENT ON COLUMN "user"."create_time" IS '创建时间';
    CREATE TABLE "movie"
    (
      "id"      BIGSERIAL PRIMARY KEY,
      "name"    VARCHAR(255) NOT NULL,
      "user_id" INT          NOT NULL
    );
    COMMENT ON COLUMN "movie"."id" IS 'Id';
    COMMENT ON COLUMN "movie"."name" IS '名称';
    COMMENT ON COLUMN "movie"."user_id" IS 'user.id';
    

    效果还是不错的,基本达到了我的要求。

    不足

    目前工具代码比较屎,如果想要改进,应该是要让工具理解MySQL DDL的词法,然后构建成例如Table、Column、Comment、Constraint、Index等对象例如:

    class Table {
        private String name;
        private Column column;
    }
    class Column {
        private String name;
        private String type;
        // 约束,例如非空等
        private Set<Constraint> constraints;
        // 索引
        private Index index;
    }
    class Index {
        private String name;
        private String type;
    }
    enum Constraint {
        NOT_NULL,...;
    }
    

    然后抽象一个方言枚举,并为不同的方言制作一个DDL Generator Handler,然后根据不同的方言生成不同数据库平台的DDL语句。

    为什么不改进?因为没有时间,工具是为工作服务的,目前能达到我的目的,就没动力修改了,未来有需求再改进吧。

    本文首发

    http://www.itmuch.com/work/mysql-ddl-2-pgsql-ddl/

    干货分享

    image.png

    相关文章

      网友评论

        本文标题:MySQL建表语句转PostgreSQL建表语句全纪录

        本文链接:https://www.haomeiwen.com/subject/ptsblqtx.html