MySQL 逗號分隔字段 IN 查詢陷阱：正確使用 FIND_IN_SET 或 LIKE

問題場景

表字段 category 存儲逗號分隔值：'1,2,3'。需查詢包含 2 或 3 的記錄。
嘗試 WHERE category IN ('2','3') 竟然「能跑通且有結果」，但邏輯錯誤。

為什麼 IN「看似生效」實則錯誤？

-- 數據樣本
id | category
1  | '1,2,3'
2  | '2,4'
3  | '3,5'

-- 錯誤查詢
SELECT * FROM t WHERE category IN ('2', '3');
-- 實際執行邏輯：category = '2' OR category = '3'
-- 結果：全不匹配（因為 category 值是 '1,2,3' 等完整字符串）
-- 某些 MySQL 版本隱式轉換可能導致非預期行返回，極不靠譜！

正確方案對比

方案一：FIND_IN_SET（專為 CSV 設計）

-- 查詢包含 2 或 3
SELECT * FROM t 
WHERE FIND_IN_SET('2', category) > 0 
   OR FIND_IN_SET('3', category) > 0;

-- 優點：語義精確，處理邊界逗號自動
-- 缺點：無法使用索引，全表掃描，大數據量性能差

方案二：LIKE 模糊匹配（需處理邊界）

-- 必須加逗號邊界，防止 '2' 匹配到 '12', '23'
SELECT * FROM t 
WHERE CONCAT(',', category, ',') LIKE '%,2,%' 
   OR CONCAT(',', category, ',') LIKE '%,3,%';

方案三：JSON 類型（MySQL 5.7+ 推薦）

-- 修改字段類型為 JSON
ALTER TABLE t MODIFY category JSON;

-- 存儲: ['1','2','3']
-- 查詢: 
SELECT * FROM t WHERE JSON_CONTAINS(category, '["2", "3"]');
-- 可建立多值索引 (MySQL 8.0.17+)，性能優異

方案四：關係型設計（根治）

拆分為關聯表，符合第一範式 (1NF)：

CREATE TABLE item_category (
    item_id INT,
    category_id INT,
    PRIMARY KEY (item_id, category_id)
);
-- 查詢極簡且高效
SELECT i.* FROM item i
JOIN item_category ic ON i.id = ic.item_id
WHERE ic.category_id IN (2, 3);

性能與選型建議

總結

IN 不能直接用於逗號分隔字符串字段。優先考慮重構為關聯表；若不可避免，小數據用 FIND_IN_SET，大數據升級 MySQL 8.0 用 JSON + 多值索引。