如何编写SQL查询匹配MySQL逗号分隔值列与PHP数组元素

问题原因 #

你当前使用的IN运算符是做完全等值匹配的，只有当items字段的整体值和IN列表中的某个值完全一致时才会返回对应行，因此只能命中items值刚好为item5的第4行。

解决方案（MySQL环境） #

使用MySQL内置的FIND_IN_SET函数实现逗号分隔值的包含匹配，该函数会专门识别逗号分隔的字符串，判断指定值是否在其中存在。

实现代码 #

基础版本

$ItemsOfInterest = ["item1", "item99", "item5"];
// 为每个待匹配元素构造FIND_IN_SET查询条件
$conditions = array_map(function($item) {
    // 简单转义避免SQL注入，生产环境建议用预处理
    return "FIND_IN_SET('".addslashes($item)."', `items`)";
}, $ItemsOfInterest);
// 用OR拼接所有条件，满足任意一个即可命中
$whereClause = implode(' OR ', $conditions);
$query = "SELECT * FROM table_name WHERE ".$whereClause;

最终生成的SQL语句如下，会匹配到你示例中的全部5行数据：

SELECT * FROM table_name 
WHERE FIND_IN_SET('item1', `items`) 
OR FIND_IN_SET('item99', `items`) 
OR FIND_IN_SET('item5', `items`)

安全版本（PDO预处理，推荐生产环境使用）

完全避免SQL注入风险：

$pdo = new PDO('mysql:host=你的主机;dbname=你的库名;charset=utf8mb4', '用户名', '密码');
$ItemsOfInterest = ["item1", "item99", "item5"];
// 生成对应数量的占位符
$placeholders = implode(' OR ', array_fill(0, count($ItemsOfInterest), 'FIND_IN_SET(?, `items`)'));
$stmt = $pdo->prepare("SELECT * FROM table_name WHERE $placeholders");
$stmt->execute($ItemsOfInterest);
$matchedRows = $stmt->fetchAll(PDO::FETCH_ASSOC);

优化建议 #

将逗号分隔值存在单一字段的设计不符合数据库第一范式，数据量较大时查询性能会很低，有条件的话建议拆分为独立的关联表，用一对多的关系存储每个条目对应的item值，后续查询、统计、修改都会更灵活高效。

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