最近在使用simple-api-doc项目时，没有注意把数据库类型地址改成MySQL，使用了默认的H2上线使用了一段时间之后才发现，H2没有MySQL方便管理，因此需要迁移，找了不少迁移工具发现都不是很好用，尤其是数据类型等问题，造成迁移失败或者数据为空。其实JDBC就支持导入，因此自己写了个测试代码来实现迁移数据。

新建表

1. 最好先把表建起来，H2数据库和MySQL的字段的类型并不一样，不建议根据H2的表结构自动建MySQL表。
2. 我们使用flyway管理schema的结构，因此也已经建好了表，而且部分表不能迁移。

迁移表

数据表列表可以通过meta获取，也可以指定需要的表迁移，需要注意哪些表不用迁移，防止数据覆盖：

@Slf4j
public class H2ToMySQLAuto {
    private static final String H2_URL = "jdbc:h2:D:\\20250915\\apidoc-db";
    private static final String H2_USER = "user";
    private static final String H2_PASSWORD = "12345678";

    private static final String MYSQL_URL = "jdbc:mysql://xx.xx.xx.xx:3306/api_doc?characterEncoding=utf8&useUnicode=true&serverTimezone=UTC";
    private static final String MYSQL_USER = "user";
    private static final String MYSQL_PASSWORD = "12345678";

    private static final boolean TRUNCATE = true;

    public static void main(String[] args) throws Exception {
        List<String> tables = Arrays.asList(
                "T_API_DOC", "T_API_DOC_HISTORY", "T_API_DOC_SCHEMA", "T_API_LOG",
                "T_API_FOLDER", "T_API_GROUP", "T_API_PROJECT",
                "T_API_PROJECT_INFO", "T_API_PROJECT_INFO_DETAIL",
                "T_API_PROJECT_SHARE", "T_API_PROJECT_TASK", "T_API_USER", "T_API_USER_GROUP"
        );
        try (Connection h2Conn = DriverManager.getConnection(H2_URL, H2_USER, H2_PASSWORD);
             Connection mysqlConn = DriverManager.getConnection(MYSQL_URL, MYSQL_USER, MYSQL_PASSWORD)) {
            mysqlConn.setAutoCommit(false);
            for (String table : tables) {
                migrateTable(h2Conn, mysqlConn, table);
            }
            mysqlConn.commit();
            log.info("全部表迁移完成");
        }
    }

    private static void migrateTable(Connection h2Conn, Connection mysqlConn, String table) throws SQLException {
        log.info("开始迁移表: {}", table);
        // 读取表字段
        DatabaseMetaData meta = h2Conn.getMetaData();
        ResultSet rsCols = meta.getColumns(null, null, table, null);
        List<String> columns = new ArrayList<>();
        while (rsCols.next()) {
            columns.add(rsCols.getString("COLUMN_NAME"));
        }
        rsCols.close();
        if (columns.isEmpty()) {
            log.info("未找到表结构: {}", table);
            return;
        }
        String colNames = String.join(", ", columns);
        String placeholders = String.join(", ", Collections.nCopies(columns.size(), "?"));
        String mysqlTable = getMySqlTable(table);
        String selectSql = "SELECT " + colNames + " FROM " + table;
        String truncateSql = "TRUNCATE TABLE " + mysqlTable;
        String insertSql = "INSERT INTO " + mysqlTable + " (" + colNames + ") VALUES (" + placeholders + ")";
        try (PreparedStatement psSelect = h2Conn.prepareStatement(selectSql);
             PreparedStatement psInsert = mysqlConn.prepareStatement(insertSql);
             ResultSet rs = psSelect.executeQuery()) {
            if (TRUNCATE) {
                mysqlConn.createStatement().execute(truncateSql);
            }
            ResultSetMetaData rsmd = rs.getMetaData();
            int colCount = rsmd.getColumnCount();
            while (rs.next()) {
                for (int i = 1; i <= colCount; i++) {
                    Object value = rs.getObject(i);
                    psInsert.setObject(i, value);
                }
                psInsert.addBatch();
            }
            psInsert.executeBatch();
            log.info("表 {} ➡ {} 迁移完成", table, mysqlTable);
        }
    }

    private static String getMySqlTable(String tableName) { // 表名映射
        return tableName.toLowerCase();
    }
}