萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

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

業務場景

表 test 的 pnum 字段存儲逗號分隔的 ID 列表(如 '2,4,7'),需查詢包含特定 ID(如 3 或 9)的記錄。

測試數據

CREATE TABLE test (pname VARCHAR(20), pnum VARCHAR(50));
INSERT INTO test VALUES 
('產品1', '1,2,4'),
('產品2', '2,4,7'),
('產品3', '3,4'),
('產品4', '1,7,8,9'),
('產品5', '33,4');

方案一:FIND_IN_SET(精確匹配,推薦)

SELECT * FROM test 
WHERE FIND_IN_SET('3', pnum) OR FIND_IN_SET('9', pnum);

原理

  • FIND_IN_SET(str, strlist) 返回 str 在 strlist 中的位置(1-based),不存在返回 0。
  • 內部按 ',' 分割 strlist,**精確匹配整個子串**,不會誤匹配 33 包含 3。

驗證

SELECT FIND_IN_SET('e', 'h,e,l,l,o'); -- 2
SELECT FIND_IN_SET('3', '33,4');      -- 0(精確匹配,非前綴)

方案二:REGEXP 正則表達式(靈活但需謹慎)

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

風險

REGEXP '(3|9)' 會匹配 '33,4'(因包含 3),導致**誤命中**。若需精確邊界,需寫邊界正則:

-- 精確匹配獨立數字(邊界為逗號或首尾)
SELECT * FROM test WHERE pnum REGEXP '(^|,)3(,|$)';

性能對比與索引建議

方案索引支持精確度適用場景
FIND_IN_SET不走索引(全表掃描)精確中小表、精確匹配
REGEXP不走索引(全表掃描)依賴正則寫法複雜模式匹配
JSON 字段 (MySQL 5.7+)可建虛擬列索引精確新架構、高性能需求
關聯表 (規範化)支持 B+ 樹索引精確核心業務、高併發

進階技巧:按指定 ID 順序排序

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

利用 FIND_IN_SET 返回位置實現自定義排序。

架構建議

反範式設計(逗號分隔字符串)僅適合: 非核心業務、數據量小、查詢頻率低、無關聯完整性要求。

核心業務請採用規範化設計:

CREATE TABLE product_tag (
    product_id INT NOT NULL,
    tag_id INT NOT NULL,
    PRIMARY KEY (product_id, tag_id)
) ENGINE=InnoDB;

-- 查詢包含 tag 3 或 9 的產品
SELECT DISTINCT p.* FROM product p
JOIN product_tag pt ON p.id = pt.product_id
WHERE pt.tag_id IN (3, 9);

總結

  • 存量逗號字段查詢:**首選 FIND_IN_SET**,精確且語義清晰。
  • 複雜模式匹配才用 REGEXP,注意邊界條件。
  • 新系統設計**拒絕逗號分隔字符串**,採用關聯表或 JSON 類型(MySQL 5.7+)。
copyright © 萬盛學電腦網 all rights reserved