MySQL建表語(yǔ)句轉(zhuǎn)PostgreSQL建表語(yǔ)句全紀(jì)錄

個(gè)人習(xí)慣用MySQL workbench EER數(shù)據(jù)建模，然后生成SQL語(yǔ)句到數(shù)據(jù)庫(kù)中執(zhí)行，這樣表之間的關(guān)系比較直觀。

像下面這樣：

• 畫(huà)圖

  

• 正向工程，生成DDL語(yǔ)句：

  

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

  

• 生成的DDL語(yǔ)句：

  

• 到數(shù)據(jù)庫(kù)執(zhí)行。

踩坑了

最近團(tuán)隊(duì)微調(diào)，我被調(diào)整到另一個(gè)小團(tuán)隊(duì)。前兩天接了個(gè)新需求，于是我依然使用MySQL workbench EER建模，結(jié)果好不容易建模完成了，卻被告知這個(gè)項(xiàng)目用的數(shù)據(jù)庫(kù)是PostgreSQL！

于是就面臨如下幾種選擇：

• 重新找個(gè)支持導(dǎo)出PostgreSQL DDL語(yǔ)句的建模軟件，再弄一遍。據(jù)我所知，macOS平臺(tái)里沒(méi)啥好的數(shù)據(jù)建模軟件…
  • PowerDesigner用不了（除非裝虛擬機(jī)，或者Wine）；
  • Navicat太難用了（居然有人說(shuō)Navicat是最好的數(shù)據(jù)庫(kù)客戶端，我只能給一個(gè)大寫(xiě)的服，在我看來(lái)，這貨連IDEA自帶數(shù)據(jù)庫(kù)管理都比不上……這觀點(diǎn)可能有點(diǎn)偏激，但現(xiàn)狀是我做個(gè)查詢，Navicat把查詢按鈕藏得很深）；
  • IDEA宣布會(huì)開(kāi)發(fā)類似功能，但一直沒(méi)有動(dòng)靜；
  • 開(kāi)源的PDMan，體驗(yàn)挺不錯(cuò)，但也得連個(gè)數(shù)據(jù)庫(kù)控制版本。
• 依然用MySQL workbench導(dǎo)出DDL，然后自己將MySQL DDL轉(zhuǎn)換成PostgreSQL DDL。

我選擇了自己轉(zhuǎn)換SQL語(yǔ)句。

開(kāi)源的DDL轉(zhuǎn)換工具

既然要轉(zhuǎn)換SQL語(yǔ)句，我心想，業(yè)界肯定有相關(guān)的工具啊。于是上萬(wàn)能的GayHub搜了下，還真有，列出來(lái)：

• mysql-to-postgres：<https://github.com/maxlapshin/mysql2postgres>
• mysql-postgresql-converter：<https://github.com/lanyrd/mysql-postgresql-converter>
• 多款工具配合使用：<https://yq.aliyun.com/articles/241> （不得不佩服這兄弟真有耐心?。。?/li>

然而試用后，內(nèi)心是崩潰的……生成出來(lái)的DDL要么有誤，要么沒(méi)有注釋。

自己開(kāi)發(fā)工具

考慮到我的訴求其實(shí)非常簡(jiǎn)單，只是個(gè)DDL語(yǔ)句轉(zhuǎn)換而已，自己開(kāi)發(fā)一個(gè)也不難。而且之前研讀Mybatis通用Mapper源碼時(shí)，知道Java世界里有個(gè)jsqlparser 的工具。

花了10分鐘簡(jiǎn)單了解了下jsqlparser 后，就開(kāi)擼開(kāi)發(fā)工具了……花了20分鐘，初版寫(xiě)完了，然后和該項(xiàng)目的同事又花了20分鐘驗(yàn)證了下，最終確定了如下的版本。代碼貼出來(lái)：

加依賴：

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

寫(xiě)代碼：

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("++++++++++開(kāi)始轉(zhuǎn)換SQL語(yǔ)句+++++++++++++");

        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("`", "\"") + ";"));
        });
    }

    /**
     * 獲得注釋的下標(biāo)
     *
     * @param columnSpecStrings columnSpecStrings
     * @return 下標(biāo)
     */
    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語(yǔ)句
     *
     * @param table        表名
     * @param column       字段名
     * @param commentValue 描述文字
     * @return COMMENT語(yǔ)句
     */
    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的。

效果演示

轉(zhuǎn)換前的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 '創(chuàng)建時(shí)間',
  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 = '電影表';

轉(zhuǎn)換后的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 '創(chuàng)建時(shí)間';
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';

效果還是不錯(cuò)的，基本達(dá)到了我的要求。

不足

目前工具代碼比較屎，如果想要改進(jìn)，應(yīng)該是要讓工具理解MySQL DDL的詞法，然后構(gòu)建成例如Table、Column、Comment、Constraint、Index等對(duì)象例如：

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,...;
}

然后抽象一個(gè)方言枚舉，并為不同的方言制作一個(gè)DDL Generator Handler，然后根據(jù)不同的方言生成不同數(shù)據(jù)庫(kù)平臺(tái)的DDL語(yǔ)句。

為什么不改進(jìn)？因?yàn)闆](méi)有時(shí)間，工具是為工作服務(wù)的，目前能達(dá)到我的目的，就沒(méi)動(dòng)力修改了，未來(lái)有需求再改進(jìn)吧。

原文

http://www.itmuch.com/work/mysql-ddl-2-pgsql-ddl/ ，轉(zhuǎn)載請(qǐng)說(shuō)明出處。

干貨分享