如何获取MySQL表中不存在的指定主键标识符

以下是几种可行的实现方式，假设你的目标表名为target_table，主键字段为id：

方法1：构造临时数据集 + 左连接查询 #

将输入的标识符列表通过UNION ALL构造成临时表，再与目标表左连接，筛选出匹配失败的记录：

SELECT input_id
FROM (
    SELECT 'FEB221571' AS input_id
    UNION ALL SELECT '1221SC170'
    UNION ALL SELECT '75960620447200111'
    UNION ALL SELECT 'ABC12344'
) AS temp_list
LEFT JOIN target_table ON temp_list.input_id = target_table.id
WHERE target_table.id IS NULL;

原理：左连接后，原表中不存在的标识符对应的target_table.id会是NULL，通过WHERE条件筛选即可得到结果。

方法2：使用NOT IN（需注意主键非空） #

如果目标表的主键字段id是NOT NULL约束的，可以用NOT IN简化查询：

SELECT input_id
FROM (
    SELECT 'FEB221571' AS input_id
    UNION ALL SELECT '1221SC170'
    UNION ALL SELECT '75960620447200111'
    UNION ALL SELECT 'ABC12344'
) AS temp_list
WHERE input_id NOT IN (SELECT id FROM target_table);

⚠️ 注意：如果target_table.id存在NULL值，NOT IN会返回空结果，因为NULL与任何值的比较结果都是UNKNOWN，导致整个条件不成立。

方法3：使用NOT EXISTS（推荐） #

这种方法性能稳定，且不受主键字段NULL值的影响：

SELECT input_id
FROM (
    SELECT 'FEB221571' AS input_id
    UNION ALL SELECT '1221SC170'
    UNION ALL SELECT '75960620447200111'
    UNION ALL SELECT 'ABC12344'
) AS temp_list
WHERE NOT EXISTS (
    SELECT 1 FROM target_table WHERE target_table.id = temp_list.input_id
);

原理：对临时表中的每个标识符，检查目标表中是否存在匹配记录，不存在则返回该标识符。

动态生成SQL提示 #

如果你的标识符列表是动态输入的（比如来自应用程序），可以用对应编程语言循环拼接UNION ALL的部分，批量生成临时表的查询语句。

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