MySQL 查詢逗號分隔字符串字段：FIND_IN_SET 與 REGEXP 實戰對比

MySQL 查詢逗號分隔字符串字段：FIND_IN_SET 與 REGEXP 實戰對比

反範式設計中常見「一字段存逗號分隔值」（如 pnum='1,2,4'）。MySQL 提供 FIND_IN_SET() 專用函數，也可用 REGEXP 正則匹配。本文實測對比兩者語義差異、性能與適用場景。

測試數據準備

CREATE TABLE test (
  id INT(6) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  pname VARCHAR(20) NOT NULL,
  pnum VARCHAR(50) NOT NULL
);
INSERT INTO test(pname,pnum) VALUES
('產品1','1,2,4'), ('產品2','2,4,7'), ('產品3','3,4'),
('產品4','1,7,8,9'), ('產品5','33,4');

方案一：FIND_IN_SET() —— 精確集合成員檢測

-- 查找 pnum 包含 3 或 9 的記錄
SELECT * FROM test 
WHERE FIND_IN_SET('3', pnum) OR FIND_IN_SET('9', pnum);

結果：

+----+-------+---------+
| id | pname | pnum    |
+----+-------+---------+
| 3  | 產品3 | 3,4     |
| 4  | 產品4 | 1,7,8,9 |
+----+-------+---------+

• 語義：將字段視為集合，按逗號分割，判斷值是否為完整元素。
• 返回值：元素位置（從 1 開始），不存在返回 0。
• 優點：語義精確、不誤匹配子串（33 不會匹配 3）。
• 缺點：不支援索引、全表掃描；字段值含空格需 REPLACE(pnum,' ','') 預處理。

方案二：REGEXP 正則表達式 —— 靈活但易誤傷

SELECT * FROM test WHERE pnum REGEXP '(3|9)';

結果：

+----+-------+---------+
| id | pname | pnum    |
+----+-------+---------+
| 3  | 產品3 | 3,4     |
| 4  | 產品4 | 1,7,8,9 |
| 5  | 產品5 | 33,4    |  <-- 誤匹配：33 含 3
+----+-------+---------+

• 語義：字符串模式匹配，3 會匹配 33、13 等任意包含 '3' 的子串。
• 修正：用邊界斷言 REGEXP '(^|,)3(,|$)' 模擬集合邊界，但寫法繁瑣、性能更差。
• 適用：模糊匹配、前綴/後綴、複雜模式；不適用 精確集合成員判斷。

進階用法：FIND_IN_SET() 實現自定義排序

-- 需按 id 4,2,3 順序返回
SELECT * FROM test WHERE id IN (4,2,3) ORDER BY FIND_IN_SET(id, '4,2,3');

結果：

+----+-------+---------+
| id | pname | pnum    |
+----+-------+---------+
| 4  | 產品4 | 1,7,8,9 |
| 2  | 產品2 | 2,4,7   |
| 3  | 產品3 | 3,4     |
+----+-------+---------+

巧妙利用 FIND_IN_SET 返回位置作排序鍵，避免 CASE WHEN 或 FIELD() 寫法。

性能對比與選型建議

最佳實踐：重構才是正道

逗號分隔字段違反第一範式，**查詢、索引、統計、維護皆痛點**。建議：

1. 關聯表：product_tag(product_id, tag_id)，外鍵約束、索引加速、JOIN 查詢。
2. JSON 類型（MySQL 5.7+）：tags JSON，JSON_CONTAINS(tags, '3') 精確匹配，支援 GIN 索引（8.0+）。
3. 若必須保留：僅用於「極少查詢、極小表、唯讀日誌表」，且統一用 FIND_IN_SET 保證語義正確。

總結

FIND_IN_SET 是精確集合成員檢測的正解，REGEXP 適合模糊/複雜模式。切記：33 不等於 3，不要用 REGEXP '(3|9)' 查集合。長期看，重構為關聯表或 JSON 才能獲得索引加速、數據完整性與可維護性。