MySQL IN 查詢結果依指定順序排序與 NOT IN 陷阱完整解析

前言

在 MySQL 開發中，IN 條件查詢預設會依主鍵或索引順序回傳資料，但實務上常需要依 IN 列表的順序輸出。本文將介紹三種實現方式、NOT IN 遇到 NULL 的經典陷阱，以及複合主鍵下 NOT IN 的正確寫法。

一、讓查詢結果依 IN 列表順序顯示

方法 1：FIELD() 函數（推薦）

SELECT * FROM `table` 
WHERE id IN (3,6,9,1,2,5,8,7) 
ORDER BY FIELD(id, 3,6,9,1,2,5,8,7);

FIELD(str, str1, str2, ...) 會回傳 str 在後續列表中的位置（從 1 開始），找不到則回傳 0。此法語意清晰、效能佳，為首選。

方法 2：FIND_IN_SET()

SELECT * FROM test 
WHERE id IN (3,1,5) 
ORDER BY FIND_IN_SET(id, '3,1,5');

FIND_IN_SET 以字串形式比對，適用於字串型欄位，但需注意效能：無法使用索引，大資料量時較慢。

方法 3：SUBSTRING_INDEX() 技巧

SELECT * FROM test 
WHERE id IN (3,1,5) 
ORDER BY SUBSTRING_INDEX('3,1,2', id, 1);

利用字串截取特性排序，屬 Hack 寫法，不建議生產環境使用，僅供參考。

二、NOT IN 遇到 NULL 的致命陷阱

當 NOT IN 子查詢或列表中包含 NULL，整個查詢將回傳 0 筆資料。

錯誤示範

SELECT COUNT(name) FROM CVE 
WHERE name NOT IN ('CVE-1999-0001', 'CVE-1999-0002', NULL); 
-- 結果永遠為 0

根本原因

NOT IN 等價於 NOT (col = val1 OR col = val2 OR ...)。若列表含 NULL，比較 col = NULL 結果為 UNKNOWN，導致整個 NOT (TRUE OR UNKNOWN) 變為 UNKNOWN，不符合 WHERE 的 TRUE 條件。

正確解法

-- 方案 1：子查詢過濾 NULL
SELECT COUNT(DISTINCT name) 
FROM CVE 
WHERE name NOT IN (
    SELECT cveID FROM cve_sig WHERE cveID IS NOT NULL
);

-- 方案 2：改用 NOT EXISTS（推薦）
SELECT COUNT(*) FROM CVE c
WHERE NOT EXISTS (
    SELECT 1 FROM cve_sig s 
    WHERE s.cveID = c.name AND s.cveID IS NOT NULL
);

三、複合主鍵下的 NOT IN 正確寫法

表 info 主鍵為 (id, info_type_id)，欲排除 (3,8) 及 (4,8) 兩筆資料。

錯誤寫法

SELECT * FROM info 
WHERE episode_id=100034 
  AND id NOT IN (3,4) 
  AND info_type_id NOT IN (8);

此寫法會錯誤排除所有 id=3 或 4 且 info_type_id=8 的組合，導致 (3,4)、(100001,4) 等合法資料被誤刪。

正確寫法

SELECT * FROM info 
WHERE episode_id=100034 
  AND (id <> 3 OR info_type_id <> 8)
  AND (id <> 4 OR info_type_id <> 8);

邏輯解析：(id, type) NOT IN ((3,8), (4,8)) 等價於 NOT ((id=3 AND type=8) OR (id=4 AND type=8))，依德摩根定律轉換為 (id<>3 OR type<>8) AND (id<>4 OR type<>8)。

總結

• 指定順序排序首選 ORDER BY FIELD(...)。
• NOT IN 列表/子查詢嚴禁出現 NULL，改用 NOT EXISTS 或預先過濾。
• 複合鍵排除需用邏輯運算展開，不可拆分欄位獨立 NOT IN。