-- 靜態列表
SELECT * FROM t WHERE id IN (1,2,3,4,5);
-- 子查詢
SELECT * FROM t1 WHERE id IN (SELECT id FROM t2 WHERE status=1);
-- 刪除
DELETE FROM t WHERE id IN (835,836,837,...,999806);
max_allowed_packet(單條 SQL 總長度)。max_allowed_packet=4M 時,約支援 **50 萬個 INT 值**(含逗號、空格)。Packet too large 或 Got a packet bigger than 'max_allowed_packet'。IN 可使用索引範圍掃描,EXPLAIN 顯示 type=range、key=idx_id。
-- 建立臨時表
CREATE TEMPORARY TABLE tmp_ids (id INT PRIMARY KEY);
INSERT INTO tmp_ids VALUES (1),(2),...; -- 或 LOAD DATA
-- JOIN 替代 IN
SELECT t.* FROM t
INNER JOIN tmp_ids USING (id);
-- 危險:若子查詢返回包含 NULL,整個 NOT IN 結果為空集
SELECT * FROM t1 WHERE id NOT IN (SELECT col FROM t2);
-- 安全寫法
SELECT * FROM t1 WHERE id NOT IN (SELECT col FROM t2 WHERE col IS NOT NULL);
-- 或用 NOT EXISTS
SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.col = t1.id);
IN / EXISTS 差異不大。EXISTS 通常更優(可短路)。EXPLAIN 實測為準。IN 列表數量受 max_allowed_packet 限制,大列表請**改用臨時表 JOIN**。注意 NOT IN 的 NULL 陷阱,優先使用 NOT EXISTS。配合索引與 EXPLAIN 驗證執行計劃,確保高效執行。