数据库迁移方案

/**
 *
 * @param tenantId 模版租户id
 * @param targetDbname 目标租户数据库名称
 */
private void createDateBase(String tenantId, String targetDbname) {
    try {
        DbBase db = DbTypeUtil.getDb(dbValue);

        //查询模版租户数据库信息
        QueryWrapper<TenantEntity> tenantQuery = new QueryWrapper<>();
        tenantQuery.lambda()
                .eq(TenantEntity::getEnCode, tenantId);
        TenantEntity templateTenant = tenantMapper.selectOne(tenantQuery);
        if (ObjectUtil.isNull(templateTenant)) {
            throw new HandleException("模版租户不存在!");
        }

        //jdbc:mysql://{host}:{port}/{dbname}?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&serverTimezone=GMT%2B8&useSSL=false
        //获取源数据库连接
        String url = db.getDefaultPrepareUrl().replace("{dbname}", templateTenant.getDbName())
                .replace("{host}", String.valueOf(dbValue.getHost()))
                .replace("{port}", String.valueOf(dbValue.getPort()))
                .replace("{schema}", StringUtil.isNotEmpty(dbValue.getDbSchema()) ? dbValue.getDbSchema() : "");
        Connection sourceConn = JdbcUtil.getConn(db.getDriver(), dbValue.getUserName(), dbValue.getPassword(),
                url);

        long start = System.currentTimeMillis();

        migrateTable(sourceConn, templateTenant.getDbName(), targetDbname);

        long end = System.currentTimeMillis();
        log.error("耗时:" + (end - start));

        sourceConn.close();


    }catch (Exception e) {
        e.printStackTrace();
    }
}

 /**
     * 数据库迁移表和数据
     * @param sourceConn 源数据源
     * @param sourceDbName 源数据库名称
     * @param targetDbName 需要创建的数据库名称
     */
    private void migrateTable(Connection sourceConn, String sourceDbName, String targetDbName) {
        if (sourceConn == null || StrUtil.isBlank(sourceDbName) || StrUtil.isBlank(targetDbName)) {
            log.error("目标数据库或源数据库不能为空");
            return;
        }
        Statement stmt = null;
        try {
            // 创建数据库连接
//                conn = DriverManager.getConnection(JDBC_URL, USER, PASSWORD);
            sourceConn.setAutoCommit(false);
            stmt = sourceConn.createStatement();

            // 创建目标数据库
            stmt.executeUpdate("CREATE DATABASE IF NOT EXISTS `" + targetDbName + "`");
            log.info("目标数据库 {} 已创建", targetDbName);

            // 获取源数据库所有表
            List<String> tables = new ArrayList<>();
            ResultSet rs = stmt.executeQuery("SHOW TABLES FROM `" + sourceDbName + "`");
            while (rs.next()) {
                tables.add(rs.getString(1));
            }

            // 复制表结构+数据
            for (String table : tables) {
                log.info("正在复制表", table);

                // 获取建表语句
                rs = stmt.executeQuery("SHOW CREATE TABLE `" + sourceDbName + "`.`" + table + "`");
                rs.next();
                String createTableSQL = rs.getString(2);

                // 在目标库创建表
                stmt.executeUpdate("USE `" + targetDbName + "`");
                stmt.executeUpdate(createTableSQL);

                // 复制数据
                stmt.executeUpdate("INSERT INTO `" + targetDbName + "`.`" + table + "` SELECT * FROM `" + sourceDbName + "`.`" + table + "`");
            }

            // 复制存储过程
            rs = stmt.executeQuery("SHOW PROCEDURE STATUS WHERE Db = '" + sourceDbName + "'");
            while (rs.next()) {
                String procName = rs.getString("Name");
                ResultSet rsProc = stmt.executeQuery("SHOW CREATE PROCEDURE `" + sourceDbName + "`.`" + procName + "`");
                rsProc.next();
                String createProc = rsProc.getString("Create Procedure");
                stmt.executeUpdate(createProc);
            }

            // 复制函数
            rs = stmt.executeQuery("SHOW FUNCTION STATUS WHERE Db = '" + sourceDbName + "'");
            while (rs.next()) {
                String funcName = rs.getString("Name");
                ResultSet rsFunc = stmt.executeQuery("SHOW CREATE FUNCTION `" + sourceDbName + "`.`" + funcName + "`");
                rsFunc.next();
                String createFunc = rsFunc.getString("Create Function");
                stmt.executeUpdate(createFunc);
            }

            // 复制触发器
            rs = stmt.executeQuery("SHOW TRIGGERS FROM `" + sourceDbName + "`");
            while (rs.next()) {
                String triggerName = rs.getString("Trigger");
                ResultSet rsTrigger = stmt.executeQuery("SHOW CREATE TRIGGER `" + sourceDbName + "`.`" + triggerName + "`");
                rsTrigger.next();
                String createTrigger = rsTrigger.getString("SQL Original Statement");
                stmt.executeUpdate(createTrigger);
            }

            // 复制视图
            rs = stmt.executeQuery("SHOW FULL TABLES IN `" + sourceDbName + "` WHERE TABLE_TYPE LIKE 'VIEW'");
            while (rs.next()) {
                String viewName = rs.getString(1);
                ResultSet rsView = stmt.executeQuery("SHOW CREATE VIEW `" + sourceDbName + "`.`" + viewName + "`");
                rsView.next();
                String createView = rsView.getString("Create View");
                stmt.executeUpdate(createView);
            }

            sourceConn.commit();
            log.info("数据库复制完成");

        } catch (SQLException e) {
            try {
                if (sourceDbName != null) sourceConn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            log.error("复制数据库时出错: {}", e.getMessage());
            e.printStackTrace();
        } finally {
            try {
                if (stmt != null) stmt.close();
                if (sourceConn != null) sourceConn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }