Oracle SQL：百万级行两表差异列查询（无需枚举全部列名）

刚好之前处理过类似的大规模表列差异排查需求，给你分场景整理几个实用方案，完全不用手动枚举上百列：

场景1：两张表列名完全相同（符合你的补充假设，实现最简单） #

核心思路是利用数据库的系统元数据表自动获取所有需要检查的列名，然后动态生成对比SQL，找出存在差异的列。下面是主流数据库的实现：

PostgreSQL 实现 #

DO $$
DECLARE
    col_list text;
    unpivot_cols text;
    result_query text;
BEGIN
    -- 从系统表获取所有数值型列（排除主键ID）
    SELECT 
        string_agg(format('SUM(CASE WHEN a.%I != b.%I THEN 1 ELSE 0 END) AS diff_cnt_%I', column_name, column_name, column_name), ', '),
        string_agg(format('diff_cnt_%I', column_name), ', ')
    INTO col_list, unpivot_cols
    FROM information_schema.columns
    WHERE table_name = 'table_a' -- 替换成你的表A名称
      AND column_name != 'ID'
      AND data_type IN ('integer', 'numeric', 'bigint');

    -- 构建动态查询：先聚合每个列的差异行数，再转成行输出有差异的列
    result_query := format('
        SELECT replace(column_name, ''diff_cnt_'', '''') AS differing_column
        FROM (
            SELECT %s
            FROM table_a a
            JOIN table_b b ON a.ID = b.ID
        ) aggregated
        UNPIVOT (
            diff_count FOR column_name IN (%s)
        ) unpivoted
        WHERE diff_count > 0;
    ', col_list, unpivot_cols);

    -- 执行并返回结果
    EXECUTE result_query;
END $$;

这个脚本会自动统计每个列的差异行数，只要差异行数大于0，就会输出对应的列名，完全不用手动列所有字段。

MySQL 实现 #

MySQL没有原生的UNPIVOT，我们可以用动态生成的UNION查询来实现：

-- 生成所有列的差异检查语句
SET @check_queries = (
    SELECT GROUP_CONCAT(
        CONCAT(
            'SELECT ''', column_name, ''' AS differing_column FROM table_a a JOIN table_b b ON a.ID = b.ID WHERE a.', column_name, ' != b.', column_name, ' LIMIT 1'
        ) SEPARATOR ' UNION DISTINCT '
    )
    FROM information_schema.columns
    WHERE table_name = 'table_a'
      AND column_name != 'ID'
      AND data_type IN ('int', 'decimal', 'bigint')
);

-- 执行动态语句
PREPARE stmt FROM @check_queries;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

这里每个列单独检查是否存在差异行，只要找到一行差异就返回列名，用UNION DISTINCT去重，避免重复输出。

场景2：两张表列名不完全相同（满足你“无此假设”的优先需求） #

这种情况需要先匹配两张表中列名相同且数据类型为数值型的列（如果列名不同但语义相同，可能需要额外的映射规则，但你没提这个，先按列名匹配来），再执行差异检查：

PostgreSQL 适配版 #

DO $$
DECLARE
    col_list text;
    unpivot_cols text;
    result_query text;
BEGIN
    -- 先匹配两张表中相同的数值型列（排除主键）
    SELECT 
        string_agg(format('SUM(CASE WHEN a.%I != b.%I THEN 1 ELSE 0 END) AS diff_cnt_%I', a.column_name, b.column_name, a.column_name), ', '),
        string_agg(format('diff_cnt_%I', a.column_name), ', ')
    INTO col_list, unpivot_cols
    FROM information_schema.columns a
    JOIN information_schema.columns b
        ON a.column_name = b.column_name
        AND a.data_type = b.data_type
    WHERE a.table_name = 'table_a'
      AND b.table_name = 'table_b'
      AND a.column_name != 'ID'
      AND a.data_type IN ('integer', 'numeric', 'bigint');

    -- 后续逻辑和场景1一致
    result_query := format('
        SELECT replace(column_name, ''diff_cnt_'', '''') AS differing_column
        FROM (
            SELECT %s
            FROM table_a a
            JOIN table_b b ON a.ID = b.ID -- 这里假设主键都叫ID，如果不同要调整JOIN条件
        ) aggregated
        UNPIVOT (
            diff_count FOR column_name IN (%s)
        ) unpivoted
        WHERE diff_count > 0;
    ', col_list, unpivot_cols);

    EXECUTE result_query;
END $$;

如果主键列名也不同，只需要把JOIN条件改成a.主键A列 = b.主键B列即可。

性能优化小贴士 #

因为两张表都是百万级数据，执行前可以做这些优化：

• 确保两张表的主键ID都有索引，加速JOIN操作
• 如果允许，可以先采样对比（比如取10%的数据），快速排查大部分无差异的列，再对疑似有差异的列做全量检查，节省时间
• 尽量在数据库负载低的时段执行，避免影响业务

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