存在互转逻辑的两张数据表对比校验方案优化咨询

现有校验流程的核心痛点是转换类差异依赖抽样核验，存在漏判风险，且人工成本高，可直接用全量规则判断的方案替换，具体实现如下：

• 第一步：全量关联两张表
  按主键ID关联Table1和Table2，不需要提前按TYPE筛选，一次性拉取所有记录的对应字段值。
• 第二步：内置转换规则自动过滤正常差异
  直接在关联逻辑里加入正常转换的判断条件，所有满足以下规则的差异直接判定为非问题，无需上报：

正常转换判定规则：

1. 两表对应记录的TYPE值不同（一个为P、一个为O）
2. 若Table1为P类型、Table2为O类型：Table1的Date of birth = Table2的foundation date，且Table1的foundation date、Table2的Date of birth均为null
3. 若Table1为O类型、Table2为P类型：Table1的foundation date = Table2的Date of birth，且Table1的Date of birth、Table2的foundation date均为null

• 第三步：直接输出真实错误记录
  排除所有符合正常转换规则的记录后，剩余所有字段不匹配的记录均为真实数据错误，直接导出上报即可。

如果用SQL实现，可一步完成所有逻辑，示例代码如下：

SELECT 
    t1.ID,
    t1.TYPE AS table1_type,
    t2.TYPE AS table2_type,
    t1.`Date of birth` AS table1_birth_date,
    t2.`Date of birth` AS table2_birth_date,
    t1.`foundation date` AS table1_foundation_date,
    t2.`foundation date` AS table2_foundation_date
FROM table1 t1
INNER JOIN table2 t2 
    ON t1.ID = t2.ID
WHERE 
    -- 过滤掉正常转换的记录
    NOT (
        (t1.TYPE = 'P' AND t2.TYPE = 'O' 
        AND t1.`Date of birth` = t2.`foundation date` 
        AND t1.`foundation date` IS NULL 
        AND t2.`Date of birth` IS NULL)
        OR
        (t1.TYPE = 'O' AND t2.TYPE = 'P' 
        AND t1.`foundation date` = t2.`Date of birth` 
        AND t1.`Date of birth` IS NULL 
        AND t2.`foundation date` IS NULL)
    )
    -- 筛选字段不匹配的记录
    AND (
        t1.TYPE <> t2.TYPE
        OR IFNULL(t1.`Date of birth`, '0000-00-00') <> IFNULL(t2.`Date of birth`, '0000-00-00')
        OR IFNULL(t1.`foundation date`, '0000-00-00') <> IFNULL(t2.`foundation date`, '0000-00-00')
    )

方案优势 #

1. 效率高：十万级数据执行上述查询仅需数秒，远快于现有先多次跑校验工具再抽样核验的流程
2. 准确率100%：所有差异全量覆盖，不存在抽样漏判的风险
3. 可复用性强：脚本一次编写可重复使用，无需每次校验都做人工操作

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