MySQL中如何查询未关联任何表的files表记录

要找出files表中未被任何关联表（共100余张，均以file_id作为外键）引用的记录，核心思路是确认files.file_id不存在于所有关联表的file_id列中。以下是两种可行方案：

一、静态SQL写法（适用于表数量较少的场景） #

如果表数量不多，可以手动逐个添加NOT EXISTS子查询：

SELECT f.*
FROM files f
WHERE NOT EXISTS (SELECT 1 FROM table1 t1 WHERE t1.file_id = f.file_id)
  AND NOT EXISTS (SELECT 1 FROM table2 t2 WHERE t2.file_id = f.file_id)
  -- 继续添加所有关联表的NOT EXISTS条件

二、动态生成SQL（推荐用于100+表的场景） #

手动编写100多张表的条件效率极低，可通过查询数据库系统表自动生成目标SQL，不同数据库的实现如下：

MySQL #

-- 生成查询未被引用记录的SQL语句
SELECT CONCAT(
  'SELECT f.* FROM files f WHERE ',
  GROUP_CONCAT(
    CONCAT('NOT EXISTS (SELECT 1 FROM `', table_name, '` WHERE file_id = f.file_id)')
    SEPARATOR ' AND '
  )
) AS query_sql
FROM information_schema.columns
WHERE column_name = 'file_id'
  AND table_schema = DATABASE() -- 指定当前数据库，也可替换为具体库名
  AND table_name != 'files'; -- 排除files表本身

执行此语句后，复制生成的query_sql内容，再次执行即可得到结果。

PostgreSQL #

-- 生成查询SQL
SELECT CONCAT(
  'SELECT f.* FROM files f WHERE ',
  STRING_AGG(
    CONCAT('NOT EXISTS (SELECT 1 FROM "', table_name, '" WHERE file_id = f.file_id)'),
    ' AND '
  )
) AS query_sql
FROM information_schema.columns
WHERE column_name = 'file_id'
  AND table_schema = current_schema()
  AND table_name != 'files';

SQL Server #

-- 生成查询SQL
SELECT CONCAT(
  'SELECT f.* FROM files f WHERE ',
  STRING_AGG(
    CONCAT('NOT EXISTS (SELECT 1 FROM ', QUOTENAME(table_name), ' WHERE file_id = f.file_id)'),
    ' AND '
  )
) AS query_sql
FROM information_schema.columns
WHERE column_name = 'file_id'
  AND table_catalog = DB_NAME()
  AND table_name != 'files';

注意事项 #

• 确认所有关联表的外键列名称确实为file_id，若有差异需调整WHERE column_name = 'file_id'的条件
• 生成SQL后建议先检查语句正确性，避免因表名特殊字符导致语法错误
• 若关联表的file_id列未建立索引，查询性能会受影响，建议先添加索引
• NOT EXISTS通常比LEFT JOIN + IS NULL性能更优，尤其是在大表场景下

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