package com.scrbg.datamigration.modules.turnover;
import java.sql.*;
import java.util.*;
import java.util.concurrent.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
import java.util.stream.IntStream;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* 数据库结构同步工具
* 支持表结构、索引、外键等的同步，具备错误恢复和并行处理能力
*
* 优化点：
* 1. 增强错误处理和重试机制
* 2. 优化日志记录，增加详细度和可读性
* 3. 改进索引同步逻辑，减少不必要的重建
* 4. 增强主键同步功能
* 5. 优化连接管理和资源释放
* 6. 添加更多错误处理策略
* 7. 改进数据类型处理逻辑
* 8. 优化大表处理性能
*/
public class DatabaseSchemaSync {
private static final Logger logger = LoggerFactory.getLogger(DatabaseSchemaSync.class);
private static final int MAX_RETRY_ATTEMPTS = 5;
private static final long RETRY_DELAY_MS = 2000;
// 配置类
public static class Config {
public boolean syncTables = true;
public boolean syncColumns = true;
public boolean syncIndexes = true;
public boolean syncForeignKeys = true;
public boolean syncTableOptions = true;
public boolean syncPrimaryKeys = true; // 新增主键同步选项
public boolean dropExtraObjects = true;
public boolean skipDataValidation = false;
public boolean verboseLogging = true;
public boolean safeAlterMode = true;
public boolean quoteIdentifiers = true;
public int threadPoolSize = Runtime.getRuntime().availableProcessors();
public List<String> excludedTables = new ArrayList<>();
public List<String> includedDatabases = new ArrayList<>();
public long timeoutMinutes = 30;
}
// 系统数据库列表
private static final Set<String> SYSTEM_DBS = Collections.unmodifiableSet(new HashSet<>(Arrays.asList(
"information_schema", "mysql", "performance_schema", "sys",
"master", "tempdb", "undo_log", "nacos", "pcwp_base",
"pcwp_log", "seata", "sync_schema_record", "xxl_job", "pcwp_report"
)));
// 错误处理策略接口
@FunctionalInterface
private interface ErrorHandler {
boolean handle(SQLException e, ErrorContext ctx);
}
// 错误上下文
private static class ErrorContext {
final String operation;
String definition;
final String tableName;
final String dbName;
String sql;
int retryCount = 0;
ErrorContext(String operation, String definition, String dbName, String tableName) {
this.operation = operation;
this.definition = definition;
this.dbName = dbName;
this.tableName = tableName;
this.sql = buildSQL();
}
private String buildSQL() {
switch (operation) {
case "ALTER TABLE":
return String.format("ALTER TABLE %s.%s %s",
quoteIdentifier(dbName), quoteIdentifier(tableName), definition);
case "CREATE TABLE":
return definition;
default:
return operation + " " + definition;
}
}
void incrementRetry() {
retryCount++;
}
}
// 表属性
private static class TableProperties {
String engine;
String charset;
String collation;
String rowFormat;
String comment;
String autoIncrement;
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
TableProperties that = (TableProperties) o;
return Objects.equals(engine, that.engine) &&
Objects.equals(charset, that.charset) &&
Objects.equals(collation, that.collation) &&
Objects.equals(rowFormat, that.rowFormat) &&
Objects.equals(comment, that.comment) &&
Objects.equals(autoIncrement, that.autoIncrement);
}
@Override
public int hashCode() {
return Objects.hash(engine, charset, collation, rowFormat, comment, autoIncrement);
}
}
// 索引信息
private static class IndexInfo {
String name;
boolean unique;
List<String> columns;
List<String> collations;
List<String> orders;
String indexType;
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
IndexInfo indexInfo = (IndexInfo) o;
return unique == indexInfo.unique &&
Objects.equals(name, indexInfo.name) &&
Objects.equals(columns, indexInfo.columns) &&
Objects.equals(collations, indexInfo.collations) &&
Objects.equals(orders, indexInfo.orders) &&
Objects.equals(indexType, indexInfo.indexType);
}
@Override
public int hashCode() {
return Objects.hash(name, unique, columns, collations, orders, indexType);
}
}
// 列信息
private static class ColumnInfo {
String name;
String type;
int size;
boolean nullable;
String defaultValue;
String comment;
String charset;
String collation;
boolean autoIncrement;
String getFullDefinition(boolean quoteIdentifiers) {
StringBuilder colDef = new StringBuilder(quoteIdentifier(name, quoteIdentifiers))
.append(" ").append(getTypeDefinition());
if (charset != null) colDef.append(" CHARACTER SET ").append(charset);
if (collation != null) colDef.append(" COLLATE ").append(collation);
colDef.append(nullable ? " NULL" : " NOT NULL");
if (autoIncrement) colDef.append(" AUTO_INCREMENT");
if (defaultValue != null) colDef.append(" DEFAULT ").append(defaultValue);
if (comment != null) colDef.append(" COMMENT '").append(escapeSql(comment)).append("'");
return colDef.toString();
}
String getTypeDefinition() {
StringBuilder typeDef = new StringBuilder(type);
if (size > 0 && type.matches("(?i)(decimal|numeric|float|double|varchar|char)")) {
typeDef.append("(").append(size);
if (type.contains(",")) {
typeDef.append(",").append(type.split(",")[1].replace(")", ""));
}
typeDef.append(")");
}
return typeDef.toString();
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
ColumnInfo that = (ColumnInfo) o;
return size == that.size &&
nullable == that.nullable &&
autoIncrement == that.autoIncrement &&
Objects.equals(name, that.name) &&
Objects.equals(type, that.type) &&
Objects.equals(defaultValue, that.defaultValue) &&
Objects.equals(comment, that.comment) &&
Objects.equals(charset, that.charset) &&
Objects.equals(collation, that.collation);
}
}
// 外键信息
private static class ForeignKeyInfo {
String name;
String column;
String pkTable;
String pkColumn;
short updateRule;
short deleteRule;
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
ForeignKeyInfo that = (ForeignKeyInfo) o;
return updateRule == that.updateRule &&
deleteRule == that.deleteRule &&
Objects.equals(name, that.name) &&
Objects.equals(column, that.column) &&
Objects.equals(pkTable, that.pkTable) &&
Objects.equals(pkColumn, that.pkColumn);
}
}
// 表结构信息
private static class TableSchema {
Map<String, ColumnInfo> columns = new LinkedHashMap<>();
Set<String> primaryKeys = new HashSet<>();
Set<IndexInfo> indexes = new HashSet<>();
Set<ForeignKeyInfo> foreignKeys = new HashSet<>();
TableProperties properties = new TableProperties();
}
private final Config config;
private final ExecutorService executor;
private final Map<String, ErrorHandler> errorHandlers = new LinkedHashMap<>();
public DatabaseSchemaSync(Config config) {
this.config = config;
this.executor = Executors.newFixedThreadPool(config.threadPoolSize);
initializeErrorHandlers();
}
private void initializeErrorHandlers() {
errorHandlers.put("PRIMARY KEY must be NOT NULL", (e, ctx) -> {
ctx.definition = ctx.definition.replace(" NULL", " NOT NULL");
return true;
});
errorHandlers.put("Invalid default value", (e, ctx) -> {
ctx.definition = ctx.definition.replaceAll("DEFAULT\\s+[^,]+", "");
return true;
});
errorHandlers.put("Duplicate key name", (e, ctx) -> {
String indexName = extractIndexName(ctx.definition);
if (indexName != null) {
ctx.definition = ctx.definition.replace(
quoteIdentifier(indexName, config.quoteIdentifiers),
quoteIdentifier(indexName + "_" + System.currentTimeMillis(), config.quoteIdentifiers));
return true;
}
return false;
});
// 处理外键约束错误
errorHandlers.put("foreign key constraint", (e, ctx) -> {
logger.warn("外键约束错误: {}", e.getMessage());
return false; // 不自动重试
});
// 处理表不存在错误
errorHandlers.put("Table doesn't exist", (e, ctx) -> {
logger.warn("表不存在错误: {}.{}", ctx.dbName, ctx.tableName);
return false;
});
}
/**
* 执行数据库结构同步
*/
public void sync(String sourceUrl, String targetUrl,
String sourceUser, String sourcePass,
String targetUser, String targetPass) throws SQLException {
long startTime = System.currentTimeMillis();
logger.info("开始数据库结构同步: {} -> {}", sourceUrl, targetUrl);
try (Connection sourceConn = createConnection(sourceUrl, sourceUser, sourcePass);
Connection targetConn = createConnection(targetUrl, targetUser, targetPass)) {
disableForeignKeyChecks(targetConn);
syncDatabaseStructure(sourceConn, targetConn);
enableForeignKeyChecks(targetConn);
long duration = (System.currentTimeMillis() - startTime) / 1000;
logger.info("数据库结构同步完成! 耗时: {}秒", duration);
} finally {
shutdownExecutor();
}
}
private void shutdownExecutor() {
executor.shutdown();
try {
if (!executor.awaitTermination(config.timeoutMinutes, TimeUnit.MINUTES)) {
executor.shutdownNow();
logger.warn("线程池未在超时时间内完成");
}
} catch (InterruptedException e) {
executor.shutdownNow();
Thread.currentThread().interrupt();
logger.error("线程池关闭中断", e);
}
}
private void syncDatabaseStructure(Connection sourceConn, Connection targetConn) throws SQLException {
List<String> databases = getNonSystemDatabases(sourceConn);
List<Future<?>> futures = new ArrayList<>();
for (String db : databases) {
if (!config.includedDatabases.isEmpty() && !config.includedDatabases.contains(db)) {
logger.debug("跳过数据库: {}", db);
continue;
}
futures.add(executor.submit(() -> {
try {
syncDatabase(sourceConn, targetConn, db);
} catch (SQLException e) {
logger.error("同步数据库 {} 失败: {}", db, e.getMessage());
}
}));
}
awaitCompletion(futures, "数据库同步");
}
private void syncDatabase(Connection sourceConn, Connection targetConn, String dbName) throws SQLException {
logger.info("开始同步数据库: {}", dbName);
createDatabaseIfNotExists(targetConn, dbName);
Set<String> sourceTables = getTables(sourceConn, dbName).stream()
.filter(t -> !config.excludedTables.contains(t))
.collect(Collectors.toSet());
Set<String> targetTables = getTables(targetConn, dbName);
List<Future<?>> tableFutures = sourceTables.stream()
.map(tableName -> executor.submit(() -> {
try {
syncTableWithRetry(sourceConn, targetConn, dbName, tableName, MAX_RETRY_ATTEMPTS);
} catch (SQLException e) {
logger.error("同步表 {}.{} 失败: {}", dbName, tableName, e.getMessage());
}
}))
.collect(Collectors.toList());
awaitCompletion(tableFutures, "表同步");
// 删除多余表
if (config.dropExtraObjects) {
for (String table : targetTables) {
if (!sourceTables.contains(table)) {
try {
dropTable(targetConn, dbName, table);
} catch (SQLException e) {
logger.error("删除表 {}.{} 失败: {}", dbName, table, e.getMessage());
}
}
}
}
logger.info("数据库 {} 同步完成", dbName);
}
private void syncTableWithRetry(Connection sourceConn, Connection targetConn,
String dbName, String tableName, int retries) throws SQLException {
for (int i = 0; i < retries; i++) {
try {
syncTable(sourceConn, targetConn, dbName, tableName);
return;
} catch (SQLException e) {
if (i == retries - 1) {
logger.error("同步表 {}.{} 失败，已达最大重试次数", dbName, tableName, e);
throw e;
}
logger.warn("重试同步表 {}.{} ({}/{}) 原因: {}",
dbName, tableName, i+1, retries, e.getMessage());
sleep(RETRY_DELAY_MS * (i+1));
}
}
}
private void syncTable(Connection sourceConn, Connection targetConn,
String dbName, String tableName) throws SQLException {
logger.info("同步表结构: {}.{}", dbName, tableName);
TableSchema sourceSchema = getTableSchema(sourceConn, dbName, tableName);
TableSchema targetSchema = getTableSchema(targetConn, dbName, tableName);
// 表不存在则创建
if (targetSchema.columns.isEmpty()) {
createTable(targetConn, dbName, tableName, sourceSchema);
return;
}
// 同步表属性
if (config.syncTableOptions && !sourceSchema.properties.equals(targetSchema.properties)) {
syncTableProperties(targetConn, dbName, tableName, sourceSchema.properties);
}
// 同步列结构
if (config.syncColumns) {
syncTableColumns(targetConn, dbName, tableName, sourceSchema, targetSchema);
}
// 同步主键
if (config.syncPrimaryKeys && !sourceSchema.primaryKeys.equals(targetSchema.primaryKeys)) {
syncPrimaryKeys(targetConn, dbName, tableName, sourceSchema.primaryKeys, targetSchema.primaryKeys);
}
// 同步索引
if (config.syncIndexes) {
syncIndexes(targetConn, dbName, tableName, sourceSchema.indexes, targetSchema.indexes);
}
// 同步外键
if (config.syncForeignKeys) {
syncForeignKeys(targetConn, dbName, tableName, sourceSchema.foreignKeys);
}
logger.info("表 {}.{} 同步完成", dbName, tableName);
}
private void createTable(Connection conn, String dbName, String tableName,
TableSchema schema) throws SQLException {
String createSql = generateCreateTable(dbName, tableName, schema);
logger.debug("创建表SQL:\n{}", createSql);
ErrorContext ctx = new ErrorContext("CREATE TABLE", createSql, dbName, tableName);
executeWithErrorHandling(conn, ctx);
logger.info("已创建表: {}.{}", dbName, tableName);
}
private String generateCreateTable(String dbName, String tableName, TableSchema schema) {
StringBuilder sql = new StringBuilder("CREATE TABLE ")
.append(quoteIdentifier(tableName, config.quoteIdentifiers))
.append(" (\n");
// 列定义
List<String> columnDefs = schema.columns.values().stream()
.map(col -> " " + col.getFullDefinition(config.quoteIdentifiers))
.collect(Collectors.toList());
sql.append(String.join(",\n", columnDefs));
// 主键
if (!schema.primaryKeys.isEmpty()) {
sql.append(",\n PRIMARY KEY (")
.append(schema.primaryKeys.stream()
.map(pk -> quoteIdentifier(pk, config.quoteIdentifiers))
.collect(Collectors.joining(", ")))
.append(")");
}
// 索引
for (IndexInfo index : schema.indexes) {
sql.append(",\n ");
if (index.unique) {
sql.append("UNIQUE ");
}
sql.append("INDEX ")
.append(quoteIdentifier(index.name, config.quoteIdentifiers))
.append(" (")
.append(IntStream.range(0, index.columns.size())
.mapToObj(i -> {
String col = quoteIdentifier(index.columns.get(i), config.quoteIdentifiers);
if (index.orders != null && i < index.orders.size()) {
col += " " + index.orders.get(i);
}
return col;
})
.collect(Collectors.joining(", ")))
.append(")");
}
// 外键
for (ForeignKeyInfo fk : schema.foreignKeys) {
sql.append(",\n CONSTRAINT ")
.append(quoteIdentifier(fk.name, config.quoteIdentifiers))
.append(" FOREIGN KEY (")
.append(quoteIdentifier(fk.column, config.quoteIdentifiers))
.append(") REFERENCES ")
.append(quoteIdentifier(fk.pkTable, config.quoteIdentifiers))
.append(" (")
.append(quoteIdentifier(fk.pkColumn, config.quoteIdentifiers))
.append(")")
.append(" ON UPDATE ").append(getForeignKeyRule(fk.updateRule))
.append(" ON DELETE ").append(getForeignKeyRule(fk.deleteRule));
}
sql.append("\n)");
// 表属性
TableProperties props = schema.properties;
if (props.engine != null) sql.append(" ENGINE=").append(props.engine);
if (props.charset != null) sql.append(" CHARACTER SET ").append(props.charset);
if (props.collation != null) sql.append(" COLLATE ").append(props.collation);
if (props.rowFormat != null) sql.append(" ROW_FORMAT=").append(props.rowFormat);
if (props.comment != null) sql.append(" COMMENT='").append(escapeSql(props.comment)).append("'");
if (props.autoIncrement != null) sql.append(" AUTO_INCREMENT=").append(props.autoIncrement);
return sql.toString();
}
private void syncTableProperties(Connection conn, String dbName, String tableName,
TableProperties sourceProps) throws SQLException {
StringBuilder alterSql = new StringBuilder("ALTER TABLE ")
.append(quoteIdentifier(tableName, config.quoteIdentifiers));
boolean needAlter = false;
if (sourceProps.engine != null) {
alterSql.append(" ENGINE=").append(sourceProps.engine);
needAlter = true;
}
if (sourceProps.charset != null) {
alterSql.append(" CHARACTER SET ").append(sourceProps.charset);
needAlter = true;
}
if (sourceProps.collation != null) {
alterSql.append(" COLLATE ").append(sourceProps.collation);
needAlter = true;
}
if (sourceProps.rowFormat != null) {
alterSql.append(" ROW_FORMAT=").append(sourceProps.rowFormat);
needAlter = true;
}
if (sourceProps.comment != null) {
alterSql.append(" COMMENT='").append(escapeSql(sourceProps.comment)).append("'");
needAlter = true;
}
if (sourceProps.autoIncrement != null) {
alterSql.append(" AUTO_INCREMENT=").append(sourceProps.autoIncrement);
needAlter = true;
}
if (needAlter) {
ErrorContext ctx = new ErrorContext("ALTER TABLE", alterSql.toString(), dbName, tableName);
executeWithErrorHandling(conn, ctx);
logger.info("已更新表属性: {}.{}", dbName, tableName);
}
}
private void syncTableColumns(Connection conn, String dbName, String tableName,
TableSchema sourceSchema, TableSchema targetSchema) throws SQLException {
try (Statement stmt = conn.createStatement()) {
// 添加/修改列
for (ColumnInfo sourceCol : sourceSchema.columns.values()) {
ColumnInfo targetCol = targetSchema.columns.get(sourceCol.name);
if (targetCol == null) {
// 添加新列
String definition = sourceCol.getFullDefinition(config.quoteIdentifiers);
ErrorContext ctx = new ErrorContext("ALTER TABLE",
"ADD COLUMN " + definition, dbName, tableName);
executeWithErrorHandling(stmt, ctx);
logger.info("已添加列: {}.{}.{}", dbName, tableName, sourceCol.name);
} else if (!sourceCol.equals(targetCol)) {
// 修改现有列
modifyColumn(stmt, dbName, tableName, sourceSchema, sourceCol, targetCol);
}
}
// 删除多余列
if (config.dropExtraObjects) {
for (String colName : targetSchema.columns.keySet()) {
if (!sourceSchema.columns.containsKey(colName) &&
!sourceSchema.primaryKeys.contains(colName)) {
ErrorContext ctx = new ErrorContext("ALTER TABLE",
"DROP COLUMN " + quoteIdentifier(colName, config.quoteIdentifiers),
dbName, tableName);
try {
executeWithErrorHandling(stmt, ctx);
logger.info("已删除列: {}.{}.{}", dbName, tableName, colName);
} catch (SQLException e) {
logger.error("删除列 {}.{}.{} 失败: {}", dbName, tableName, colName, e.getMessage());
}
}
}
}
}
}
private void syncPrimaryKeys(Connection conn, String dbName, String tableName,
Set<String> sourcePKs, Set<String> targetPKs) throws SQLException {
if (sourcePKs.equals(targetPKs)) return;
// 删除现有主键
if (!targetPKs.isEmpty()) {
String dropSql = "ALTER TABLE " + quoteIdentifier(tableName, config.quoteIdentifiers)
+ " DROP PRIMARY KEY";
ErrorContext ctx = new ErrorContext("ALTER TABLE", dropSql, dbName, tableName);
executeWithErrorHandling(conn, ctx);
logger.info("已删除主键: {}.{}", dbName, tableName);
}
// 添加新主键
if (!sourcePKs.isEmpty()) {
String pkColumns = sourcePKs.stream()
.map(pk -> quoteIdentifier(pk, config.quoteIdentifiers))
.collect(Collectors.joining(", "));
String addSql = "ADD PRIMARY KEY (" + pkColumns + ")";
ErrorContext ctx = new ErrorContext("ALTER TABLE", addSql, dbName, tableName);
executeWithErrorHandling(conn, ctx);
logger.info("已添加主键: {}.{}", dbName, tableName);
}
}
private void modifyColumn(Statement stmt, String dbName, String tableName,
TableSchema schema, ColumnInfo sourceCol, ColumnInfo targetCol) throws SQLException {
// 处理主键约束
if (schema.primaryKeys.contains(sourceCol.name)) {
if (targetCol.nullable) {
String notNullDef = sourceCol.getFullDefinition(config.quoteIdentifiers)
.replace(" NULL", " NOT NULL");
ErrorContext ctx = new ErrorContext("ALTER TABLE",
"MODIFY COLUMN " + notNullDef, dbName, tableName);
executeWithErrorHandling(stmt, ctx);
}
}
// 检查数据长度
if (!config.skipDataValidation && shouldValidateLength(sourceCol, targetCol)) {
if (!validateColumnDataLength(stmt.getConnection(), dbName, tableName,
sourceCol.name, sourceCol.size)) {
logger.warn("跳过修改列 {}.{}.{}，数据超过新长度 {}",
dbName, tableName, sourceCol.name, sourceCol.size);
return;
}
}
String modifySql = buildColumnModifySql(sourceCol, schema.columns);
if (config.safeAlterMode && needSafeAlter(sourceCol, targetCol)) {
safeModifyColumn(stmt.getConnection(), dbName, tableName, sourceCol, targetCol, modifySql);
} else {
ErrorContext ctx = new ErrorContext("ALTER TABLE",
"MODIFY COLUMN " + modifySql, dbName, tableName);
executeWithErrorHandling(stmt, ctx);
logger.info("已修改列: {}.{}.{}", dbName, tableName, sourceCol.name);
}
}
private void syncIndexes(Connection conn, String dbName, String tableName,
Set<IndexInfo> sourceIndexes, Set<IndexInfo> targetIndexes) throws SQLException {
// 删除多余索引
if (config.dropExtraObjects) {
for (IndexInfo targetIdx : targetIndexes) {
if (sourceIndexes.stream().noneMatch(srcIdx -> srcIdx.name.equals(targetIdx.name))) {
dropIndexSafely(conn, dbName, tableName, targetIdx);
}
}
}
// 添加/修改索引
for (IndexInfo srcIdx : sourceIndexes) {
Optional<IndexInfo> targetIdx = targetIndexes.stream()
.filter(t -> t.name.equals(srcIdx.name))
.findFirst();
if (!targetIdx.isPresent()) {
createIndex(conn, dbName, tableName, srcIdx);
} else if (!srcIdx.equals(targetIdx.get())) {
dropIndexSafely(conn, dbName, tableName, targetIdx.get());
createIndex(conn, dbName, tableName, srcIdx);
}
}
}
private void syncForeignKeys(Connection conn, String dbName, String tableName,
Set<ForeignKeyInfo> sourceForeignKeys) throws SQLException {
// 先删除所有外键
if (config.dropExtraObjects) {
dropAllForeignKeys(conn, dbName, tableName);
}
// 添加外键
for (ForeignKeyInfo fk : sourceForeignKeys) {
addForeignKey(conn, dbName, tableName, fk);
}
}
// ========== 数据库操作工具方法 ==========
private Connection createConnection(String url, String user, String pass) throws SQLException {
return DriverManager.getConnection(url, user, pass);
}
private void disableForeignKeyChecks(Connection conn) throws SQLException {
executeSimpleStatement(conn, "SET FOREIGN_KEY_CHECKS=0");
}
private void enableForeignKeyChecks(Connection conn) throws SQLException {
executeSimpleStatement(conn, "SET FOREIGN_KEY_CHECKS=1");
}
private List<String> getNonSystemDatabases(Connection conn) throws SQLException {
List<String> dbs = new ArrayList<>();
try (ResultSet rs = conn.getMetaData().getCatalogs()) {
while (rs.next()) {
String dbName = rs.getString(1);
if (!SYSTEM_DBS.contains(dbName.toLowerCase())) {
dbs.add(dbName);
}
}
}
return dbs;
}
private Set<String> getTables(Connection conn, String dbName) throws SQLException {
Set<String> tables = new HashSet<>();
try (ResultSet rs = conn.getMetaData().getTables(dbName, null, "%", new String[]{"TABLE"})) {
while (rs.next()) {
tables.add(rs.getString("TABLE_NAME"));
}
}
return tables;
}
private TableSchema getTableSchema(Connection conn, String dbName, String tableName) throws SQLException {
TableSchema schema = new TableSchema();
DatabaseMetaData meta = conn.getMetaData();
// 获取列信息
try (ResultSet rs = meta.getColumns(dbName, null, tableName, null)) {
while (rs.next()) {
ColumnInfo col = new ColumnInfo();
col.name = rs.getString("COLUMN_NAME");
col.type = rs.getString("TYPE_NAME");
col.size = rs.getInt("COLUMN_SIZE");
col.nullable = "YES".equals(rs.getString("IS_NULLABLE"));
col.defaultValue = formatDefaultValue(rs.getString("COLUMN_DEF"), col.type);
col.comment = rs.getString("REMARKS");
col.autoIncrement = "YES".equals(rs.getString("IS_AUTOINCREMENT"));
schema.columns.put(col.name, col);
}
}
// 获取主键信息
try (ResultSet rs = meta.getPrimaryKeys(dbName, null, tableName)) {
while (rs.next()) {
schema.primaryKeys.add(rs.getString("COLUMN_NAME"));
}
}
// 获取索引信息
try (ResultSet rs = meta.getIndexInfo(dbName, null, tableName, false, true)) {
Map<String, IndexInfo> indexMap = new HashMap<>();
while (rs.next()) {
String indexName = rs.getString("INDEX_NAME");
String columnName = rs.getString("COLUMN_NAME");
if (indexName != null && columnName != null && !indexName.equalsIgnoreCase("PRIMARY")) {
IndexInfo index = indexMap.computeIfAbsent(indexName, k -> {
IndexInfo idx = new IndexInfo();
idx.name = k;
try {
idx.unique = !rs.getBoolean("NON_UNIQUE");
} catch (SQLException e) {
throw new RuntimeException(e);
}
idx.columns = new ArrayList<>();
idx.orders = new ArrayList<>();
try {
idx.indexType = rs.getString("TYPE");
} catch (SQLException e) {
throw new RuntimeException(e);
}
return idx;
});
index.columns.add(columnName);
index.orders.add("A".equals(rs.getString("ASC_OR_DESC")) ? "ASC" : "DESC");
}
}
schema.indexes.addAll(indexMap.values());
}
// 获取外键信息
try (ResultSet rs = meta.getImportedKeys(dbName, null, tableName)) {
while (rs.next()) {
ForeignKeyInfo fk = new ForeignKeyInfo();
fk.name = rs.getString("FK_NAME");
fk.column = rs.getString("FKCOLUMN_NAME");
fk.pkTable = rs.getString("PKTABLE_NAME");
fk.pkColumn = rs.getString("PKCOLUMN_NAME");
fk.updateRule = rs.getShort("UPDATE_RULE");
fk.deleteRule = rs.getShort("DELETE_RULE");
schema.foreignKeys.add(fk);
}
}
// 获取表属性
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(
String.format("SHOW CREATE TABLE %s.%s",
quoteIdentifier(dbName, config.quoteIdentifiers),
quoteIdentifier(tableName, config.quoteIdentifiers)))) {
if (rs.next()) {
String createSql = rs.getString(2);
schema.properties = parseTableProperties(createSql);
}
}
return schema;
}
private TableProperties parseTableProperties(String createSql) {
TableProperties props = new TableProperties();
// 解析ENGINE
props.engine = extractProperty(createSql, "ENGINE");
// 解析字符集
props.charset = extractProperty(createSql, "CHARACTER SET");
if (props.charset == null) {
props.charset = extractProperty(createSql, "CHARSET");
}
// 解析排序规则
props.collation = extractProperty(createSql, "COLLATE");
// 解析行格式
props.rowFormat = extractProperty(createSql, "ROW_FORMAT");
// 解析注释
props.comment = extractProperty(createSql, "COMMENT");
// 解析自增值
props.autoIncrement = extractProperty(createSql, "AUTO_INCREMENT");
return props;
}
private String extractProperty(String sql, String property) {
Pattern pattern = Pattern.compile(property + "[= ]+([^\\s,]+)", Pattern.CASE_INSENSITIVE);
Matcher matcher = pattern.matcher(sql);
return matcher.find() ? matcher.group(1) : null;
}
// ========== 其他工具方法 ==========
private static String quoteIdentifier(String identifier) {
if (identifier == null || identifier.isEmpty()) {
return identifier;
}
return "`" + identifier.replace("`", "``") + "`";
}
private static String quoteIdentifier(String identifier, boolean quote) {
if (!quote || identifier == null || identifier.isEmpty()) {
return identifier;
}
return "`" + identifier.replace("`", "``") + "`";
}
private static String escapeSql(String input) {
if (input == null) return null;
return input.replace("'", "''");
}
private static String formatDefaultValue(String value, String columnType) {
if (value == null || value.equalsIgnoreCase("NULL")) {
return "NULL";
}
if (value.contains("CURRENT_TIMESTAMP") || value.equalsIgnoreCase("NOW()")) {
return value;
}
if (value.startsWith("b'") && value.endsWith("'")) {
return value;
}
String normalizedType = columnType.toUpperCase().split("\\(")[0];
if (normalizedType.matches("(TINYINT|SMALLINT|INT|BIGINT|DECIMAL|FLOAT|DOUBLE)")) {
return value.replaceAll("[^0-9.-]", "");
}
if (normalizedType.matches("(BIT|BOOL|BOOLEAN)")) {
return value.equalsIgnoreCase("true") || value.equals("1") ? "1" : "0";
}
value = value.replace("'", "").replace("\"", "");
return value.trim().isEmpty() ? "NULL" : "'" + value + "'";
}
private static String getForeignKeyRule(short rule) {
switch (rule) {
case DatabaseMetaData.importedKeyCascade: return "CASCADE";
case DatabaseMetaData.importedKeySetNull: return "SET NULL";
case DatabaseMetaData.importedKeySetDefault: return "SET DEFAULT";
case DatabaseMetaData.importedKeyRestrict: return "RESTRICT";
case DatabaseMetaData.importedKeyNoAction: return "NO ACTION";
default: return "RESTRICT";
}
}
private String buildColumnModifySql(ColumnInfo col, Map<String, ColumnInfo> allColumns) {
StringBuilder sql = new StringBuilder(col.getFullDefinition(config.quoteIdentifiers));
// 添加位置信息
List<String> orderedColumns = new ArrayList<>(allColumns.keySet());
int index = orderedColumns.indexOf(col.name);
if (index > 0) {
String prevCol = orderedColumns.get(index - 1);
sql.append(" AFTER ").append(quoteIdentifier(prevCol, config.quoteIdentifiers));
}
return sql.toString();
}
private boolean needSafeAlter(ColumnInfo sourceCol, ColumnInfo targetCol) {
if (!sourceCol.type.equalsIgnoreCase(targetCol.type)) {
return true;
}
if (!sourceCol.nullable && targetCol.nullable) {
return true;
}
if (sourceCol.type.toLowerCase().startsWith("decimal") &&
targetCol.type.toLowerCase().startsWith("decimal")) {
String sourcePrecision = sourceCol.type.replaceAll(".*\\((\\d+,\\d+)\\).*", "$1");
String targetPrecision = targetCol.type.replaceAll(".*\\((\\d+,\\d+)\\).*", "$1");
return !sourcePrecision.equals(targetPrecision);
}
return false;
}
private boolean shouldValidateLength(ColumnInfo sourceCol, ColumnInfo targetCol) {
return sourceCol.size > 0 && sourceCol.size < targetCol.size &&
sourceCol.type.matches("(?i)(CHAR|VARCHAR|TEXT)");
}
private boolean validateColumnDataLength(Connection conn, String dbName, String tableName,
String columnName, int newLength) throws SQLException {
String query = String.format("SELECT COUNT(*) FROM %s.%s WHERE CHAR_LENGTH(%s) > %d",
quoteIdentifier(dbName, config.quoteIdentifiers),
quoteIdentifier(tableName, config.quoteIdentifiers),
quoteIdentifier(columnName, config.quoteIdentifiers),
newLength);
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query)) {
return !rs.next() || rs.getInt(1) == 0;
}
}
private void safeModifyColumn(Connection conn, String dbName, String tableName,
ColumnInfo sourceCol, ColumnInfo targetCol,
String modifySql) throws SQLException {
String tempColName = sourceCol.name + "_temp_" + System.currentTimeMillis();
try (Statement stmt = conn.createStatement()) {
// 1. 添加临时列
String addTempColSql = String.format("ALTER TABLE %s.%s ADD COLUMN %s %s",
quoteIdentifier(dbName, config.quoteIdentifiers),
quoteIdentifier(tableName, config.quoteIdentifiers),
quoteIdentifier(tempColName, config.quoteIdentifiers),
sourceCol.getTypeDefinition());
executeWithErrorHandling(stmt, new ErrorContext("ALTER TABLE", addTempColSql, dbName, tableName));
// 2. 复制数据
String copyDataSql = String.format("UPDATE %s.%s SET %s = %s",
quoteIdentifier(dbName, config.quoteIdentifiers),
quoteIdentifier(tableName, config.quoteIdentifiers),
quoteIdentifier(tempColName, config.quoteIdentifiers),
quoteIdentifier(sourceCol.name, config.quoteIdentifiers));
executeWithErrorHandling(stmt, new ErrorContext("UPDATE", copyDataSql, dbName, tableName));
// 3. 删除原列
String dropColSql = String.format("ALTER TABLE %s.%s DROP COLUMN %s",
quoteIdentifier(dbName, config.quoteIdentifiers),
quoteIdentifier(tableName, config.quoteIdentifiers),
quoteIdentifier(sourceCol.name, config.quoteIdentifiers));
executeWithErrorHandling(stmt, new ErrorContext("ALTER TABLE", dropColSql, dbName, tableName));
// 4. 添加新列
String addNewColSql = String.format("ALTER TABLE %s.%s ADD COLUMN %s %s",
quoteIdentifier(dbName, config.quoteIdentifiers),
quoteIdentifier(tableName, config.quoteIdentifiers),
quoteIdentifier(sourceCol.name, config.quoteIdentifiers),
sourceCol.getFullDefinition(config.quoteIdentifiers));
executeWithErrorHandling(stmt, new ErrorContext("ALTER TABLE", addNewColSql, dbName, tableName));
// 5. 复制数据回新列
String copyBackSql = String.format("UPDATE %s.%s SET %s = %s",
quoteIdentifier(dbName, config.quoteIdentifiers),
quoteIdentifier(tableName, config.quoteIdentifiers),
quoteIdentifier(sourceCol.name, config.quoteIdentifiers),
quoteIdentifier(tempColName, config.quoteIdentifiers));
executeWithErrorHandling(stmt, new ErrorContext("UPDATE", copyBackSql, dbName, tableName));
// 6. 删除临时列
String dropTempColSql = String.format("ALTER TABLE %s.%s DROP COLUMN %s",
quoteIdentifier(dbName, config.quoteIdentifiers),
quoteIdentifier(tableName, config.quoteIdentifiers),
quoteIdentifier(tempColName, config.quoteIdentifiers));
executeWithErrorHandling(stmt, new ErrorContext("ALTER TABLE", dropTempColSql, dbName, tableName));
logger.info("安全模式修改列完成: {}.{}.{}", dbName, tableName, sourceCol.name);
} catch (SQLException e) {
logger.error("安全模式修改列失败: {}.{}.{} - {}", dbName, tableName, sourceCol.name, e.getMessage());
throw e;
}
}
private void dropIndexSafely(Connection conn, String dbName, String tableName, IndexInfo index) {
String sql = String.format("DROP INDEX %s ON %s.%s",
quoteIdentifier(index.name, config.quoteIdentifiers),
quoteIdentifier(dbName, config.quoteIdentifiers),
quoteIdentifier(tableName, config.quoteIdentifiers));
try (Statement stmt = conn.createStatement()) {
stmt.execute(sql);
logger.debug("已删除索引: {}.{}.{}", dbName, tableName, index.name);
} catch (SQLException e) {
logger.error("删除索引 {}.{}.{} 失败: {}", dbName, tableName, index.name, e.getMessage());
}
}
private void createIndex(Connection conn, String dbName, String tableName, IndexInfo index) {
StringBuilder sql = new StringBuilder("CREATE ");
if (index.unique) sql.append("UNIQUE ");
sql.append("INDEX ")
.append(quoteIdentifier(index.name, config.quoteIdentifiers))
.append(" ON ")
.append(quoteIdentifier(dbName, config.quoteIdentifiers))
.append(".")
.append(quoteIdentifier(tableName, config.quoteIdentifiers))
.append(" (")
.append(IntStream.range(0, index.columns.size())
.mapToObj(i -> {
String col = quoteIdentifier(index.columns.get(i), config.quoteIdentifiers);
if (index.orders != null && i < index.orders.size()) {
col += " " + index.orders.get(i);
}
return col;
})
.collect(Collectors.joining(", ")))
.append(")");
try (Statement stmt = conn.createStatement()) {
stmt.execute(sql.toString());
logger.debug("已创建索引: {}.{}.{}", dbName, tableName, index.name);
} catch (SQLException e) {
logger.error("创建索引 {}.{}.{} 失败: {}", dbName, tableName, index.name, e.getMessage());
handleIndexCreationError(e, conn, dbName, tableName, index);
}
}
private void handleIndexCreationError(SQLException e, Connection conn,
String dbName, String tableName, IndexInfo index) {
if (e.getMessage().contains("Duplicate key name")) {
String newName = index.name + "_" + System.currentTimeMillis();
logger.warn("尝试使用新索引名: {}", newName);
index.name = newName;
createIndex(conn, dbName, tableName, index);
}
}
private void dropAllForeignKeys(Connection conn, String dbName, String tableName) throws SQLException {
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(
String.format("SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS " +
"WHERE TABLE_SCHEMA = '%s' AND TABLE_NAME = '%s' AND CONSTRAINT_TYPE = 'FOREIGN KEY'",
dbName, tableName))) {
while (rs.next()) {
String fkName = rs.getString(1);
String sql = String.format("ALTER TABLE %s.%s DROP FOREIGN KEY %s",
quoteIdentifier(dbName, config.quoteIdentifiers),
quoteIdentifier(tableName, config.quoteIdentifiers),
quoteIdentifier(fkName, config.quoteIdentifiers));
try {
stmt.execute(sql);
logger.debug("已删除外键约束: {}.{}.{}", dbName, tableName, fkName);
} catch (SQLException e) {
logger.error("删除外键约束 {}.{}.{} 失败: {}", dbName, tableName, fkName, e.getMessage());
}
}
}
}
private void addForeignKey(Connection conn, String dbName, String tableName,
ForeignKeyInfo fk) throws SQLException {
String sql = String.format("ALTER TABLE %s.%s ADD CONSTRAINT %s FOREIGN KEY (%s) " +
"REFERENCES %s (%s) ON UPDATE %s ON DELETE %s",
quoteIdentifier(dbName, config.quoteIdentifiers),
quoteIdentifier(tableName, config.quoteIdentifiers),
quoteIdentifier(fk.name, config.quoteIdentifiers),
quoteIdentifier(fk.column, config.quoteIdentifiers),
quoteIdentifier(fk.pkTable, config.quoteIdentifiers),
quoteIdentifier(fk.pkColumn, config.quoteIdentifiers),
getForeignKeyRule(fk.updateRule),
getForeignKeyRule(fk.deleteRule));
try (Statement stmt = conn.createStatement()) {
stmt.execute(sql);
logger.debug("已添加外键约束: {}.{}.{}", dbName, tableName, fk.name);
} catch (SQLException e) {
logger.error("添加外键约束 {}.{}.{} 失败: {}", dbName, tableName, fk.name, e.getMessage());
}
}
private void executeWithErrorHandling(Connection conn, ErrorContext ctx) throws SQLException {
try (Statement stmt = conn.createStatement()) {
executeWithErrorHandling(stmt, ctx);
}
}
private void executeWithErrorHandling(Statement stmt, ErrorContext ctx) throws SQLException {
while (ctx.retryCount < MAX_RETRY_ATTEMPTS) {
try {
logger.debug("执行SQL: {}", ctx.sql);
stmt.execute(ctx.sql);
return;
} catch (SQLException e) {
boolean handled = false;
for (Map.Entry<String, ErrorHandler> entry : errorHandlers.entrySet()) {
if (e.getMessage().contains(entry.getKey())) {
logger.warn("处理错误: {} - 应用策略: {}", e.getMessage(), entry.getKey());
if (entry.getValue().handle(e, ctx)) {
handled = true;
ctx.incrementRetry();
logger.info("重试操作 ({}/{}): {}", ctx.retryCount, MAX_RETRY_ATTEMPTS, ctx.sql);
break;
}
}
}
if (!handled) {
throw e;
}
}
}
throw new SQLException("操作重试次数超过上限: " + ctx.operation);
}
private void executeSimpleStatement(Connection conn, String sql) throws SQLException {
try (Statement stmt = conn.createStatement()) {
stmt.execute(sql);
}
}
private void awaitCompletion(List<Future<?>> futures, String taskName) {
for (Future<?> future : futures) {
try {
future.get();
} catch (InterruptedException e) {
Thread.currentThread().interrupt();
logger.error("{} 任务被中断", taskName);
} catch (ExecutionException e) {
logger.error("{} 任务执行异常: {}", taskName, e.getCause().getMessage());
}
}
}
private void sleep(long millis) {
try {
Thread.sleep(millis);
} catch (InterruptedException e) {
Thread.currentThread().interrupt();
}
}
private String extractIndexName(String definition) {
Matcher matcher = Pattern.compile("(INDEX|KEY)\\s+(`?)([^`\\s]+)\\2").matcher(definition);
return matcher.find() ? matcher.group(3) : null;
}
private void createDatabaseIfNotExists(Connection conn, String dbName) throws SQLException {
String sql = "CREATE DATABASE IF NOT EXISTS " + quoteIdentifier(dbName, config.quoteIdentifiers);
try (Statement stmt = conn.createStatement()) {
stmt.execute(sql);
}
}
private void dropTable(Connection conn, String dbName, String tableName) throws SQLException {
String sql = String.format("DROP TABLE %s.%s",
quoteIdentifier(dbName, config.quoteIdentifiers),
quoteIdentifier(tableName, config.quoteIdentifiers));
try (Statement stmt = conn.createStatement()) {
stmt.execute(sql);
logger.info("已删除表: {}.{}", dbName, tableName);
}
}
public static void main(String[] args) {
DatabaseSchemaSync.Config config = new DatabaseSchemaSync.Config();
config.threadPoolSize = 8;
config.excludedTables = Arrays.asList("temp_table", "backup_data");
config.verboseLogging = true;
DatabaseSchemaSync sync = new DatabaseSchemaSync(config);
try {
sync.sync(
"jdbc:mysql://172.17.3.22:3306",
"jdbc:mysql://172.17.3.24:3306",
"root", "EetVoJuoHiRyxnGiTXPf100@MP2cGLFVm",
"root", "EetVoJuoHiRyxnGiTXPf100@MP2cGLFVm"
);
} catch (SQLException e) {
logger.error("数据库同步失败", e);
}
}
}
优化此代码
本文解析了在使用char类型进行比较时可能遇到的问题，特别是在数据库字段为char(n)的情况下，直接比较可能会因补全长度而得到意外结果。文章提供了使用.trim()方法解决这一问题的方案。
扫一扫
1942
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
