如何查询MySQL中指定rid最新版本与前序版本的差异

首先，假设你的表结构类似这样（我们叫它record_versions）：

• rid: 记录唯一标识
• version_id: 版本号（数值越大表示版本越新，也可以用时间戳version_ts替代）
• 其他N个业务列：col1, col2, ..., coln

核心思路是先筛选出每个rid的最新两个版本，再通过自连接把新旧版本的数据对齐，最后逐列对比差异。

步骤1：获取每个rid的最新两个版本 #

利用MySQL 8.0+支持的窗口函数ROW_NUMBER()，可以轻松给每个rid的版本按新旧排序：

WITH ranked_versions AS (
    SELECT
        rid,
        version_id,
        col1, col2, ..., coln,
        ROW_NUMBER() OVER (PARTITION BY rid ORDER BY version_id DESC) AS rn
    FROM record_versions
)
SELECT * FROM ranked_versions WHERE rn IN (1, 2);

这里PARTITION BY rid表示按记录分组，ORDER BY version_id DESC让最新版本排第一（rn=1），上一版本排第二（rn=2）。

步骤2：自连接对齐新旧版本 #

把上面的CTE结果作为数据源，将同一rid的最新版本（rn=1）和上一版本（rn=2）连接起来：

WITH ranked_versions AS (
    SELECT
        rid,
        version_id,
        col1, col2, ..., coln,
        ROW_NUMBER() OVER (PARTITION BY rid ORDER BY version_id DESC) AS rn
    FROM record_versions
)
SELECT
    new.rid,
    new.version_id AS latest_version,
    old.version_id AS previous_version,
    -- 处理版本变化描述（兼容无历史版本的情况）
    CASE 
        WHEN old.version_id IS NULL THEN '无历史版本'
        ELSE CONCAT('版本 ', old.version_id, ' → ', new.version_id)
    END AS version_change,
    -- 逐列对比差异，示例以col1、col2为例
    CASE 
        WHEN new.col1 <=> old.col1 THEN '无变化'
        ELSE CONCAT('从「', COALESCE(old.col1, '空值'), '」变为「', COALESCE(new.col1, '空值'), '」')
    END AS col1_change,
    CASE 
        WHEN new.col2 <=> old.col2 THEN '无变化'
        ELSE CONCAT('从「', COALESCE(old.col2, '空值'), '」变为「', COALESCE(new.col2, '空值'), '」')
    END AS col2_change,
    -- 其他业务列重复上述CASE逻辑即可
    ...
FROM ranked_versions new
LEFT JOIN ranked_versions old
    ON new.rid = old.rid 
    AND new.rn = 1 
    AND old.rn = 2
-- 可以添加WHERE条件指定特定rid
WHERE new.rid = '你的目标rid';

关键细节说明： #

• 使用LEFT JOIN是为了兼容只有一个版本的rid，此时old表的所有字段会是NULL，通过CASE语句可以友好提示“无历史版本”。
• <=>是MySQL的空值安全比较运算符，能正确处理NULL和非NULL的对比（普通的=或<>无法识别NULL的相等性）。
• COALESCE()函数用来把NULL显示为友好的“空值”文本，避免结果里出现生硬的NULL。

针对多列的批量处理技巧 #

如果你的表有几十列，手动写每个列的CASE语句太麻烦，可以通过查询INFORMATION_SCHEMA.COLUMNS来自动生成对比代码：

SELECT
    CONCAT(
        'CASE 
            WHEN new.', column_name, ' <=> old.', column_name, ' THEN ''无变化''
            ELSE CONCAT(''从「'', COALESCE(old.', column_name, ', ''空值''), ''」变为「'', COALESCE(new.', column_name, ', ''空值''), ''」'')
        END AS ', column_name, '_change,'
    ) AS column_change_sql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = '你的数据库名'
  AND table_name = 'record_versions'
  AND column_name NOT IN ('rid', 'version_id'); -- 排除不需要对比的列

执行这个查询后，把输出的结果复制到主SQL的SELECT部分即可，节省大量重复工作。

兼容MySQL 5.x的方案（无窗口函数） #

如果你的MySQL版本低于8.0，无法使用窗口函数，可以用子查询获取每个rid的最新两个版本：

SELECT
    new.rid,
    new.version_id AS latest_version,
    old.version_id AS previous_version,
    CASE 
        WHEN old.version_id IS NULL THEN '无历史版本'
        ELSE CONCAT('版本 ', old.version_id, ' → ', new.version_id)
    END AS version_change,
    -- 同样的列对比逻辑
    ...
FROM record_versions new
LEFT JOIN record_versions old
    ON new.rid = old.rid
    AND old.version_id = (
        SELECT MAX(version_id)
        FROM record_versions
        WHERE rid = new.rid AND version_id < new.version_id
    )
WHERE new.version_id = (
    SELECT MAX(version_id)
    FROM record_versions
    WHERE rid = new.rid
)
AND new.rid = '你的目标rid';

这个方案通过子查询找到每个rid的最新版本，以及最新版本的上一个版本，逻辑和窗口函数一致，但性能在数据量大时可能稍差，优先推荐窗口函数方案。

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