mysql - 查找表中在同一数据库的任何其他表中没有引用的所有行

Question

我正在使用外键和 InnoDB。我有一个图像表，其中包含所有上传的图像数据。每次我删除与图像表的连接时，我通常会将图像表中的特定行标记为已删除。因此垃圾收集器通常会删除未使用的图像。

没有任何图像同时被超过 1 个连接使用。

为了优化目的，我想找到图像表中没有连接的所有行(“Id”列)。因为任何未使用的行都是僵尸行。

根据我们失败的测试，仍有一些行没有连接。

示例图像表:

CREATE TABLE `image` (
  `Id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `FileName` varchar(255) NOT NULL,
  `Is_Deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `Disabled` tinyint(1) unsigned DEFAULT '0',
  PRIMARY KEY (`Id`),
) ENGINE=InnoDB AUTO_INCREMENT=816 DEFAULT CHARSET=utf8;

Answer 1

您可以使用如下查询列出所有“僵尸”行:

SELECT
  *
FROM
  image
WHERE
  image.`Id` NOT IN (SELECT table1.IdFK FROM table1 UNION 
                     SELECT table2.IdFK FROM table2 UNION
                     SELECT table3.IdFK FROM table3 ... )

它基本上检查 image.Id 是否作为 FK 存在于另一个表中。您可以添加所需数量的 UNION。

作为旁注，也许应该考虑添加完整性约束？