MySQL查询问题：逗号分隔factory_id关联查询结果不全

你的SQL查询仅返回factory_id=1的记录，是因为wla_user表中factory_id存储的是字符串'1,2'，子查询返回的是单个字符串值，此时IN条件实际是判断wla_container.factory_id IN ('1,2')，而非预期的IN (1,2)。如果wla_container的factory_id是数值类型，数据库会将字符串'1,2'隐式转换为数值1，因此仅匹配到factory_id=1的记录。

临时解决（无需修改表结构） #

根据你使用的数据库，使用字符串拆分相关函数来匹配：

MySQL/MariaDB #

使用FIND_IN_SET函数：

SELECT id, contNUm, contStatus, factory_id 
FROM wla_container 
WHERE FIND_IN_SET(factory_id, (SELECT factory_id FROM wla_user WHERE email = 'halimah@gmail.com')) > 0

PostgreSQL #

使用string_to_array结合ANY：

SELECT id, contNUm, contStatus, factory_id 
FROM wla_container 
WHERE factory_id = ANY (string_to_array((SELECT factory_id FROM wla_user WHERE email = 'halimah@gmail.com'), ','))::int[]

SQL Server #

使用STRING_SPLIT函数：

SELECT c.id, c.contNUm, c.contStatus, c.factory_id 
FROM wla_container c
JOIN (
    SELECT value AS factory_id 
    FROM STRING_SPLIT((SELECT factory_id FROM wla_user WHERE email = 'halimah@gmail.com'), ',')
) u_fact ON c.factory_id = u_fact.factory_id

长期规范方案（推荐） #

逗号分隔存储关联ID不符合数据库设计范式，建议创建中间关联表来维护用户与工厂的多对多关系：

1. 创建中间表：

CREATE TABLE wla_user_factory (
    user_id INT NOT NULL,
    factory_id INT NOT NULL,
    PRIMARY KEY (user_id, factory_id),
    FOREIGN KEY (user_id) REFERENCES wla_user(id),
    FOREIGN KEY (factory_id) REFERENCES wla_container(factory_id)
);

2. 插入关联数据：

INSERT INTO wla_user_factory (user_id, factory_id) VALUES (1, 1), (1, 2);

3. 使用关联查询获取结果：

SELECT c.id, c.contNUm, c.contStatus, c.factory_id 
FROM wla_container c
JOIN wla_user_factory uf ON c.factory_id = uf.factory_id
JOIN wla_user u ON uf.user_id = u.id
WHERE u.email = 'halimah@gmail.com';

这种方案不仅能解决当前问题，还能提升查询性能、简化后续维护，避免因字符串拆分带来的潜在问题。

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