1 公用类


import lombok.Data;

@Data
public class DdlChangeInfo {
    /**
     * 表名
     */
    private String tableName;

    /**
     * 来源数据库DDL
     */
    private String sourceDdl;

    /**
     * 目标数据库DDL
     */
    private String targetDdl;

    /**
     * 来源数据库类型
     */
    private String sourceSqlType;

    /**
     * 目标数据库类型
     */
    private String targetSqlType;
}

import java.util.List;

public interface IDdlChangeStartegy {
    void change(List<DdlChangeInfo> ddlChangeInfoList);
}

2 转换类

2.1 MySQL->PostgreSQL

import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statements;
import net.sf.jsqlparser.statement.create.table.ColumnDefinition;
import net.sf.jsqlparser.statement.create.table.CreateTable;

import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

/**
 * Mysql转Postgresql DDL策略
 */
@Slf4j
public class Mysql2PosgresqlDdlChangeStartgy implements IDdlChangeStartegy {

    @Override
    public void change(List<DdlChangeInfo> ddlChangeInfoList) {
        for (DdlChangeInfo ddlChangeInfo : ddlChangeInfoList) {
            String ddl = ddlChangeInfo.getSourceDdl().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(" double ", " float8 ")
                    .replaceAll(" datetime ", " timestamp(6) ")
                    .replaceAll("DEFAULT 00000000000", "DEFAULT 0")
                    .replaceAll("unsigned zerofill", "")
                    .replaceAll("bigint\\([0-9]*\\) NOT NULL AUTO_INCREMENT", "BIGSERIAL PRIMARY KEY")
                    .replaceAll("bigint\\([0-9]*\\)", "INT8");


            try {
                Statements statements = CCJSqlParserUtil.parseStatements(ddl);

                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"));
                    }
                    StringBuilder postgresqlDdl = new StringBuilder();
                    postgresqlDdl.append(createSQL + ";");
                    comments.forEach(t -> postgresqlDdl.append(t.replaceAll("`", "\"") + ";"));

                    ddlChangeInfo.setTargetDdl(postgresqlDdl.toString());
                });
            } catch (Exception e) {
                log.error(e.getMessage(), e);
            }
        }
    }

    /**
     * 获得注释的下标
     *
     * @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);
    }
}

待补充
3 测试类
3.1 MySQL->PostgreSQL

import net.sf.jsqlparser.JSQLParserException;

import java.util.ArrayList;
import java.util.List;


public class MysqlDdl2PgDdlTestMain {
    public static void main(String[] args) throws JSQLParserException {
        List<DdlChangeInfo> ddlChangeInfoList = new ArrayList<>();
        DdlChangeInfo ddlChangeInfo0 = new DdlChangeInfo();
        ddlChangeInfo0.setSourceDdl("CREATE TABLE `t_order` (" +
                "  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键'," +
                "  `goods_id` int(11) DEFAULT NULL COMMENT '商品id'," +
                "  `goods_name` varchar(255) DEFAULT NULL COMMENT '商品名称'," +
                "  `dealer_id` int(11) DEFAULT NULL COMMENT '经销商id'," +
                "  `dealer_name` varchar(255) DEFAULT NULL COMMENT '经销商名称'," +
                "  `goods_num` int(11) DEFAULT NULL COMMENT '商品数量'," +
                "  `amount` decimal(10,2) DEFAULT NULL COMMENT '折前金额'," +
                "  `discount_amount` decimal(10,2) DEFAULT NULL COMMENT '折后金额'," +
                "  `discount` double DEFAULT NULL COMMENT '折扣'," +
                "  `order_time` datetime DEFAULT NULL COMMENT '订单时间'," +
                "  `status` int(11) DEFAULT NULL COMMENT '订单状态,0:未支付 1:已支付'," +
                "  `create_time` datetime DEFAULT NULL COMMENT '创建时间'," +
                "  `update_time` datetime DEFAULT NULL COMMENT '更新时间'," +
                "  PRIMARY KEY (`id`)" +
                ");");
        ddlChangeInfo0.setTableName("t_order");
        ddlChangeInfoList.add(ddlChangeInfo0);

        DdlChangeInfo ddlChangeInfo1 = new DdlChangeInfo();
        ddlChangeInfo1.setSourceDdl("CREATE TABLE `t_order_1` (" +
                "  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键'," +
                "  `goods_id` int(11) DEFAULT NULL COMMENT '商品id'," +
                "  `goods_name` varchar(255) DEFAULT NULL COMMENT '商品名称'," +
                "  `discount` double DEFAULT NULL COMMENT '折扣'," +
                "  `order_time` datetime DEFAULT NULL COMMENT '订单时间'," +
                "  `status` int(11) DEFAULT NULL COMMENT '订单状态,0:未支付 1:已支付'," +
                "  `create_time` datetime DEFAULT NULL COMMENT '创建时间'," +
                "  `update_time` datetime DEFAULT NULL COMMENT '更新时间'," +
                "  PRIMARY KEY (`id`)" +
                ");");
        ddlChangeInfo1.setTableName("t_order_1");
        ddlChangeInfoList.add(ddlChangeInfo1);

        System.out.println("init:" + ddlChangeInfoList);

        Mysql2PosgresqlDdlChangeStartgy mysql2PosgresqlDdlChangeStartgy = new Mysql2PosgresqlDdlChangeStartgy();
        mysql2PosgresqlDdlChangeStartgy.change(ddlChangeInfoList);

        System.out.println("changed:" + ddlChangeInfoList);
    }
}
作者:Jeebiz  创建时间:2022-10-16 01:17
 更新时间:2024-07-10 22:56