如何利用ON DELETE仅删除孤立子行？

好问题！你现在通过查询统计引用次数的方法确实能找出孤立的子表行，但确实有更规范、更高效的实现方式，下面给你几种常用的方案：

1. 直接用DELETE ... WHERE EXISTS一键清理孤立行 #

不需要先查询再手动删除，可以用单条SQL语句直接删除所有未被父表引用的子表行，这是数据库操作中最直接的方式：

DELETE FROM nobel_prizes.countries c
WHERE NOT EXISTS (
    SELECT 1 FROM nobel_prizes.person p
    WHERE p.country_id = c.id
);

这条语句会自动筛选出所有没有关联person记录的countries行并删除，比分步操作更高效，也符合数据库操作的规范。

2. 创建触发器实现自动清理 #

如果你希望在删除父表（person）行时，自动检查对应的子表（countries）行是否还有其他引用，没有的话就自动删除，可以创建一个AFTER DELETE触发器：

DELIMITER //
CREATE TRIGGER clean_orphaned_countries
AFTER DELETE ON nobel_prizes.person
FOR EACH ROW
BEGIN
    -- 仅当该country没有其他关联person时才删除
    DELETE FROM nobel_prizes.countries c
    WHERE c.id = OLD.country_id
    AND NOT EXISTS (
        SELECT 1 FROM nobel_prizes.person p
        WHERE p.country_id = c.id
    );
END //
DELIMITER ;

设置好这个触发器后，每次删除person记录时，数据库会自动帮你检查并清理掉变成孤立状态的countries行，完全不需要手动介入。

3. 调整外键约束逻辑（按需选择） #

你当前使用的ON DELETE CASCADE其实不太适合这个场景——因为它会尝试在删除任意一条关联的person记录时就删除对应的countries行，这就导致了其他person还引用该countries行时会报错。

如果业务允许的话，可以调整外键约束：

• 若person.country_id允许为NULL，可以改为ON DELETE SET NULL，这样删除person行时只会把对应的country_id设为NULL，不会触发countries行的删除，之后再用上面的方法清理孤立行；
• 保持默认的ON DELETE RESTRICT（或NO ACTION），这样删除person行时不会影响countries行，再定期清理孤立行即可。

另外，你的初始查询可以优化一下，用LEFT JOIN能更直观地显示所有countries的引用次数（包括0次的孤立行）：

SELECT c.id AS country_id, c.country, COUNT(p.id) AS reference_count
FROM nobel_prizes.countries c
LEFT JOIN nobel_prizes.person p ON c.id = p.country_id
GROUP BY c.id, c.country;

内容的提问来源于stack exchange，提问作者Gergely Tóth