MySQL如何用逗号分隔字符串筛选逗号分隔列的记录？

需求说明 #

现有一张存储用户信息的表，其中Attribute IDs列以逗号分隔存储多个属性ID，需要筛选出同时包含指定所有属性ID（比如示例中的3和4）的记录。

示例数据 #

期望结果 #

MySQL直接实现方法 #

方法1：使用FIND_IN_SET函数 #

FIND_IN_SET可以检测某个值是否在逗号分隔的字符串中，要同时匹配多个ID，只需用AND连接多个条件：

SELECT *
FROM your_table_name
WHERE FIND_IN_SET('3', `Attribute IDs`) > 0
  AND FIND_IN_SET('4', `Attribute IDs`) > 0;

注意：如果列名包含空格，必须用反引号`包裹。

方法2：使用正则表达式（REGEXP） #

用正则可以更精准匹配完整的属性ID，避免出现类似13被误判为包含3的情况：

SELECT *
FROM your_table_name
WHERE `Attribute IDs` REGEXP '(^|,)3(,|$)'
  AND `Attribute IDs` REGEXP '(^|,)4(,|$)';

正则表达式解释：(^|,)匹配字符串开头或逗号，3是目标ID，(,|$)匹配逗号或字符串结尾，确保匹配的是完整的单个ID。

动态筛选字符串的处理（比如传入"3,4"这类参数） #

如果筛选的ID列表是动态传入的字符串，不能硬写条件，可以通过以下步骤处理：

1. 将筛选字符串拆分成单个ID的临时表；
2. 关联原表，用FIND_IN_SET匹配每个ID；
3. 按用户ID分组，确保匹配的ID数量等于筛选的ID总数。

示例代码（假设筛选字符串为@filter_ids）：

-- 创建临时表存储拆分后的ID
CREATE TEMPORARY TABLE temp_attr_ids (attr_id INT);

-- 拆分字符串并插入临时表（这里以"3,4"为例，实际可替换为变量）
INSERT INTO temp_attr_ids (attr_id)
VALUES (3), (4);

-- 查询符合条件的记录
SELECT t.*
FROM your_table_name t
JOIN temp_attr_ids ta ON FIND_IN_SET(ta.attr_id, t.`Attribute IDs`) > 0
GROUP BY t.ID
HAVING COUNT(DISTINCT ta.attr_id) = (SELECT COUNT(*) FROM temp_attr_ids);

-- 清理临时表
DROP TEMPORARY TABLE temp_attr_ids;

替代方案（推荐）：优化表结构 #

存储逗号分隔值违反数据库设计的第一范式，会导致查询效率低、难以维护，长期来看最好的方案是拆分表结构：

1. 创建用户属性关联表user_attributes，结构如下：

CREATE TABLE user_attributes (
    user_id INT,
    attribute_id INT,
    PRIMARY KEY (user_id, attribute_id),
    FOREIGN KEY (user_id) REFERENCES your_table_name(ID)
);

2. 将原表中Attribute IDs的逗号分隔值拆分成多行插入到user_attributes表中，比如Jenny的记录对应三行：(2,2)、(2,3)、(2,4)；
3. 筛选时只需关联两张表，分组后验证匹配数量：

SELECT t.*
FROM your_table_name t
JOIN user_attributes ua ON t.ID = ua.user_id
WHERE ua.attribute_id IN (3,4)
GROUP BY t.ID
HAVING COUNT(DISTINCT ua.attribute_id) = 2;

这种方法不仅查询效率更高，还支持更复杂的属性筛选逻辑，也符合数据库设计的最佳实践。

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