如何高效比对A表更新行与重建行的差异？（200+列场景）

针对200+列的表A，要对比更新后行与重建行的字段差异并关联列注释，原UNION拼接的逐列查询方式效率极低，推荐采用整行转键值对+一次性比对的方案，大幅提升查询速度。

核心思路 #

1. 将更新后的A行、重建生成的A行分别转换为「列名-值」的键值对结构（利用数据库JSON/行转列函数）
2. 关联INFORMATION_SCHEMA.COLUMNS获取列注释
3. 一次性筛选出两行值不同的记录，输出列名、注释、对应值

方案1：MySQL 8.0+ 实现 #

步骤1：动态生成整行JSON（避免手动写200+列）

先获取表A的所有列名，动态生成JSON转换语句：

-- 生成更新后行的JSON
SET @db = 'database';
SET @table = 'table_A';
SET @updated_id = '修改后的A行ID';

SET @cols = (
    SELECT GROUP_CONCAT(CONCAT('`', COLUMN_NAME, '`'))
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = @db AND TABLE_NAME = @table
);

SET @get_updated_row = CONCAT(
    'SELECT JSON_OBJECT(', @cols, ') AS row_json INTO @updated_json ',
    'FROM ', @table, ' WHERE id = ''', @updated_id, ''''
);
PREPARE stmt FROM @get_updated_row;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 生成重建行的JSON（替换为你的重建逻辑）
SET @get_rebuilt_row = CONCAT(
    'SELECT JSON_OBJECT(', @cols, ') AS row_json INTO @rebuilt_json ',
    'FROM (',
    -- 此处替换为你的重建算法，需返回与表A结构一致的单行
    'SELECT MAX(b.col1) AS col1, AVG(b.col2) AS col2, ... FROM table_b WHERE b.original_a_id = ''原A行ID''',
    ') AS rebuilt'
);
PREPARE stmt FROM @get_rebuilt_row;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

步骤2：比对差异并关联注释

WITH updated_kv AS (
    SELECT 
        j.column_name,
        JSON_UNQUOTE(JSON_EXTRACT(@updated_json, CONCAT('$.', j.column_name))) AS updated_value
    FROM (
        SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = @db AND TABLE_NAME = @table
    ) j
),
rebuilt_kv AS (
    SELECT 
        j.column_name,
        JSON_UNQUOTE(JSON_EXTRACT(@rebuilt_json, CONCAT('$.', j.column_name))) AS rebuilt_value
    FROM (
        SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = @db AND TABLE_NAME = @table
    ) j
)
SELECT 
    c.COLUMN_NAME,
    c.COLUMN_COMMENT,
    ukv.updated_value,
    rkv.rebuilt_value
FROM INFORMATION_SCHEMA.COLUMNS c
JOIN updated_kv ukv ON c.COLUMN_NAME = ukv.column_name
JOIN rebuilt_kv rkv ON c.COLUMN_NAME = rkv.column_name
WHERE 
    ukv.updated_value <> rkv.rebuilt_value
    OR (ukv.updated_value IS NULL AND rkv.rebuilt_value IS NOT NULL)
    OR (ukv.updated_value IS NOT NULL AND rkv.rebuilt_value IS NULL)
ORDER BY c.ORDINAL_POSITION;

方案2：PostgreSQL 实现 #

PostgreSQL的JSON函数更简洁，直接用row_to_json和jsonb_each_text实现行转键值对：

WITH updated_row AS (
    SELECT row_to_json(t) AS row_json
    FROM (SELECT * FROM table_A WHERE id = '修改后的A行ID') t
),
rebuilt_row AS (
    SELECT row_to_json(t) AS row_json
    FROM (
        -- 替换为你的重建算法，返回与表A结构一致的单行
        SELECT MAX(b.col1) AS col1, AVG(b.col2) AS col2, ... FROM table_b WHERE b.original_a_id = '原A行ID'
    ) t
),
updated_kv AS (
    SELECT key AS column_name, value AS updated_value
    FROM updated_row, jsonb_each_text(row_json::jsonb)
),
rebuilt_kv AS (
    SELECT key AS column_name, value AS rebuilt_value
    FROM rebuilt_row, jsonb_each_text(row_json::jsonb)
)
SELECT 
    c.column_name,
    c.column_comment,
    ukv.updated_value,
    rkv.rebuilt_value
FROM information_schema.columns c
JOIN updated_kv ukv ON c.column_name = ukv.column_name
JOIN rebuilt_kv rkv ON c.column_name = rkv.column_name
WHERE ukv.updated_value IS DISTINCT FROM rkv.rebuilt_value
ORDER BY c.ordinal_position;

优势说明 #

• 仅需2次核心查询（获取更新行、重建行），避免了原方案200+次UNION的重复查询开销
• 动态生成SQL适配200+列，无需手动编写大量重复代码
• 直接关联系统表获取列注释，结果包含所有必要信息

内容的提问来源于stack exchange，提问作者Tom's