mysql以数据源1的库为基准，将表结构同步给数据源2，增加表或者增加字段

package com.cf.newbarcode.api.config;

import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
import java.util.*;

public class DatabaseStructureComparator {

    // 数据库连接信息（请根据实际情况修改）
    private static final String DB1000_URL = "jdbc:mysql://192.168.1.192:3306/cfmxxx?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true";
    private static final String DB1000_USER = "root";
    private static final String DB1000_PASSWORD = "@123";
    private static final String DB1000_SCHEMA = "cfxxx";

    private static final String DB1161_URL = "jdbc:mysql://10.32.6.299:3306/cfmxxx?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true";
    private static final String DB1161_USER = "root";
    private static final String DB1161_PASSWORD = "@123";
    private static final String DB1161_SCHEMA = "cfxxx";

    private static final String OUTPUT_SQL_FILE = "sync_table_structure.sql";

    public static void main(String[] args) {
        Connection conn1000 = null;
        Connection conn1161 = null;
        FileWriter writer = null;

        try {
            // 加载驱动并连接数据库
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn1000 = DriverManager.getConnection(DB1000_URL, DB1000_USER, DB1000_PASSWORD);
            conn1161 = DriverManager.getConnection(DB1161_URL, DB1161_USER, DB1161_PASSWORD);
            writer = new FileWriter(OUTPUT_SQL_FILE);

            // 获取基准库表名（仅cfmoto库）
            Set<String> tables1000 = getTableNamesBySchema(conn1000, DB1000_SCHEMA);
            // 获取目标库表名（仅cfmoto库，转小写用于匹配）
            Set<String> tables1161 = getTableNamesBySchemaLowercase(conn1161, DB1161_SCHEMA);

            System.out.println("数据库1000中【" + DB1000_SCHEMA + "】库有效表共 " + tables1000.size() + " 张，开始对比...");

            // 遍历基准库表进行对比
            for (String tableName : tables1000) {
                System.out.println("正在处理表：" + tableName);

                // 目标库不存在该表 → 生成创建表SQL
                if (!tables1161.contains(tableName.toLowerCase())) {
                    System.out.println("表 " + tableName + " 在1161的【" + DB1161_SCHEMA + "】库中不存在，生成创建SQL");
                    String createTableSql = getCreateTableSql(conn1000, tableName);
                    if (createTableSql != null) {
                        writer.write("-- 表 " + tableName + " 在1161库中不存在，创建表\n");
                        writer.write(createTableSql + ";\n\n");
                    }
                    continue;
                }

                // 目标库存在该表 → 对比字段差异（含注释）
                Map<String, String> columns1000 = getTableColumnsWithComment(conn1000, DB1000_SCHEMA, tableName);
                Map<String, String> columns1161 = getTableColumnsWithComment(conn1161, DB1161_SCHEMA, tableName);

                // 生成缺失字段的ALTER语句（包含COMMENT）
                List<String> addSqlList = new ArrayList<>();
                for (Map.Entry<String, String> entry : columns1000.entrySet()) {
                    String columnName = entry.getKey();
                    String columnDef = entry.getValue();

                    if (!columns1161.containsKey(columnName)) {
                        String addSql = String.format("ALTER TABLE `%s` ADD COLUMN %s;", tableName, columnDef);
                        addSqlList.add(addSql);
                        System.out.println("表 " + tableName + " 缺失字段：" + columnName);
                    }
                }

                // 写入SQL文件
                if (!addSqlList.isEmpty()) {
                    writer.write("-- 表 " + tableName + " 缺失字段的添加SQL\n");
                    for (String sql : addSqlList) {
                        writer.write(sql + "\n");
                    }
                    writer.write("\n");
                }
            }

            System.out.println("对比完成！SQL文件已输出到：" + OUTPUT_SQL_FILE);

        } catch (ClassNotFoundException e) {
            System.err.println("MySQL驱动未找到：" + e.getMessage());
        } catch (SQLException | IOException e) {
            e.printStackTrace();
        } finally {
            // 关闭资源
            try {
                if (conn1000 != null) conn1000.close();
                if (conn1161 != null) conn1161.close();
                if (writer != null) writer.close();
            } catch (SQLException | IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 获取指定库的表名（仅目标schema）
     */
    private static Set<String> getTableNamesBySchema(Connection conn, String schema) throws SQLException {
        Set<String> tableNames = new HashSet<>();
        String sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ? AND TABLE_TYPE = 'BASE TABLE'";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, schema);
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            tableNames.add(rs.getString("TABLE_NAME"));
        }
        rs.close();
        pstmt.close();
        return tableNames;
    }

    /**
     * 获取指定库的表名（转小写，用于匹配）
     */
    private static Set<String> getTableNamesBySchemaLowercase(Connection conn, String schema) throws SQLException {
        Set<String> tableNames = new HashSet<>();
        String sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ? AND TABLE_TYPE = 'BASE TABLE'";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, schema);
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            tableNames.add(rs.getString("TABLE_NAME").toLowerCase());
        }
        rs.close();
        pstmt.close();
        return tableNames;
    }

    /**
     * 核心方法：修复大数值超出Integer范围问题，用Long接收长度值
     */
    private static Map<String, String> getTableColumnsWithComment(Connection conn, String schema, String tableName) throws SQLException {
        Map<String, String> columns = new HashMap<>();
        String sql = "SELECT " +
                "COLUMN_NAME, " +
                "DATA_TYPE, " +
                "CHARACTER_MAXIMUM_LENGTH, " + // 字符类型长度（可能为大数值，如4294967295）
                "NUMERIC_PRECISION, " +        // 数值类型精度
                "NUMERIC_SCALE, " +             // 数值类型小数位
                "IS_NULLABLE, " +
                "EXTRA, " +
                "COLUMN_COMMENT " +
                "FROM INFORMATION_SCHEMA.COLUMNS " +
                "WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? " +
                "ORDER BY ORDINAL_POSITION";

        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, schema);
        pstmt.setString(2, tableName);
        ResultSet rs = pstmt.executeQuery();

        while (rs.next()) {
            String columnName = rs.getString("COLUMN_NAME");
            String dataType = rs.getString("DATA_TYPE");
            // 核心修复：用Long接收大数值，避免超出Integer范围
            Long charMaxLength = rs.getObject("CHARACTER_MAXIMUM_LENGTH") != null ? rs.getLong("CHARACTER_MAXIMUM_LENGTH") : 0L;
            Integer numericPrecision = rs.getObject("NUMERIC_PRECISION") != null ? rs.getInt("NUMERIC_PRECISION") : 0;
            Integer numericScale = rs.getObject("NUMERIC_SCALE") != null ? rs.getInt("NUMERIC_SCALE") : 0;
            String isNullable = rs.getString("IS_NULLABLE");
            String extra = rs.getString("EXTRA");
            String comment = rs.getString("COLUMN_COMMENT");

            // 构建字段定义（包含注释）
            String columnDef = buildColumnDefinition(
                    columnName, dataType, charMaxLength,
                    numericPrecision, numericScale,
                    isNullable, extra, comment
            );
            columns.put(columnName, columnDef);
        }

        rs.close();
        pstmt.close();
        return columns;
    }

    /**
     * 构建字段定义：参数类型改为Long，兼容大数值
     */
    private static String buildColumnDefinition(
            String columnName, String dataType, Long charMaxLength,
            Integer numericPrecision, Integer numericScale,
            String isNullable, String extra, String comment) {

        StringBuilder def = new StringBuilder();
        def.append("`").append(columnName).append("` ");
        def.append(dataType);

        // 处理长度/精度：charMaxLength为Long类型，支持大数值
        if ("decimal".equalsIgnoreCase(dataType)) {
            if (numericPrecision > 0) {
                def.append("(").append(numericPrecision).append(",").append(numericScale).append(")");
            }
        } else if (charMaxLength > 0 && !Arrays.asList(
                "int", "bigint", "tinyint", "smallint", "mediumint",
                "float", "double", "date", "datetime", "timestamp", "time"
        ).contains(dataType.toLowerCase())) {
            // 大数值（如4294967295）直接拼接，MySQL可识别
            def.append("(").append(charMaxLength).append(")");
        }

        // 处理NULL/NOT NULL
        def.append(" ").append("NO".equals(isNullable) ? "NOT NULL" : "NULL");

        // 处理额外信息
        if (extra != null && !extra.isEmpty()) {
            def.append(" ").append(extra);
        }

        // 处理注释
        String escapedComment = (comment == null || comment.trim().isEmpty()) ? "" : comment.replace("'", "\\'");
        def.append(" COMMENT '").append(escapedComment).append("'");

        return def.toString();
    }

    /**
     * 获取表的创建SQL（包含注释）
     */
    private static String getCreateTableSql(Connection conn, String tableName) throws SQLException {
        String sql = "SHOW CREATE TABLE `" + tableName + "`";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();
        String createSql = null;
        if (rs.next()) {
            createSql = rs.getString(2);
        }
        rs.close();
        pstmt.close();
        return createSql;
    }
}