从MySQL表中删除所有未被外键引用的记录

Question

我有一个地址表，被6个其他表引用（有时是多个表）。其中一些表大约有50万条记录（地址表大约有750,000条记录）。我想要定期运行查询，删除所有没有被任何表引用的记录。以下子查询不是一个选项，因为查询永远无法完成 - 范围太大。

delete from address where address_id not in (select ...)
and not in (select ...) and not in (select ...) ...

我希望能够使用外键约束，并且可以删除所有未被外键约束阻止的记录（因为没有对表的引用）。我找不到一种方法来做到这一点（或者有吗？）。有没有其他好的想法来解决这个问题？

- Rick van der Ree

Answer 1

你可以尝试以下方法。

DELETE
    address
FROM
    address
    LEFT JOIN other_table ON (address.id = other_table.ref_field)
    LEFT JOIN other_table ON (address.id = other_table2.ref_field)
WHERE
    other_table.id IS NULL AND other_table2.id IS NULL

或者

DELETE 
FROM address A
WHERE NOT EXISTS (
  SELECT 1
  FROM other_table B
  WHERE B.a_key = A.id
)

- Always Sunny

Answer 2

我总是使用这个：

DELETE FROM table WHERE id NOT IN (SELECT id FROM OTHER table)

- sathia

---

让你的代码更易读一些：从表中删除那些id不在其他表的外键列中出现的记录。 - Felix Dolderer

Answer 3

我会首先创建一个临时表（t），该表是6个引用表中ID的联合，然后运行以下命令：

DELETE x FROM x LEFT JOIN t USING (ID) WHERE x.ID IS NULL;

x是地址表的地址。

在这里查看“多表语法”： http://dev.mysql.com/doc/refman/5.0/en/delete.html

显然，临时表应该在ID上有它的主键。查询和连接可能需要一些时间，但我看不到其他解决方法。它应该是优化过的，而不是多个子查询版本。

- Dan Bolser