MySQL的SQL语句转PostgreSQL的SQL语句工具编写总结

1、起因
   在学习基于vue+springboot的前后端分离项目时，因项目需求变更，需要将mysql数据库全部迁移到postgersql数据库的需求，因而编写了一个基于jave的sql语句转换工具
2、代码实现描述
   基于jsqlparser工具，完成数据库sql语句的读取，解析，并通过字符串替换完成postgresql语句的拼接，目前支持drop语句，create语句和insert语句的转化
3、源码链接：mysql２postgre: 支持将mysql的sql语句转换为postgersql的sql语句
4、核心代码块 

public class Mysql2Postgresql {
  /**
     *
     * @param sqlFilePath：表示mysql的sql脚本路径
     * @param postgresqlModel：表示postgersql中的模式
     */

    public void generatePostgreSql(String sqlFilePath, String postgresqlModel) throws IOException, JSQLParserException {
        List<Statement> dropStatement = new ArrayList<>();
        List<Statement> createStatement = new ArrayList<>();
        List<Statement> insertStatement = new ArrayList<>();
        // MySQL DDL路径
        String dDLs = FileUtils.readFileToString(new File(sqlFilePath), "UTF-8");
        Statements statements = CCJSqlParserUtil.parseStatements(dDLs);

        for (Statement s: statements.getStatements()) {
            if (s instanceof Drop) {
                dropStatement.add(s);
            } else if (s instanceof CreateTable) {
                createStatement.add(s);
            } else if (s instanceof Insert) {
                insertStatement.add(s);
            }
        }
        System.out.println("======Drop Table BEGIN=====");
        dropStatement
                .stream()
                .map(statement -> (Drop) statement)
                .forEach(ct -> {
                    System.out.println(ct.toString().replaceAll("`", "") + ";");
                });
        System.out.println("======Drop Table END=====\n");
        System.out.println("======Insert Data BEGIN=====");
        insertStatement
                .stream()
                .map(statement -> (Insert) statement)
                .forEach(ct -> {
                    System.out.println(ct.toString()
                            .replaceAll(ct.getTable().getName(), postgresqlModel +"." + ct.getTable().getName())
                            .replaceAll("`", "") + ";");
                });
        System.out.println("======Insert Data END=====\n");
        System.out.println("======Create Table BEGIN=====");
        createStatement
                .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.getColumnSpecs();
                        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.setColumnSpecs(columnSpecStrings);
                    }).collect(Collectors.toList());
            ct.setColumnDefinitions(collect);
            String createSQL = ct.toString()
                    .replaceAll("bigint \\([0-9]+\\)", "bigint")
//                    .replaceAll("varchar \\(255\\)", "varchar\\(255\\)")
                    .replaceAll("AUTO_INCREMENT", "")
                    .replaceAll("USING BTREE", "")
                    .replaceAll("`", "")
                    .replaceAll(" ENGINE = InnoDB = 191 DEFAULT CHARSET = utf8 ROW_FORMAT = COMPACT ", "")
                    .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\"\\)", "")
                    .replaceAll("DEFAULT NULL", "NULL")
                    .replaceAll(", KEY [a-z_A-Z0-9]+ \\(.*\\)", ")")
                    .replaceAll(" ENGINE .* COMPACT ", "")
                    .replaceAll("datetime", "timestamp")
                    .replaceAll("int \\([0-9]+\\)", "int")
                    .replaceAll(", UNIQUE KEY [a-z_A-Z0-9]+ \\(.*\\) ", "")
                    .replaceAll("ENGINE .* = utf8", "")
                    .replaceAll(" user ", " \"user\" ");

            // 如果存在表注释
            if (createSQL.contains("COMMENT")) {
                createSQL = createSQL.substring(0, createSQL.indexOf("COMMENT"));
            }
            System.out.println(createSQL + ";");

            comments.forEach(t -> System.out.println(t.replaceAll("`", "") + ";"));
        });
        System.out.println("======Create Table END=====\n");
    }

}

5、注意点:该脚本不支持识别事务的相关语句，如commit，如有文档包含相关字段，需要提前删除后才能正常转化