MySQL中高效筛选含指定值的逗号分隔字段数据方案

一、利用全文索引快速匹配（适合快速优化，无需重构数据） #

如果你的MySQL版本支持全文索引，可通过以下步骤实现高效查询：

1. 给some_ids字段创建全文索引：

CREATE FULLTEXT INDEX idx_ft_some_ids ON original_table(some_ids);

2. 调整全文检索的分隔符，让MySQL将逗号识别为单词分隔符（临时修改参数）：

SET GLOBAL ft_boolean_syntax = '+ -><()~*:""&|,';

3. 使用布尔全文检索查询包含指定ID的记录：

SELECT * FROM original_table WHERE MATCH(some_ids) AGAINST('+"2"' IN BOOLEAN MODE);

该方式可利用全文索引，效率远高于FIND_IN_SET()，适合数据量较大的场景。

二、重构数据结构（长期最优方案） #

逗号分隔存储多值本身违反数据库设计范式，是性能问题的根源。最优方案是拆分数据到关联表：

1. 新建关联表，关联原表主键与单个some_id：

CREATE TABLE user_some_id_map (
    name VARCHAR(50) NOT NULL,
    some_id INT NOT NULL,
    PRIMARY KEY (name, some_id),
    INDEX idx_some_id (some_id)
);

2. 将原表中逗号分隔的ID拆分插入到关联表：

INSERT INTO user_some_id_map(name, some_id)
SELECT name, SUBSTRING_INDEX(SUBSTRING_INDEX(some_ids, ',', n), ',', -1) AS some_id
FROM original_table
JOIN (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) numbers
WHERE n <= LENGTH(some_ids) - LENGTH(REPLACE(some_ids, ',', '')) + 1;

3. 查询时通过关联表快速筛选：

SELECT t.* FROM original_table t
JOIN user_some_id_map m ON t.name = m.name
WHERE m.some_id = 2;

该方式完全利用some_id上的索引，性能最佳，同时符合数据库设计规范，便于后续维护扩展。

三、使用虚拟列+B-tree索引（MySQL 8.0+） #

若无法重构表且使用MySQL 8.0及以上版本，可创建带格式的虚拟列并建立索引：

1. 添加存储型虚拟列，给some_ids前后加逗号，确保每个ID被逗号包裹：

ALTER TABLE original_table ADD COLUMN some_ids_wrapped VARCHAR(255) GENERATED ALWAYS AS (CONCAT(',', some_ids, ',')) STORED;

2. 给虚拟列建立B-tree索引：

CREATE INDEX idx_some_ids_wrapped ON original_table(some_ids_wrapped);

3. 查询时匹配包裹后的字符串：

SELECT * FROM original_table WHERE some_ids_wrapped LIKE '%,2,%';

注意：该方式的索引利用效率取决于字符串长度，性能弱于前两种方案，但优于FIND_IN_SET()。

内容的提问来源于stack exchange，提问作者Bernhard Kraus