从另一个表中删除 ID 不匹配的 sql 行
我正在尝试删除 mysql 表中的孤立条目.
I'm trying to delete orphan entries in a mysql table.
我有 2 张这样的桌子:
I have 2 tables like this:
表文件:
| id | ....
------------
| 1 | ....
| 2 | ....
| 7 | ....
| 9 | ....
表blob:
| fileid | ....
------------
| 1 | ....
| 2 | ....
| 3 | ....
| 4 | ....
| 4 | ....
| 4 | ....
| 9 | ....
fileid 和 id 列可用于将表连接在一起.
The fileid and id columns can be used to join the tables together.
我想删除表 blob 中所有在表 files.id 中找不到 fileid 的行.
I want to delete all rows in table blob where fileid cannot be found in the table files.id.
因此使用上面的示例将删除行:3 &blob 表中的 4(s).
So using the example above that would delete rows: 3 & 4(s) in the blob table.
推荐答案
使用 LEFT JOIN/IS NULL:
DELETE b FROM BLOB b
LEFT JOIN FILES f ON f.id = b.fileid
WHERE f.id IS NULL
使用 NOT EXISTS:
DELETE FROM BLOB
WHERE NOT EXISTS(SELECT NULL
FROM FILES f
WHERE f.id = fileid)
使用 NOT IN:
DELETE FROM BLOB
WHERE fileid NOT IN (SELECT f.id
FROM FILES f)
警告
尽可能在事务中执行 DELETE(假设支持 - IE:不在 MyISAM 上)，以便在出现问题时可以使用回滚来恢复更改.
Warning
Whenever possible, perform DELETEs within a transaction (assuming supported - IE: Not on MyISAM) so you can use rollback to revert changes in case of problems.
相关文章