如何找出无外键引用的user_agents表行（无需显式列关联表）

我有个user_agents表，结构如下：

CREATE TABLE user_agents (
  pk bigint NOT NULL AUTO_INCREMENT,
  user_agent TEXT NOT NULL,
  user_agent_hash BINARY(16) UNIQUE NOT NULL,
  PRIMARY KEY (pk),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_cs;

这个表被好多其他表通过外键关联着，现在我要清理掉所有没人引用的user_agents记录。

目前我是这么写查询的：

SELECT pk
FROM user_agents ua
  LEFT JOIN table_1 t1 ON ua.pk = t1.user_agent_fk
  LEFT JOIN table_2 t2 ON ua.pk = t2.user_agent_fk
  LEFT JOIN table_3 t3 ON ua.pk = t3.user_agent_fk
  LEFT JOIN table_4 t4 ON ua.pk = t4.user_agent_fk
WHERE t1.pk IS NULL
  AND t2.pk IS NULL
  AND t3.pk IS NULL
  AND t4.pk IS NULL;

但这写法太麻烦了，以后要是新增个关联的table_5，还得手动改这个查询。有没有不用手动列所有关联表，就能找出无引用记录的办法？

方法1：用INFORMATION_SCHEMA自动生成查询 #

MySQL的INFORMATION_SCHEMA里存着所有表的外键信息，我们可以查这个视图自动获取所有关联user_agents.pk的表和字段，然后动态拼查询。

先跑下面这个语句，拿到所有需要的关联信息：

SELECT 
  CONCAT('LEFT JOIN ', referenced_table_name, ' t', ROW_NUMBER() OVER(), ' ON ua.pk = t', ROW_NUMBER() OVER(), '.', column_name) AS join_part,
  CONCAT('t', ROW_NUMBER() OVER(), '.', referenced_column_name, ' IS NULL') AS where_part
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE referenced_table_name = 'user_agents' 
  AND referenced_column_name = 'pk'
  AND table_schema = DATABASE(); -- 只查当前数据库的表

把查出来的join_part全部拼到FROM user_agents ua后面，where_part用AND拼起来放到WHERE里，就是自动包含所有关联表的查询了。以后加了新的关联表，再跑一遍这个语句更新查询就行，不用手动改。

比如生成的完整查询大概是这样：

SELECT pk
FROM user_agents ua
LEFT JOIN table_1 t1 ON ua.pk = t1.user_agent_fk
LEFT JOIN table_2 t2 ON ua.pk = t2.user_agent_fk
LEFT JOIN table_3 t3 ON ua.pk = t3.user_agent_fk
LEFT JOIN table_4 t4 ON ua.pk = t4.user_agent_fk
WHERE t1.pk IS NULL AND t2.pk IS NULL AND t3.pk IS NULL AND t4.pk IS NULL;

方法2：写存储过程自动清理 #

如果要定期做清理，可以整个存储过程，自动生成并执行删除操作：

DELIMITER //
CREATE PROCEDURE CleanUnusedUserAgents()
BEGIN
  DECLARE join_sql TEXT DEFAULT '';
  DECLARE where_sql TEXT DEFAULT '';
  DECLARE full_delete_sql TEXT;

  -- 拼所有LEFT JOIN的部分
  SELECT GROUP_CONCAT(
    CONCAT('LEFT JOIN ', referenced_table_name, ' t', seq, ' ON ua.pk = t', seq, '.', column_name)
    SEPARATOR ' '
  ) INTO join_sql
  FROM (
    SELECT 
      referenced_table_name, 
      column_name,
      ROW_NUMBER() OVER() AS seq
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE referenced_table_name = 'user_agents' 
      AND referenced_column_name = 'pk'
      AND table_schema = DATABASE()
  ) AS ref_tables;

  -- 拼WHERE条件部分
  SELECT GROUP_CONCAT(
    CONCAT('t', seq, '.', referenced_column_name, ' IS NULL')
    SEPARATOR ' AND '
  ) INTO where_sql
  FROM (
    SELECT 
      referenced_column_name,
      ROW_NUMBER() OVER() AS seq
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE referenced_table_name = 'user_agents' 
      AND referenced_column_name = 'pk'
      AND table_schema = DATABASE()
  ) AS ref_tables;

  -- 生成完整的删除语句（先子查询找出要删的pk，再删除）
  SET full_delete_sql = CONCAT(
    'DELETE FROM user_agents WHERE pk IN (SELECT pk FROM user_agents ua ', join_sql, ' WHERE ', where_sql, ')'
  );

  -- 执行语句
  PREPARE stmt FROM full_delete_sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

以后要清理的时候，直接调用这个存储过程就行：

CALL CleanUnusedUserAgents();

注意点 #

• 执行这个查询的账号得有访问INFORMATION_SCHEMA的权限。
• 删数据之前一定要先跑查询确认要删的记录，别误删了有用的数据。
• 如果外键设置了ON DELETE CASCADE或者ON DELETE SET NULL，要注意这些规则的影响，但咱们这个场景是删没人引用的记录，不影响现有关联的数据。

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