MySQL实现列转行并对比新旧表字段差异的方法

因为MySQL不支持原生PIVOT/UNPIVOT，我们可以通过UNION ALL逐字段拆分的方式实现需求，只提取新旧值不同的字段，输出字段名、旧值、新值的格式。

核心思路 #

1. 用JOIN关联两张表的同一条记录（假设存在主键，比如id）
2. 对每个需要比较的字段，单独写一个查询，将字段名作为固定值输出，同时取出新旧值
3. 用UNION ALL合并所有字段的查询结果
4. 通过WHERE条件过滤出新旧值不一致的记录（注意处理NULL的特殊情况）

示例代码 #

假设两张表的结构为：id(主键), name(字符串), age(整数), email(字符串)，SQL如下：

-- 处理name字段的差异
SELECT 
    'name' AS field_name,
    th.name AS old_value,
    t.name AS new_value
FROM test_hist th
JOIN test t ON th.id = t.id
WHERE (th.name <> t.name 
       OR (th.name IS NULL AND t.name IS NOT NULL) 
       OR (th.name IS NOT NULL AND t.name IS NULL))

UNION ALL

-- 处理age字段的差异（转字符串统一类型）
SELECT 
    'age' AS field_name,
    CAST(th.age AS CHAR) AS old_value,
    CAST(t.age AS CHAR) AS new_value
FROM test_hist th
JOIN test t ON th.id = t.id
WHERE (th.age <> t.age 
       OR (th.age IS NULL AND t.age IS NOT NULL) 
       OR (th.age IS NOT NULL AND t.age IS NULL))

UNION ALL

-- 处理email字段的差异
SELECT 
    'email' AS field_name,
    th.email AS old_value,
    t.email AS new_value
FROM test_hist th
JOIN test t ON th.id = t.id
WHERE (th.email <> t.email 
       OR (th.email IS NULL AND t.email IS NOT NULL) 
       OR (th.email IS NOT NULL AND t.email IS NULL));

关键注意事项 #

• 类型统一：如果字段类型不同（比如整数和字符串），需要用CAST()转成相同类型（比如CHAR），否则UNION ALL会报错
• NULL处理：MySQL中NULL <> 任何值的结果都是NULL，不会被WHERE匹配，所以必须单独判断NULL的情况
• 性能优化：如果数据量较大，确保id字段有索引，避免全表扫描；也可以先筛选出有变化的记录，再做拆分，比如先查WHERE th.id = t.id AND (th.name <> t.name OR th.age <> t.age OR th.email <> t.email)，再关联拆分

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