如何用单条MySQL语句实现两不同表指定列比对并删除差异数据

解决方案 #

你要实现的逻辑是删除table1中column1值不存在于table2的column2列中的记录，以下是几种单SQL实现方式：

写法1：NOT IN 实现（最贴近原逻辑） #

DELETE FROM table1 
WHERE column1 NOT IN (
    SELECT column2 FROM table2
);

注意：如果table2.column2允许为NULL，该写法会返回空结果，不会执行删除，空值场景不推荐使用

写法2：LEFT JOIN 实现（兼容空值场景，性能更优） #

DELETE t1
FROM table1 t1
LEFT JOIN table2 t2 ON t1.column1 = t2.column2
WHERE t2.column2 IS NULL;

这种写法直接通过关联匹配，没有子查询，大多数数据库下性能优于NOT IN写法，也不会受table2.column2空值的影响。

写法3：NOT EXISTS 实现（兼容空值，语义清晰） #

DELETE FROM table1 t1
WHERE NOT EXISTS (
    SELECT 1 FROM table2 t2 WHERE t2.column2 = t1.column1
);

该写法同样不受空值影响，执行效率和LEFT JOIN写法基本持平。

补充说明 #

你原PHP代码的逻辑存在两个潜在问题：

• 如果table1数据量很大，拉取全量column1到PHP内存中会占用过多内存，甚至导致程序崩溃
• 如果diff结果为空，拼接出来的SQL会变成IN ('')，会误删column1为空的记录，单条SQL可以避免这类字符串拼接导致的逻辑错误。

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