MySQL中IN()函数匹配表字段列表值失效问题求助

你的问题出在对IN操作符的误用，以及数据库隐式类型转换带来的巧合结果：

1. 为什么3 in (ids)能返回结果？
   当你写3 in (ids)时，(ids)并不是将逗号分隔的字符串拆分为多个值的集合，而是把ids字段的单个字符串值作为IN的唯一匹配项。在你使用的数据库（大概率是MySQL）中，数字与字符串比较会触发隐式类型转换：字符串'3,4'会被转换为数字3（遇到非数字的逗号时停止转换），此时3 = '3,4'的比较结果为真，所以这条记录被匹配到。

2. 为什么4 in (ids)返回空？
   遵循同样的隐式转换逻辑，字符串'3,4'转换为数字后是3，4 = 3显然不成立，因此没有匹配结果。

正确的查询写法 #

要匹配逗号分隔字符串中的值，不能直接用IN，需要使用数据库提供的专门函数：

• MySQL/MariaDB 场景：

  SELECT id 
  FROM packets 
  WHERE cust='58267' AND FIND_IN_SET('4', ids) > 0 AND now() <= expire 
  ORDER BY id DESC
  LIMIT 1;
  

• PostgreSQL 场景：

  SELECT id 
  FROM packets 
  WHERE cust='58267' AND '4' = ANY(STRING_TO_ARRAY(ids, ',')) AND now() <= expire 
  ORDER BY id DESC
  LIMIT 1;
  

• SQL Server 场景：

  SELECT id 
  FROM packets 
  WHERE cust='58267' AND EXISTS (
      SELECT 1 FROM STRING_SPLIT(ids, ',') WHERE value = '4'
  ) AND GETDATE() <= expire 
  ORDER BY id DESC
  OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;
  

数据库设计建议 #

从规范角度，不建议用逗号分隔字符串存储多值数据，这违反了关系型数据库的第一范式（1NF），会导致查询效率低下、数据维护困难等问题。更合理的方案是创建关联表（比如packet_ids），记录packet_id与对应单个id的关联关系，通过标准的JOIN查询实现需求，性能和可维护性都会大幅提升。

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