如何实现SQL动态对比多表指定日期的数据差异？

我有70个结构相似的SQL表，所有表都以CURRENT_DATE和ID列开头，其余列各不相同。我想编写一个函数或存储过程，通过输入table_name、columns、date1、date2（待对比的两个日期），输出包含STATUS（NEW/CHANGED）、ID及指定列的对比结果，用来快速校验数据上传是否正确，或者是否有新增、变更数据。目前正在学习SQL，不想为70个表手动写脚本，希望找到简便实现方式，不要用游标对比动态数据后插入结果。

实现思路 #

核心用动态SQL拼接查询语句，利用FULL JOIN直接对比两个日期的数据集，完全不需要游标遍历。通过参数传入表名、待对比列、目标日期，自动生成对比逻辑，一次性输出结果。

存储过程示例（MySQL） #

DELIMITER //

CREATE PROCEDURE CompareTableData(
    IN p_table_name VARCHAR(100),
    IN p_columns VARCHAR(500),
    IN p_date1 DATE,
    IN p_date2 DATE
)
BEGIN
    -- 拼接动态对比SQL
    SET @sql = CONCAT(
        'SELECT 
            CASE 
                WHEN t1.ID IS NULL THEN ''NEW''
                WHEN CONCAT_WS(''|'', ', p_columns, ') != CONCAT_WS(''|'', ', REPLACE(p_columns, ', ', ', t2.'), ') THEN ''CHANGED''
            END AS STATUS,
            COALESCE(t1.ID, t2.ID) AS ID,
            -- 展示两个日期的对应列值，方便直观对比
            ', p_columns, ',
            ', REPLACE(p_columns, ', ', ', t2.'), ' AS ', REPLACE(p_columns, ', ', ', t2_'), '
        FROM (SELECT ID, ', p_columns, ' FROM ', p_table_name, ' WHERE CURRENT_DATE = ''', p_date1, ''') t1
        FULL JOIN (SELECT ID, ', p_columns, ' FROM ', p_table_name, ' WHERE CURRENT_DATE = ''', p_date2, ''') t2
        ON t1.ID = t2.ID
        -- 只筛选新增或变更的数据，不需要无变化数据可保留此条件
        WHERE t1.ID IS NULL OR CONCAT_WS(''|'', ', p_columns, ') != CONCAT_WS(''|'', ', REPLACE(p_columns, ', ', ', t2.'), ')
        ORDER BY STATUS, ID;'
    );

    -- 执行动态SQL
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

使用说明 #

1. 调用方式：直接传入表名、待对比列（用逗号分隔）、两个对比日期即可：

   CALL CompareTableData('user_info', 'name, age, email', '2024-05-01', '2024-05-02');
   

2. STATUS逻辑：
   • NEW：date2存在但date1不存在的ID（新增数据）
   • CHANGED：ID在两个日期都存在，但指定列的组合值有差异
   • 如需展示DELETED（date1有但date2无）或UNCHANGED（无变化）状态，可直接修改CASE语句逻辑
3. 列对比技巧：用CONCAT_WS把指定列拼接成字符串对比，避免逐列写判断逻辑，大幅简化动态拼接的复杂度，同时自动忽略NULL值（比CONCAT更可靠）。

注意事项 #

• SQL注入防护：如果参数来自外部输入，必须对p_table_name和p_columns做白名单校验，只允许合法的表名和列名传入。
• 跨数据库适配：如果用SQL Server，需调整语法：用EXEC sp_executesql代替PREPARE/EXECUTE，字符串拼接用+或STRING_AGG，日期格式也需对应调整。
• 性能优化：确保CURRENT_DATE和ID列有联合索引，避免大表全表扫描导致查询缓慢。

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