FIND_IN_SET() 与 IN()

2021-11-20 00:00:00 mysql

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

I have 2 tables in my database. One is for orders, and one is for companies.

订单具有以下结构:

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

公司有这样的结构:

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

要获取订单的公司名称，我可以执行以下查询:

To get an order's companies names, I can do a query as such:

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

该查询工作正常，但以下查询无效.

That query works fine, but the following query does not.

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

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

Why does the first query work but not the second one?

第一个查询返回:

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

第二个查询只返回:

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

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

Why is this, why does the first query return all the companies, but the second query only returns the first one?

推荐答案

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

attachedCompanyIDs 是一个标量值，它被转换为 INT(companyID 的类型).

attachedCompanyIDs is a scalar value which is cast into INT (type of companyID).

演员表只返回直到第一个非数字的数字(在你的情况下是逗号).

The cast only returns numbers up to the first non-digit (a comma in your case).

因此，

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

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

In PostgreSQL, you could cast the string into array (or store it as an array in the first place):

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

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

and this would even use an index on companyID.

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

Unfortunately, this does not work in MySQL since the latter does not support arrays.

您可能会发现这篇文章很有趣(请参阅#2):

You may find this article interesting (see #2):

  • MySQL 中的 10 件事(不会按预期工作)

更新:

如果逗号分隔列表中的值数量有一些合理的限制(比如不超过5)，那么你可以尝试使用这个查询:

If there is some reasonable limit on the number of values in the comma separated lists (say, no more than 5), so you can try to use this query:

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)

相关文章