MySQL建表语句转PostgreSQL建表语句全纪录-白红宇

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

阅读量：6957 次

发布时间：2019-06-27

本文共 6804 字，大约阅读时间需要 22 分钟。

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

像下面这样：

画图

正向工程，生成DDL语句：

忽略生成外键，以及外键索引啥的：

生成的DDL语句：

到数据库执行。

踩坑了

最近团队微调，我被调整到另一个小团队。前两天接了个新需求，于是我依然使用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搜了下，还真有，列出来：

mysql-to-postgres：

mysql-postgresql-converter：

多款工具配合使用：（不得不佩服这兄弟真有耐心啊！）

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

自己开发工具

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

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

加依赖：


        
     
      com.github.jsqlparser
         
     
      jsqlparser
         
     
      1.2

写代码：

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
    
      columnDefinitions = ct.getColumnDefinitions();            List
     
       comments = new ArrayList<>();            List
      
        collect = columnDefinitions.stream()                    .peek(columnDefinition -> {                        List
       
         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
        
          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
    
      constraints;    // 索引    private Index index;}class Index {    private String name;    private String type;}enum Constraint {    NOT_NULL,...;}

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

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

原文

，转载请说明出处。

干货分享

转载于:https://my.oschina.net/eacdy/blog/2995462

你可能感兴趣的文章

ubuntu重啟網卡提示"Job failed while stopping"解決方法！【参考别人的信息】