FIND_IN_SET() 与 IN()：何时应将每个值用于逗号分隔值？

FIND_IN_SET() 与 IN()：揭开查询难题

在数据库查询领域，了解不同函数之间的细微差别是至关重要的。在比较 FIND_IN_SET() 和 IN() 时，尤其是在查询存储为逗号分隔字符串的值时，就会出现这样的区别。

查询差异

考虑两个表： “orders”的列“attachedCompanyIDs”包含逗号分隔的公司标识符，“company”的列包含“companyID”列和相应的公司名称。以下查询使用 FIND_IN_SET() 有效检索与订单关联的公司名称：

SELECT name
FROM orders, company
WHERE orderID = 1
AND FIND_IN_SET(companyID, attachedCompanyIDs)

但是，使用 IN() 的类似查询会产生意外结果：

SELECT name
FROM orders, company
WHERE orderID = 1
AND companyID IN (attachedCompanyIDs)

隐藏的陷阱

根本原因在于MySQL如何处理将 AttachedCompanyID 转换为整数时的逗号分隔值。转换会截断第一个非数字处的数字，从而有效地将字符串减少为第一个逗号分隔值。

例如，将attachedCompanyIDs 设置为“1,2,3”，IN() 查询会错误变成：

companyID IN (1)

这解释了为什么 IN() 查询只返回第一个公司名称，而 FIND_IN_SET() 返回所有公司名称三.

克服限制

要解决此问题，请考虑适当处理逗号分隔字符串的替代方法。在 PostgreSQL 中，可以使用数组：

SELECT name
FROM orders
JOIN company
ON companyID = ANY(('{' || attachedCompanyIDs || '}')::INT[])
WHERE orderID = 1

不幸的是，MySQL 中不支持数组。对于有限数量的值（例如少于 5 个），可以使用交叉连接和字符串操作来解决：

SELECT name
FROM orders
CROSS JOIN
(
SELECT 1 AS pos
UNION ALL
SELECT 2 AS pos
UNION ALL
SELECT 3 AS pos
UNION ALL
SELECT 4 AS pos
UNION ALL
SELECT 5 AS pos
) q
JOIN company
ON companyID = CAST(NULLIF(SUBSTRING_INDEX(attachedCompanyIDs, ',', -pos), SUBSTRING_INDEX(attachedCompanyIDs, ',', 1 - pos)) AS UNSIGNED)

以上是FIND_IN_SET() 与 IN()：何时应将每个值用于逗号分隔值？的详细内容。更多信息请关注PHP中文网其他相关文章！