表 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');
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(精確匹配,非前綴)
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+ 樹索引 | 精確 | 核心業務、高併發 |
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,注意邊界條件。