FIND_IN_SET() vs IN()

我的数据库中有两个表，一个是订单表，一个是公司表。

命令有这样的结构:

OrderID     |     attachedCompanyIDs
------------------------------------
1                     1,2,3
2                     2,4

公司有这样的结构:

CompanyID      |        name
--------------------------------------
1                 Company 1
2                 Another Company
3                 StackOverflow
4                 Nothing

要获得订单的公司名称，我可以这样查询:

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

该查询可以正常工作，但下面的查询不能。

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

为什么第一个查询有效，而第二个查询无效？

第一个查询返回:

name
---------------
Company 1
Another Company
StackOverflow

第二个查询只返回:

name
---------------
Company 1

为什么，为什么第一个查询返回所有公司，而第二个查询只返回第一个？

247102 次浏览
小开

因为第二个查询查找 id 为1或2或3的行, 第一个查询查找 companyID 中存在的逗号分隔值之一,

这里的另一个问题是，您没有在 where 中的公共键上连接表，因此将得到一个行的变异，即 = count (table1) * count (table2) ;

你的问题确实存在于我的答案的第二部分

小开

AttachedCompanyID 是一个很大的字符串，因此 mysql 尝试在这里查找 company，它的强制转换为整数

当你使用 where 时

所以如果 comapnyid = 1:

companyID IN ('1,2,3')

这是返回真

但是如果数字1不是首要的

 companyID IN ('2,3,1')

它的回报是假的

小开
最佳答案 
SELECT  name
FROM    orders,company
WHERE   orderID = 1
AND companyID IN (attachedCompanyIDs)

attachedCompanyIDs是一个转换为 INT(companyID的类型)的标量值。

强制转换只返回第一个非数字的数字(在您的情况下是逗号)。

因此,

companyID IN ('1,2,3') ≡ companyID IN (CAST('1,2,3' AS INT)) ≡ companyID IN (1)

在 PostgreSQL中，您可以将字符串强制转换为数组(或者首先将其存储为数组) :

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

这甚至会使用 companyID上的索引。

不幸的是，这在 MySQL中不起作用，因为后者不支持数组。

你可能会觉得这篇文章很有趣(见 #2) :

更新:

如果逗号分隔的列表中的值数量有一些合理的限制(比如，不超过 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)
小开

获取所有相关公司的名称，而不是基于特定的 ID。

SELECT
(SELECT GROUP_CONCAT(cmp.cmpny_name)
FROM company cmp
WHERE FIND_IN_SET(cmp.CompanyID, odr.attachedCompanyIDs)
) AS COMPANIES
FROM orders odr

提交答案