FIND_IN_SET（）与IN（）

一尘不染

FIND_IN_SET（）与IN（）

mysql

我的数据库中有2个表。一种是用于订单，一种是用于公司。

订单具有以下结构：

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

为什么会这样，为什么第一个查询返回所有公司，而第二个查询仅返回第一个公司？

阅读 826

2020-05-17

共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）：

MySQL中的10件事（无法按预期工作）

更新：

如果以逗号分隔的列表中的值数量有一定的合理限制（例如，不超过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)

2020-05-17