MySQL FIND_IN_SET() 与 IN()

FIND_IN_SET() vs IN()

提问by Rocket Hazmat

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

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

Orders has this structure:

订单具有以下结构：

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

And Company has this structure:

公司有这样的结构：

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?

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

The first query returns:

第一个查询返回：

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

The second query only returns:

第二个查询只返回：

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

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

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

采纳答案by Quassnoi

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

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

attachedCompanyIDs是一个标量值，它被转换为INT（类型companyID）。

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

演员表只返回直到第一个非数字的数字（在你的情况下是逗号）。

Thus,

因此，

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

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

在 中PostgreSQL，您可以将字符串转换为数组（或首先将其存储为数组）：

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

and this would even use an index on companyID.

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

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

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

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

您可能会发现这篇文章很有趣（请参阅 参考资料#2）：

• 10 things in MySQL (that won't work as expected)

• MySQL 中的 10 件事（不会按预期工作）

Update:

更新：

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:

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

回答by Haim Evgi

attachedCompanyIDs is one big string, so mysql try to find company in this its cast to integer

attachedCompanyIDs 是一个大字符串，因此 mysql 尝试在其中查找公司，将其转换为整数

when you use where in

当你使用 where in

so if comapnyid = 1 :

所以如果comapnyid = 1：

companyID IN ('1,2,3')

this is return true

这是返回真

but if the number 1 is not in the first place

但如果数字 1 不在首位

 companyID IN ('2,3,1')

its return false

它的返回假

回答by Anupriya Pundir

To get the all related companies name, not based on particular Id.

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

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

回答by superfro

because the second query is looking for rows with the id's 1 OR 2 OR 3, the first query is looking for a one of the comma delimited values to exist in companyID,

因为第二个查询正在寻找 ID 为 1 OR 2 OR 3 的行，所以第一个查询正在寻找一个存在于 companyID 中的逗号分隔值，

and another problem here is you aren't joining the tables on a common key in your where so you are going to get a mutation of rows that = count(table1) * count(table2);

这里的另一个问题是您没有在您的 where 中加入公共键上的表，因此您将获得 = count(table1) * count(table2); 的行突变。

Your problem really exists with part 2 of my answer. (with your second query)

您的问题确实存在于我的答案的第 2 部分。（与您的第二个查询）

回答by prashant

Let me explain when to use FIND_IN_SET and When to use IN.

让我解释一下何时使用 FIND_IN_SET 和何时使用 IN。

Let's take table A which has columns named "aid","aname". Let's take table B which has columns named "bid","bname","aids".

让我们以具有名为“aid”、“aname”的列的表 A 为例。让我们以表 B 为例，它具有名为“bid”、“bname”、“aids”的列。

Now there are dummy values in Table A and Table B as below.

现在表 A 和表 B 中有虚拟值，如下所示。

Table A

表A

aid aname

帮助姓名

1 Apple

1 苹果

2 Banana

2 香蕉

3 Mango

3 芒果

Table B

表B

bid bname aids

投标 bname 辅助工具

1 Apple 1,2

1 苹果 1,2

2 Banana 2,1

2 香蕉 2,1

3 Mango 3,1,2

3 芒果 3,1,2

enter code here

Case1: if you want to get those records from table b which has 1 value present in aids columns then you have to use FIND_IN_SET.

案例 1：如果您想从表 b 中获取那些在辅助列中存在 1 个值的记录，那么您必须使用 FIND_IN_SET。

Query: select * from A JOIN B ON FIND_IN_SET(A.aid,b.aids) where A.aid = 1 ;

查询： select * from A JOIN B ON FIND_IN_SET(A.aid,b.aids) where A.aid = 1 ;

Case2: if you want to get those records from table a which has 1 OR 2 OR 3 value present in aid columns then you have to use IN.

案例 2：如果您想从表 a 中获取那些在辅助列中存在 1 OR 2 OR 3 值的记录，那么您必须使用 IN。

Query: select * from A JOIN B ON A.aid IN (b.aids);

查询：select * from A JOIN B ON A.aid IN (b.aids);

Now here upto you that what you needs through mysql query.

现在由您决定通过 mysql 查询需要什么。

回答by amit gangrade

SELECT o.*, GROUP_CONCAT(c.name) FROM Orders AS o , Company.c
    WHERE FIND_IN_SET(c.CompanyID , o.attachedCompanyIDs) GROUP BY o.attachedCompanyIDs