03.MySQL IN 详解

一、💡 一句话理解

核心结论

IN 用来判断“一个值是否属于一组候选值”。它本身并不天然慢，真正需要警惕的是 NULL、数据类型混用、超大列表，以及缺少合适索引。

-- 查询状态为已付款或已发货的订单。
SELECT *
FROM orders
WHERE status IN ('PAID', 'SHIPPED');

逻辑上可以理解成：

SELECT *
FROM orders
WHERE status = 'PAID'
   OR status = 'SHIPPED';

本文以 MySQL 8.x 为例。基础条件查询可先参考 01.MySQL基础语法与约束，子查询基础可参考 03.MySQL多表查询。

二、🧭 理论：IN 是什么

2.1 固定值列表

最常见的形式是把候选值直接写在括号中：

-- 查询三个指定状态中的订单。
SELECT *
FROM orders
WHERE status IN ('PAID', 'SHIPPED', 'FINISHED');

适合查询多个状态、用户 ID、分类或枚举值。

2.2 子查询

IN 括号里也可以是一条子查询：

-- 先查出 VIP 用户的 ID，再查询这些用户的订单。
SELECT *
FROM orders
WHERE user_id IN (
    SELECT id
    FROM users
    WHERE level = 'VIP'
);

这里的含义不是“逐条执行很多次 SQL”，而是判断订单的 user_id 是否属于子查询产生的结果集合。对 IN 子查询，MySQL 优化器可能选择半连接、物化或 EXISTS 等执行策略，最终方式要看执行计划。

2.3 多列组合匹配

MySQL 支持使用行构造器，让多列作为一个整体进行匹配：

-- 两列必须同时匹配同一组候选值。
SELECT *
FROM orders
WHERE (user_id, status) IN (
    (1001, 'PAID'),
    (1002, 'SHIPPED')
);

逻辑上相当于：

SELECT *
FROM orders
WHERE (user_id = 1001 AND status = 'PAID')
   OR (user_id = 1002 AND status = 'SHIPPED');

也可以配合多列子查询：

-- 左右两边的列数和对应含义必须一致。
SELECT *
FROM orders
WHERE (user_id, product_id) IN (
    SELECT user_id, product_id
    FROM favorites
);

三、⚙️ 理论：IN 是怎么判断的

3.1 固定列表近似多个 OR

下面两个条件在逻辑上表达同一件事：

-- IN 写法更紧凑。
WHERE status IN ('PAID', 'SHIPPED', 'FINISHED')
 
-- 展开后是多个等值判断通过 OR 连接。
WHERE status = 'PAID'
   OR status = 'SHIPPED'
   OR status = 'FINISHED'

对于子查询，MySQL 官方把 expr IN (subquery) 定义为 expr = ANY (subquery)：子查询结果中只要存在一个相等值，条件就成立。

3.2 SQL 使用三值逻辑

IN 的结果不只有真和假：

结果	SQL 含义	WHERE 是否保留该行
1	TRUE，条件成立	保留
0	FALSE，条件不成立	不保留
NULL	UNKNOWN，无法确定	不保留

SELECT
    2 IN (1, 2, 3)      AS matched,      -- 1
    5 IN (1, 2, 3)      AS not_matched,  -- 0
    NULL IN (1, 2, 3)   AS unknown_value;-- NULL

3.3 候选列表中存在 NULL

SELECT 2 IN (1, 2, NULL); -- 结果是 1，因为已经找到 2。
SELECT 5 IN (1, 2, NULL); -- 结果是 NULL，而不是 0。

第二个表达式近似于：

5 = 1 OR 5 = 2 OR 5 = NULL
FALSE OR FALSE OR UNKNOWN
最终结果：UNKNOWN，也就是 NULL

MySQL 官方说明：左侧表达式为 NULL 时，或者没有找到匹配值但候选列表中存在 NULL 时，IN() 会返回 NULL。

NULL 不是空字符串或数字 0

NULL 表示未知或缺失。判断它必须使用 IS NULL 或 IS NOT NULL，不能使用 = NULL 或 <> NULL。

四、⚠️ 理论：NOT IN 与 NULL 陷阱

4.1 NOT IN 的真实含义

NOT IN 是对整个 IN 判断取反：

WHERE id NOT IN (1, 2, 3)

逻辑上可以理解为：

WHERE id <> 1
  AND id <> 2
  AND id <> 3

4.2 为什么一个 NULL 能让结果消失

SELECT 5 NOT IN (1, 2, NULL); -- 结果为 NULL，不是 1。

判断过程近似于：

5 <> 1 AND 5 <> 2 AND 5 <> NULL
TRUE AND TRUE AND UNKNOWN
最终结果：UNKNOWN，也就是 NULL

因此下面的查询存在风险：

SELECT *
FROM users
WHERE id NOT IN (
    SELECT user_id
    FROM blacklist
);

如果 blacklist.user_id 中存在 NULL，原本看起来“不在黑名单”的记录也可能因为判断结果为 UNKNOWN 而不返回。

4.3 两种安全处理方式

第一种方式是明确排除子查询中的 NULL：

SELECT *
FROM users
WHERE id NOT IN (
    SELECT user_id
    FROM blacklist
    WHERE user_id IS NOT NULL
);

第二种方式通常更稳妥：使用 NOT EXISTS 表达“不存在匹配记录”。

SELECT *
FROM users AS u
WHERE NOT EXISTS (
    SELECT 1
    FROM blacklist AS b
    WHERE b.user_id = u.id
);

如果外层 u.id 也允许为 NULL，要根据业务语义决定是否添加：

WHERE u.id IS NOT NULL
  AND NOT EXISTS (...)

记忆规则

NOT IN 的数据来源只要可能包含 NULL，就要先排除 NULL，或者改用 NOT EXISTS。

五、🧭 理论：IN、EXISTS 和 JOIN 怎么选

5.1 固定的小集合使用 IN

WHERE status IN ('PAID', 'SHIPPED')

这是最直观、最容易维护的写法。

5.2 只关心关联记录是否存在

下面两种写法表达相近的业务意图：

-- IN：判断 user_id 是否属于 VIP 用户 ID 集合。
SELECT *
FROM orders
WHERE user_id IN (
    SELECT id
    FROM users
    WHERE level = 'VIP'
);

-- EXISTS：判断当前订单是否存在对应的 VIP 用户。
SELECT *
FROM orders AS o
WHERE EXISTS (
    SELECT 1
    FROM users AS u
    WHERE u.id = o.user_id
      AND u.level = 'VIP'
);

现代 MySQL 可能把它们转换成相近的执行策略，不能只凭写法断定 EXISTS 一定更快。应使用 09.EXPLAIN 中的方法检查真实执行计划。

5.3 需要关联表字段时使用 JOIN

-- 结果需要用户名，所以直接关联 users 表。
SELECT o.id, o.amount, u.username
FROM orders AS o
JOIN users AS u ON u.id = o.user_id
WHERE u.level = 'VIP';

IN 和 EXISTS 只负责判断“是否存在”；JOIN 还能取得关联表字段，但一对多关联可能让外层记录重复。

六、📈 理论：索引与性能

6.1 IN 可以使用索引

假设已经建立索引：

CREATE INDEX idx_orders_status ON orders(status);

下面的 IN 查询有机会使用该索引：

SELECT *
FROM orders
WHERE status IN ('PAID', 'SHIPPED');

IN 并不会天然导致全表扫描。优化器是否选择索引，还会考虑候选值数量、数据分布、选择性、预计返回比例、回表成本和表统计信息。索引原理可参考 04.MySQL 索引。

6.2 用执行计划验证

-- 查看优化器预计采用的执行方案。
EXPLAIN
SELECT *
FROM orders
WHERE status IN ('PAID', 'SHIPPED');

MySQL 8.0.18 及以上还可以观察实际执行：

-- 注意：EXPLAIN ANALYZE 会真正执行这条查询。
EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE status IN ('PAID', 'SHIPPED');

重点关注 key、预计和实际读取行数，以及过滤后保留的行数。完整分析方法见 09.EXPLAIN 和 10.慢查询与 SQL 优化。

6.3 保持字段与候选值类型一致

不推荐：

-- 对索引列做转换，还混淆了数字和字符串语义。
WHERE CAST(user_id AS CHAR) IN ('1001', '1002')

推荐：

-- user_id 是数字列，候选值也使用数字。
WHERE user_id IN (1001, 1002)

隐式类型转换可能产生反直觉结果，也可能影响索引使用。

6.4 超大 IN 列表

下面的 SQL 即使能够执行，也不适合无限扩张：

WHERE id IN (1, 2, 3 /* ……几万个值…… */)

它会带来更长的网络传输、SQL 解析和优化时间，也不便维护。MySQL 官方说明，IN() 候选数量最终受 max_allowed_packet 限制。

大量 ID 更适合批量写入临时表，再进行关联：

CREATE TEMPORARY TABLE selected_ids (
    id BIGINT PRIMARY KEY
);
 
-- 在应用层使用批量参数安全写入待查询 ID。
-- INSERT INTO selected_ids (id) VALUES (?), (?), ...;
 
SELECT o.*
FROM orders AS o
JOIN selected_ids AS s ON s.id = o.id;

七、⚠️ 实践前必须避开的常见错误

7.1 混用字符串和数字

不要这样写：

WHERE code IN (1, 2, 'A')

MySQL 可能执行隐式类型转换，得到反直觉结果。字段是字符串时统一使用字符串：

WHERE code IN ('1', '2', 'A')

字段是数字时统一使用数字：

WHERE code IN (1, 2, 3)

7.2 程序生成空列表

下面是非法 SQL：

WHERE id IN ()

应用收到空集合时，应直接返回空结果、跳过查询，或者根据业务明确生成恒假条件：

WHERE 1 = 0

7.3 一个占位符不能表示多个值

错误思路：

WHERE id IN (?)

然后把一个参数设置为字符串 "1,2,3"。数据库会把它当成一个标量值，而不是三个独立值。

正确做法是按集合数量生成占位符，并逐个绑定参数：

WHERE id IN (?, ?, ?)

不要直接拼接用户输入，否则容易产生 SQL 注入。

7.4 不要用 FIND_IN_SET 代替正常的 IN

不推荐：

WHERE FIND_IN_SET(id, '1,2,3')

推荐：

WHERE id IN (1, 2, 3)

更根本的做法是遵循关系型设计，不要把多个值塞进一个逗号分隔字段。

八、🚀 实践：从准备到验证

8.1 前置准备

准备一个 MySQL 8.x 测试实例和能够执行 SQL 的客户端。本实验使用独立数据库 mysql_in_lab，不会修改已有业务表。

先确认版本：

SELECT VERSION();

8.2 可以拿来干什么

8.2.1 查询多个业务状态

前置对象是订单表的 status 列：

SELECT *
FROM mysql_in_lab.demo_orders
WHERE status IN ('PAID', 'SHIPPED');

输入是一组订单状态，输出是状态属于该集合的订单。适合枚举、分类和多选筛选条件。

8.2.2 查询属于指定用户集合的记录

前置对象是订单表的 user_id 列和用户表：

SELECT *
FROM mysql_in_lab.demo_orders
WHERE user_id IN (
    SELECT id
    FROM mysql_in_lab.demo_users
    WHERE level = 'VIP'
);

子查询先产生 VIP 用户 ID 集合，外层再返回这些用户的订单。适合“属于某个动态集合”的查询。

8.2.3 排除已经存在的关联记录

如果排除集合可能出现 NULL，优先使用 NOT EXISTS：

SELECT *
FROM mysql_in_lab.demo_users AS u
WHERE NOT EXISTS (
    SELECT 1
    FROM mysql_in_lab.demo_blacklist AS b
    WHERE b.user_id = u.id
);

输入是用户表和黑名单表，输出是不在黑名单中的用户。

8.3 完整实践：建表、查询和验证

下面的 SQL 可以按顺序整体执行：

-- 创建独立练习库，避免操作已有业务数据。
CREATE DATABASE IF NOT EXISTS mysql_in_lab
    DEFAULT CHARACTER SET utf8mb4;
 
USE mysql_in_lab;
 
-- 用户表用于演示 IN 子查询。
CREATE TABLE IF NOT EXISTS demo_users (
    id BIGINT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    level VARCHAR(20) NOT NULL,
    INDEX idx_level (level)
);
 
-- 订单表用于演示固定列表和索引。
CREATE TABLE IF NOT EXISTS demo_orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    status VARCHAR(20) NULL,
    amount DECIMAL(10, 2) NOT NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_status (status)
);
 
-- 黑名单列故意允许 NULL，用来复现 NOT IN 陷阱。
CREATE TABLE IF NOT EXISTS demo_blacklist (
    id BIGINT PRIMARY KEY,
    user_id BIGINT NULL,
    INDEX idx_user_id (user_id)
);
 
-- 清空练习数据，保证脚本重复执行时结果一致。
TRUNCATE TABLE demo_blacklist;
TRUNCATE TABLE demo_orders;
TRUNCATE TABLE demo_users;
 
INSERT INTO demo_users (id, username, level) VALUES
    (101, '张三', 'NORMAL'),
    (102, '李四', 'VIP'),
    (103, '王五', 'VIP'),
    (104, '赵六', 'NORMAL');
 
INSERT INTO demo_orders (id, user_id, status, amount) VALUES
    (1, 101, 'CREATED', 18.00),
    (2, 102, 'PAID', 28.00),
    (3, 103, 'SHIPPED', 38.00),
    (4, 104, NULL, 48.00);
 
-- NULL 用于演示 NOT IN 的三值逻辑陷阱。
INSERT INTO demo_blacklist (id, user_id) VALUES
    (1, 102),
    (2, NULL);
 
-- 验证 1：固定列表查询。
-- 预期返回订单 2、3。
SELECT *
FROM demo_orders
WHERE status IN ('PAID', 'SHIPPED');
 
-- 验证 2：IN 子查询。
-- VIP 用户是 102、103，预期返回订单 2、3。
SELECT *
FROM demo_orders
WHERE user_id IN (
    SELECT id
    FROM demo_users
    WHERE level = 'VIP'
);
 
-- 验证 3：观察 NOT IN 陷阱。
-- 子查询包含 NULL，这条语句不会按直觉返回 101、103、104。
SELECT *
FROM demo_users
WHERE id NOT IN (
    SELECT user_id
    FROM demo_blacklist
);
 
-- 验证 4：过滤子查询中的 NULL。
-- 预期返回 101、103、104。
SELECT *
FROM demo_users
WHERE id NOT IN (
    SELECT user_id
    FROM demo_blacklist
    WHERE user_id IS NOT NULL
);
 
-- 验证 5：使用 NOT EXISTS，预期同样返回 101、103、104。
SELECT *
FROM demo_users AS u
WHERE NOT EXISTS (
    SELECT 1
    FROM demo_blacklist AS b
    WHERE b.user_id = u.id
);
 
-- 验证 6：查看 status IN 查询是否使用 idx_status。
EXPLAIN
SELECT *
FROM demo_orders
WHERE status IN ('PAID', 'SHIPPED');

如果建表时报“数据库已存在”或“表已存在”，脚本中的 IF NOT EXISTS 会避免重复创建错误；如果数据插入时发生主键重复，确认前面的 TRUNCATE TABLE 已成功执行。

九、📌 总结

• IN 表示“属于候选集合”，固定列表可理解为多个等值条件通过 OR 连接。
• expr IN (subquery) 表示子查询结果中至少存在一个与 expr 相等的值。
• WHERE 只保留 TRUE，FALSE 和 NULL 都会被过滤。
• NOT IN 最大的风险是候选集合中的 NULL；优先排除 NULL 或改用 NOT EXISTS。
• IN 可以使用索引，并不天然慢；最终应通过 EXPLAIN 或 EXPLAIN ANALYZE 验证。
• 候选值必须保持类型一致，超大列表应考虑临时表加 JOIN。

最终记忆句

小集合用 IN，动态存在性可用 IN 或 EXISTS，排除集合时先想到 NULL 和 NOT EXISTS。

十、📚 官方资料

• MySQL 8.4：Comparison Functions and Operators
• MySQL 8.4：Subqueries with ANY, IN, or SOME
• MySQL 8.4：Optimizing Subqueries
• MySQL 8.4：Restrictions on Subqueries
• MySQL 8.4：Working with NULL Values