如何使用数组查询MySQL中的set数据类型？

问题核心 #

你遇到的问题是FIND_IN_SET()不支持传入多元素作为第一个参数，以下分场景给出最优实现方案：

场景1：单元素数组（如['a']） #

不管role字段是MySQL原生SET类型，还是逗号分隔的字符串，直接用FIND_IN_SET()即可：

SELECT * FROM your_table WHERE FIND_IN_SET('a', role) > 0;

执行后会返回包含'a'的所有记录（对应示例中的id=1和id=3）。

场景2：多元素数组（如['a','b']，需同时包含所有元素） #

方案1：多FIND_IN_SET()结合AND（兼容所有MySQL版本）

对数组中的每个元素单独调用FIND_IN_SET()，用AND连接确保所有元素都存在：

SELECT * FROM your_table 
WHERE FIND_IN_SET('a', role) > 0 
  AND FIND_IN_SET('b', role) > 0;

执行后仅返回同时包含'a'和'b'的记录（对应示例中的id=3）。

如果是动态数组（比如PHP中的$role变量），可以通过预处理语句安全生成SQL：

// PHP示例：动态生成安全查询
$roles = ['a', 'b'];
$conn = new mysqli('localhost', 'user', 'password', 'database');

// 生成占位符条件
$conditions = array_fill(0, count($roles), 'FIND_IN_SET(?, role) > 0');
$sql = "SELECT * FROM your_table WHERE " . implode(' AND ', $conditions);

// 绑定参数并执行
$stmt = $conn->prepare($sql);
$stmt->bind_param(str_repeat('s', count($roles)), ...$roles);
$stmt->execute();
$result = $stmt->get_result();

方案2：位运算（仅适用于MySQL原生SET类型，效率更高）

如果role是MySQL定义的SET('a','b','c')类型，每个元素对应一个二进制位（a=1，b=2，c=4），可以用位运算直接判断：

-- 同时包含a和b，位掩码为1|2=3
SELECT * FROM your_table WHERE (role & 3) = 3;

这种方法比多次调用FIND_IN_SET()效率更高，适合大数据量场景。

方案3：JSON_TABLE分组校验（MySQL 8.0+适用）

如果你的MySQL版本是8.0及以上，可用JSON_TABLE将数组转为临时表，通过分组计数判断是否包含所有元素：

SELECT t.*
FROM your_table t
JOIN JSON_TABLE('["a","b"]', '$[*]' COLUMNS(role_val VARCHAR(10) PATH '$')) j
ON FIND_IN_SET(j.role_val, t.role) > 0
GROUP BY t.id, t.role
HAVING COUNT(DISTINCT j.role_val) = 2; -- 2为数组元素总数

这种方法无需循环拼接SQL，适合元素数量不固定的动态场景。

原写法无效原因 #

FIND_IN_SET(str, strlist)的第一个参数只能是单个字符串，无法传入多元素集合或数组，因此FIND_IN_SET(('a','b'),role)会被解析为错误参数，导致执行失败。

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