求通用SQL方案：跨库对比同结构表的行与列数据差异

你当前的按行对比SQL能找出存在差异的行，但无法直接定位具体哪一列不同、两边的取值分别是什么。以下是基于主键关联的逐列对比实现，包含静态写法（固定表）和动态可复用写法（通用场景）：

一、静态写法（适合固定表结构） #

假设主键为id，两个跨库表为db1.table1和db2.table2，包含字段col1, col2, col3。

方式1：一行展示所有列差异 #

SELECT
    COALESCE(t1.id, t2.id) AS primary_key,
    -- 标记每一列是否存在差异
    CASE 
        WHEN t1.col1 <> t2.col1 OR (t1.col1 IS NULL) <> (t2.col1 IS NULL)
        THEN 'col1' 
    END AS diff_col1,
    t1.col1 AS table1_col1,
    t2.col1 AS table2_col1,
    CASE 
        WHEN t1.col2 <> t2.col2 OR (t1.col2 IS NULL) <> (t2.col2 IS NULL)
        THEN 'col2' 
    END AS diff_col2,
    t1.col2 AS table1_col2,
    t2.col2 AS table2_col2,
    CASE 
        WHEN t1.col3 <> t2.col3 OR (t1.col3 IS NULL) <> (t2.col3 IS NULL)
        THEN 'col3' 
    END AS diff_col3,
    t1.col3 AS table1_col3,
    t2.col3 AS table2_col3
FROM
    db1.table1 t1
FULL OUTER JOIN
    db2.table2 t2 ON t1.id = t2.id
WHERE
    -- 过滤出有差异的行：要么某一边无数据，要么至少一列值不同
    (t1.id IS NULL OR t2.id IS NULL)
    OR t1.col1 <> t2.col1 OR (t1.col1 IS NULL) <> (t2.col1 IS NULL)
    OR t1.col2 <> t2.col2 OR (t1.col2 IS NULL) <> (t2.col2 IS NULL)
    OR t1.col3 <> t2.col3 OR (t1.col3 IS NULL) <> (t2.col3 IS NULL)

方式2：一行展示一个差异点（排查更直观） #

-- 1. 仅存在于table1的行
SELECT
    t1.id AS primary_key,
    '仅存在于table1' AS diff_type,
    NULL AS diff_column,
    NULL AS table2_value,
    CONCAT('所有列值: ', JSON_OBJECT('col1', t1.col1, 'col2', t1.col2, 'col3', t1.col3)) AS table1_value
FROM db1.table1 t1
LEFT JOIN db2.table2 t2 ON t1.id = t2.id
WHERE t2.id IS NULL

UNION ALL

-- 2. 仅存在于table2的行
SELECT
    t2.id AS primary_key,
    '仅存在于table2' AS diff_type,
    NULL AS diff_column,
    CONCAT('所有列值: ', JSON_OBJECT('col1', t2.col1, 'col2', t2.col2, 'col3', t2.col3)) AS table2_value,
    NULL AS table1_value
FROM db2.table2 t2
LEFT JOIN db1.table1 t1 ON t2.id = t1.id
WHERE t1.id IS NULL

UNION ALL

-- 3. 主键存在但col1值不同
SELECT
    t1.id AS primary_key,
    '列值差异' AS diff_type,
    'col1' AS diff_column,
    t1.col1 AS table1_value,
    t2.col1 AS table2_value
FROM db1.table1 t1
JOIN db2.table2 t2 ON t1.id = t2.id
WHERE t1.col1 <> t2.col1 OR (t1.col1 IS NULL) <> (t2.col1 IS NULL)

UNION ALL

-- 4. 主键存在但col2值不同
SELECT
    t1.id AS primary_key,
    '列值差异' AS diff_type,
    'col2' AS diff_column,
    t1.col2 AS table1_value,
    t2.col2 AS table2_value
FROM db1.table1 t1
JOIN db2.table2 t2 ON t1.id = t2.id
WHERE t1.col2 <> t2.col2 OR (t1.col2 IS NULL) <> (t2.col2 IS NULL)

UNION ALL

-- 5. 主键存在但col3值不同
SELECT
    t1.id AS primary_key,
    '列值差异' AS diff_type,
    'col3' AS diff_column,
    t1.col3 AS table1_value,
    t2.col3 AS table2_value
FROM db1.table1 t1
JOIN db2.table2 t2 ON t1.id = t2.id
WHERE t1.col3 <> t2.col3 OR (t1.col3 IS NULL) <> (t2.col3 IS NULL)

二、动态可复用写法（通用所有同结构表） #

通过读取数据库元数据自动生成对比SQL，只需修改三个变量即可适配任意表：

-- 配置参数：修改这三个值即可
SET @table1 = 'db1.table1';
SET @table2 = 'db2.table2';
SET @primary_key = 'id';

-- 生成列差异对比的SQL片段
SELECT GROUP_CONCAT(
    CONCAT(
        'SELECT
            t1.', @primary_key, ' AS primary_key,
            ''列值差异'' AS diff_type,
            ''', COLUMN_NAME, ''' AS diff_column,
            t1.', COLUMN_NAME, ' AS table1_value,
            t2.', COLUMN_NAME, ' AS table2_value
        FROM ', @table1, ' t1
        JOIN ', @table2, ' t2 ON t1.', @primary_key, ' = t2.', @primary_key, '
        WHERE t1.', COLUMN_NAME, ' <> t2.', COLUMN_NAME, ' OR (t1.', COLUMN_NAME, ' IS NULL) <> (t2.', COLUMN_NAME, ' IS NULL)'
    ) SEPARATOR ' UNION ALL '
) INTO @column_diff_sql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = SUBSTRING_INDEX(@table1, '.', 1)
  AND TABLE_NAME = SUBSTRING_INDEX(@table1, '.', -1)
  AND COLUMN_NAME <> @primary_key;

-- 拼接完整SQL
SET @full_sql = CONCAT(
    '-- 仅存在于table1的行
    SELECT
        t1.', @primary_key, ' AS primary_key,
        ''仅存在于table1'' AS diff_type,
        NULL AS diff_column,
        NULL AS table2_value,
        JSON_OBJECT(', (SELECT GROUP_CONCAT(CONCAT('''', COLUMN_NAME, ''', t1.', COLUMN_NAME) SEPARATOR ', ') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = SUBSTRING_INDEX(@table1, '.', 1) AND TABLE_NAME = SUBSTRING_INDEX(@table1, '.', -1)), ') AS table1_value
    FROM ', @table1, ' t1
    LEFT JOIN ', @table2, ' t2 ON t1.', @primary_key, ' = t2.', @primary_key, '
    WHERE t2.', @primary_key, ' IS NULL

    UNION ALL

    -- 仅存在于table2的行
    SELECT
        t2.', @primary_key, ' AS primary_key,
        ''仅存在于table2'' AS diff_type,
        NULL AS diff_column,
        JSON_OBJECT(', (SELECT GROUP_CONCAT(CONCAT('''', COLUMN_NAME, ''', t2.', COLUMN_NAME) SEPARATOR ', ') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = SUBSTRING_INDEX(@table2, '.', 1) AND TABLE_NAME = SUBSTRING_INDEX(@table2, '.', -1)), ') AS table2_value,
        NULL AS table1_value
    FROM ', @table2, ' t2
    LEFT JOIN ', @table1, ' t1 ON t2.', @primary_key, ' = t1.', @primary_key, '
    WHERE t1.', @primary_key, ' IS NULL

    UNION ALL

    ', @column_diff_sql
);

-- 执行动态SQL
PREPARE stmt FROM @full_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

关键注意事项 #

1. NULL值处理：标准SQL中NULL <> 任何值结果为UNKNOWN，必须单独判断两边的NULL状态是否不一致
2. 跨库引用：必须用库名.表名的格式指定表
3. 主键关联：确保两个表的主键语义一致，否则无法正确对齐行

内容的提问来源于stack exchange，提问作者sup