import java.sql.Connection;
|
import java.sql.DriverManager;
|
import java.sql.PreparedStatement;
|
import java.sql.ResultSet;
|
import java.util.ArrayList;
|
import java.util.LinkedHashMap;
|
import java.util.List;
|
import java.util.Map;
|
import java.util.TreeMap;
|
|
/**
|
* 只读探针：按主键逐行比对两个库的同名表，回答「同一主键在两边是不是同一份内容」。
|
*
|
* <p>用法：java RowCompare <左库url> <右库url> [表名,表名...]
|
* 默认比对内置的核心业务表清单。不做任何写入。
|
*/
|
public class RowCompare {
|
|
private static final String[] DEFAULT_TABLES = {
|
"merchant", "merchant_store", "merchant_product", "merchant_operator",
|
"merchant_onboarding_application", "merchant_onboarding_progress", "merchant_bank_card",
|
"merchant_verification_attempt", "merchant_agreement_acceptance",
|
"benefit_order", "verify_record", "payment_transaction", "order_refund",
|
"payment_reconciliation_issue", "payment_callback_event",
|
"talent_user", "talent_card", "talent_dynamic_code",
|
"admin_user", "merchant_coupon", "news_article",
|
};
|
|
public static void main(String[] args) throws Exception {
|
try (Connection left = DriverManager.getConnection(args[0], "root", "123456");
|
Connection right = DriverManager.getConnection(args[1], "root", "123456")) {
|
String[] tables = args.length > 2 ? args[2].split(",") : DEFAULT_TABLES;
|
System.out.printf("%-34s %7s %7s %7s %7s %7s %7s%n",
|
"表", "总数L", "总数R", "同内容", "内容不同", "仅左", "仅右");
|
for (String table : tables) {
|
compare(left, right, table.trim());
|
}
|
}
|
}
|
|
private static void compare(Connection left, Connection right, String table) {
|
try {
|
List<String> keys = primaryKey(left, table);
|
if (keys.isEmpty()) {
|
System.out.printf("%-34s %7s%n", table, "（无单列主键，跳过）");
|
return;
|
}
|
String key = keys.get(0);
|
// 只比对两边都有的列：本机库可能还没应用某些迁移，列集合不同不代表行内容不同。
|
List<String> leftColumns = columns(left, table);
|
List<String> rightColumns = columns(right, table);
|
List<String> shared = new ArrayList<>(leftColumns);
|
shared.retainAll(rightColumns);
|
if (!shared.contains(key)) {
|
System.out.printf("%-34s %7s%n", table, "（主键列不在共有列里）");
|
return;
|
}
|
Map<String, List<String>> leftRows = fingerprint(left, table, key, shared);
|
Map<String, List<String>> rightRows = fingerprint(right, table, key, shared);
|
int same = 0;
|
int different = 0;
|
List<String> conflicts = new ArrayList<>();
|
List<String> example = null;
|
for (Map.Entry<String, List<String>> entry : leftRows.entrySet()) {
|
List<String> other = rightRows.get(entry.getKey());
|
if (other == null) continue;
|
if (other.equals(entry.getValue())) same++;
|
else {
|
different++;
|
if (conflicts.size() < 8) conflicts.add(entry.getKey());
|
if (example == null) example = differingColumns(shared, entry.getValue(), other);
|
}
|
}
|
int onlyLeft = 0;
|
for (String id : leftRows.keySet()) if (!rightRows.containsKey(id)) onlyLeft++;
|
int onlyRight = 0;
|
for (String id : rightRows.keySet()) if (!leftRows.containsKey(id)) onlyRight++;
|
System.out.printf("%-34s %7d %7d %7d %7d %7d %7d%n",
|
table, leftRows.size(), rightRows.size(), same, different, onlyLeft, onlyRight);
|
if (!conflicts.isEmpty()) {
|
System.out.println(" 主键相同但内容不同：" + String.join(", ", conflicts)
|
+ (different > conflicts.size() ? " ...（共 " + different + " 条）" : ""));
|
if (example != null) System.out.println(" 差异列（示例）：" + String.join(", ", example));
|
}
|
} catch (Exception exception) {
|
System.out.printf("%-34s %7s%n", table, "（" + exception.getMessage() + "）");
|
}
|
}
|
|
private static List<String> differingColumns(List<String> columns, List<String> left, List<String> right) {
|
List<String> differing = new ArrayList<>();
|
for (int index = 0; index < columns.size(); index++) {
|
if (!left.get(index).equals(right.get(index))) differing.add(columns.get(index));
|
}
|
return differing;
|
}
|
|
private static List<String> columns(Connection connection, String table) throws Exception {
|
List<String> columns = new ArrayList<>();
|
try (PreparedStatement statement = connection.prepareStatement("""
|
select column_name from information_schema.columns
|
where table_schema = database() and table_name = ? order by ordinal_position
|
""")) {
|
statement.setString(1, table);
|
try (ResultSet rows = statement.executeQuery()) {
|
while (rows.next()) columns.add(rows.getString(1));
|
}
|
}
|
return columns;
|
}
|
|
private static List<String> primaryKey(Connection connection, String table) throws Exception {
|
List<String> keys = new ArrayList<>();
|
try (PreparedStatement statement = connection.prepareStatement("""
|
select column_name from information_schema.key_column_usage
|
where table_schema = database() and table_name = ? and constraint_name = 'PRIMARY'
|
order by ordinal_position
|
""")) {
|
statement.setString(1, table);
|
try (ResultSet rows = statement.executeQuery()) {
|
while (rows.next()) keys.add(rows.getString(1));
|
}
|
}
|
return keys;
|
}
|
|
/** 主键 -> 共有列的内容指纹（按列顺序拼串，null 用特殊标记）。 */
|
private static Map<String, List<String>> fingerprint(Connection connection, String table, String key,
|
List<String> columns) throws Exception {
|
Map<String, List<String>> rows = new TreeMap<>();
|
String selection = String.join(", ", columns.stream().map(column -> "`" + column + "`").toList());
|
try (PreparedStatement statement = connection.prepareStatement(
|
"select " + selection + " from `" + table + "`");
|
ResultSet result = statement.executeQuery()) {
|
while (result.next()) {
|
List<String> content = new ArrayList<>(columns.size());
|
for (int index = 1; index <= columns.size(); index++) {
|
Object value = result.getObject(index);
|
content.add(value == null ? "\u0000" : String.valueOf(value));
|
}
|
rows.put(String.valueOf(result.getObject(key)), content);
|
}
|
}
|
return rows;
|
}
|
}
|
