1.首先加一下依赖
<dependency> <groupId>com.github.jsqlparser</groupId> <artifactId>jsqlparser</artifactId> <version>1.2</version> </dependency>
2.加下脚本
/**

• @author Ben.Zhou

• @version 1.0

• @description

• @data 2022/3/10 16:51
  */
  public class MysqlDdl2PgDdlUtil {
  public static void main(String[] args) throws IOException, JSQLParserException {
  // 你的MySQL DDL路径
  String mysqlDDLPath = “C:\Users\wb_xg20\Desktop\finance_super1.sql”;
  String dDLs = FileUtils.readFileToString(new File(mysqlDDLPath));

   System.out.println(dDLs);
   dDLs = dDLs.replaceAll("current_timestamp\\(\\) ON UPDATE current_timestamp\\(\\)", "current_timestamp")
           .replaceAll("current_timestamp\\(\\)", "current_timestamp")
           .replaceAll("current_timestamp\\(\\)", "current_timestamp")
           .replaceAll("int\\([0-9]*\\)", "INT")
           .replaceAll("tinyint\\([0-9]*\\)", "INT")
           .replaceAll("tinyINT","INT")
           .replaceAll("double\\([0-9]*,[0-9]*\\)","float8")
           .replaceAll("DEFAULT 00000000000","DEFAULT 0")
           .replaceAll("unsigned zerofill","")
           .replaceAll("bigint\\([0-9]*\\) NOT NULL AUTO_INCREMENT", "BIGSERIAL PRIMARY KEY")
           .replaceAll("bigint\\([0-9]*\\)","INT8");
   //dDLs = dDLs.replaceAll("bigint\\(19\\)","int8");
   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);
  

MySql建表脚本转CK

前置知识

主要是类型的转换、和空、非空的问题

整形

• ClickHouse则直接使用Int8、Int16、Int32和Int64指代4种大小的Int类型，其末尾的数字正好表明了占用字节的大小（8位=1字节）

• mysql中: int(M)的作用于int的范围明显是无关的，int(M)只是用来显示数据的宽度，我们能看到的宽度。当字段被设计为int类型，那么它的范围就已经被写死了(-2147483648~2147483647),与M无关。

CK的NOT NULL、NULL和Nullable的区别

1. 如果字段声明为 not null它的默认值已由它的类型而定，不允许再为其设置默认值,比如int为0，String为空串。不需要显式声明
2. 如果字段显式声明为null，默认值为NULL。
3. 如果字段声明为Nullable，默认为NULL，可设置默认值，损耗性能。

/**
 * @Author yhchen
 * @Date 2022/3/8 10:22
 */
public class MysqlToCK2 {
    public static void main(String[] args) {
    //将sql复制到这里
        String createTable = "DROP TABLE IF EXISTS `runoob_tbl`;\n" +
                "CREATE TABLE IF NOT EXISTS `runoob_tbl`(\n" +
                "   `runoob_id` int UNSIGNED AUTO_INCREMENT,\n" +
                "   `runoob_title` VARCHAR(100) NOT NULL,\n" +
                "   `runoob_author` VARCHAR(40) NOT NULL,\n" +
                "   `submission_date` DATE,\n" +
                "   PRIMARY KEY ( `runoob_id` )\n" +
                ")ENGINE=InnoDB DEFAULT CHARSET=utf8;";
        System.out.println("请检查语句,不要有空行 也就是只有'\\n'");
        String res = changeMysqlTableToClickHouse(createTable);
        System.out.println("转换后的建表语句为：");
        System.out.println(res);
        System.out.println("需要删除ENGINE的上一行最后的一个逗号");
    }

    public static String changeMysqlTableToClickHouse(String tableName) {
        String tables = tableName;
        String primaryKey = "`id`";//默认id
        String[] rows = tables.split("\n");
        StringBuilder replaceTables = new StringBuilder();
        Boolean haveKey = false;
        int i = 0;
        for (String row : rows) {
            // 注释，不处理
            if (row.contains("--")) {
                replaceTables.append(row + "\n");
                continue;
            }
            if (row.contains("KEY")) {
                if (row.contains("PRIMARY")) {
                    haveKey = true;
                    primaryKey = row.substring(row.indexOf("(") + 1, row.indexOf(")"));
                }
                // 跳过、不添加
                continue;
            }
            if (row.contains("ENGINE=InnoDB")) {
                // 引擎替换
                row = ") ENGINE = ReplacingMergeTree";
            }

            // 无关删除
            String changeRow = row.replaceAll("AUTO_INCREMENT", "")
                    .replaceAll("CHARACTER SET utf8mb4", "")
                    .replaceAll("CHARACTER SET utf8", "")
                    .replaceAll("ON UPDATE CURRENT_TIMESTAMP", "")
                    .replaceAll("CURRENT_TIMESTAMP", "")
                    .replaceAll("( DEFAULT CHARSET).*", "")
                    // 时间替换
                    .replaceAll("datetime DEFAULT NULL", " DateTime ")
                    .replaceAll(" datetime ", " DateTime ");

            /*String规则*/
            // 为空,字符串
            changeRow = changeRow.replaceAll("(` ).*(char).*(DEFAULT NULL)", "` String NULL");
            changeRow = changeRow.replaceAll("(` ).*(char).*(DEFAULT '')", "` String");
            // changeRow = changeRow.replaceAll("(DEFAULT '')", "NULL");
            // 非空,字符串
            changeRow = changeRow.replaceAll("(` ).*(char).*(NOT NULL)", "` String");
            changeRow = changeRow.replaceAll("text", "String");
            changeRow = changeRow.replaceAll("(DEFAULT NULL)", "NULL");
            changeRow = changeRow.replaceAll("(NOT NULL)", "");

            // 以空格分割
            String[] changeColumns = changeRow.split("[ ]");
            //      System.out.println(changeRow);
            // 含有int的替换规则
            if (changeColumns[3].contains("int") || changeColumns[3].contains("bigint")
                    ||changeColumns[3].contains("INT")) {
                changeColumns[3].replaceAll("INT","int");
                changeColumns[3].replaceAll("BIGINT","bigint");
                // 将括号内的数字拿出来
                int length = Integer.parseInt(changeColumns[3]
                        .replaceAll("bigint", "")
                        .replaceAll("tinyint", "")
                        .replaceAll("int", "")
                        .replaceAll("\\(", "")
                        .replaceAll("\\)", ""));
                // 获取数据类型
                String type = changeColumns[3].substring(0, changeColumns[3].indexOf("("));
                // 处理int 是否可以为空值
                String last = " NULL";
                String[] _int = {"Int8", "Int16", "Int32", "Int64"};
                if (changeRow.contains("DEFAULT NULL")) {
                    changeRow = changeRow.replaceAll("DEFAULT NULL", "");
                    for (int j = 0; j < _int.length; j++) {
                        _int[j] = _int[j] + last;
                    }
                }
                if ("tinyint".equals(type)) {
                    changeRow = changeRow
                            .replaceFirst(type + "\\(" + length + "\\)", _int[0]);
                } else if ("smallint".equals(type)) {
                    changeRow = changeRow
                            .replaceFirst(type + "\\(" + length + "\\)", _int[1]);
                } else if ("int".equals(type) || "mediumint".equals(type)) {
                    changeRow = changeRow
                            .replaceFirst(type + "\\(" + length + "\\)", _int[2]);
                } else {
                    changeRow = changeRow
                            .replaceFirst(type + "\\(" + length + "\\)", _int[3]);
                }
            }

            replaceTables.append(changeRow.trim() + "\n");
            if (i == 0) {
                replaceTables.append("\n");
            }
            i++;
        }
        if (replaceTables.toString().contains(",) ENGINE = Memory")) {
            String temp = replaceTables.substring(0, replaceTables.indexOf(",) ENGINE = Memory"));
            replaceTables = new StringBuilder(temp + ") ENGINE = Memory ");
        }
        replaceTables.toString().replaceAll("CREATE TABLE `" + tableName + "`", tableName + "_local");
        if (haveKey) {
            replaceTables.append("PRIMARY KEY " + primaryKey);
        }
        replaceTables.append("\nORDER BY " + primaryKey);
        replaceTables.append(";");
        return replaceTables.toString();
    }
}

String createSQL = ct.toString()
.replaceAll("`", “”")
//.replaceAll(“bigint (19)”,“int8”)
.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(“int8 NOT NULL AUTO_INCREMENT”, “BIGSERIAL PRIMARY KEY”)
.replaceAll(“IF NOT EXISTS”, “”)
.replaceAll(“TINYINT”, “SMALLINT”)
.replaceAll(“DATETIME”, “TIMESTAMP”)
.replaceAll(", PRIMARY KEY \(“id”\)", “”);

        // 如果存在表注释
        if (createSQL.contains("COMMENT")) {

            String substring = createSQL.substring(createSQL.indexOf("= '")+2);
            String commentSql = genCommentSql(table.toString(), substring);
            comments.add(commentSql);
            createSQL = createSQL.substring(0, createSQL.indexOf("COMMENT"));
            createSQL = createSQL.substring(0, createSQL.indexOf("ENGINE"));
        }
        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);
}

/**
 * 生成COMMENT语句
 *
 * @param table        表名
 * @param commentValue 描述文字
 * @return COMMENT语句
 */
private static String genCommentSql(String table, String commentValue) {
    return String.format("COMMENT ON TABLE %s IS %s", table, commentValue);
}

}