import java.sql.*;
|
import java.util.*;
|
|
/**
|
* Read-only: 同一个归属 id（talent_id / merchant_id）在两个库里各有多少行、差在哪些表。
|
* 用于两库合并后核对「某个账号的数据是不是全在」，以及定位主键撞车影响的表。
|
*/
|
public class TenantDataDiff {
|
public static void main(String[] args) throws Exception {
|
String column = args[0];
|
long id = Long.parseLong(args[1]);
|
String left = args[2];
|
String right = args[3];
|
try (Connection a = DriverManager.getConnection(left, "root", "123456");
|
Connection b = DriverManager.getConnection(right, "root", "123456")) {
|
System.out.println("=== " + column + " = " + id);
|
System.out.println("左 " + one(a, "select concat(@@hostname, '/', database())"));
|
System.out.println("右 " + one(b, "select concat(@@hostname, '/', database())"));
|
System.out.println();
|
System.out.format("%-46s %8s %8s %8s%n", "表", "左", "右", "差");
|
for (String table : referencingTables(a, column)) {
|
if (!exists(b, table)) {
|
System.out.format("%-46s %8s %8s %8s%n", table, count(a, table, column, id), "-", "右库无此表");
|
continue;
|
}
|
long leftCount = count(a, table, column, id);
|
long rightCount = count(b, table, column, id);
|
if (leftCount == 0 && rightCount == 0) continue;
|
System.out.format("%-46s %8d %8d %8d%s%n", table, leftCount, rightCount, leftCount - rightCount,
|
leftCount == rightCount ? "" : " <-- 不一致");
|
}
|
}
|
}
|
|
private static List<String> referencingTables(Connection connection, String column) throws SQLException {
|
List<String> tables = new ArrayList<>();
|
try (PreparedStatement statement = connection.prepareStatement(
|
"select table_name from information_schema.columns where table_schema = database() and column_name = ? order by table_name")) {
|
statement.setString(1, column);
|
try (ResultSet rows = statement.executeQuery()) {
|
while (rows.next()) tables.add(rows.getString(1));
|
}
|
}
|
return tables;
|
}
|
|
private static boolean exists(Connection connection, String table) throws SQLException {
|
try (PreparedStatement statement = connection.prepareStatement(
|
"select count(*) from information_schema.tables where table_schema = database() and table_name = ?")) {
|
statement.setString(1, table);
|
try (ResultSet rows = statement.executeQuery()) {
|
return rows.next() && rows.getLong(1) > 0;
|
}
|
}
|
}
|
|
private static long count(Connection connection, String table, String column, long id) throws SQLException {
|
// 列名与表名都来自 information_schema，不是外部输入。
|
try (Statement statement = connection.createStatement();
|
ResultSet rows = statement.executeQuery("select count(*) from `" + table + "` where `" + column + "` = " + id)) {
|
return rows.next() ? rows.getLong(1) : 0;
|
}
|
}
|
|
private static String one(Connection connection, String sql) throws SQLException {
|
try (Statement statement = connection.createStatement(); ResultSet rows = statement.executeQuery(sql)) {
|
return rows.next() ? rows.getString(1) : "";
|
}
|
}
|
}
|
