問題描述

我的數(shù)據(jù)庫中有 2 個(gè)表.一種用于訂單，一種用于公司.

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

訂單具有以下結(jié)構(gòu):

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

公司有這樣的結(jié)構(gòu):

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

要獲取訂單的公司名稱，我可以執(zhí)行以下查詢:

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)

為什么第一個(gè)查詢有效，而第二個(gè)無效?

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

第一個(gè)查詢返回:

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

第二個(gè)查詢只返回:

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

這是為什么，為什么第一個(gè)查詢返回所有公司，而第二個(gè)查詢只返回第一個(gè)?

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 是一個(gè)標(biāo)量值，它被轉(zhuǎn)換為 INT(companyID 的類型).

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

演員表只返回直到第一個(gè)非數(shù)字的數(shù)字(在你的情況下是逗號(hào)).

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中，您可以將字符串轉(zhuǎn)換為數(shù)組(或首先將其存儲(chǔ)為數(shù)組):

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

這甚至?xí)褂?companyID 上的索引.

and this would even use an index on companyID.

不幸的是，這在 MySQL 中不起作用，因?yàn)楹笳卟恢С謹(jǐn)?shù)組.

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

您可能會(huì)發(fā)現(xiàn)這篇文章很有趣(請(qǐng)參閱#2):

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

• MySQL 中的 10 件事(不會(huì)按預(yù)期工作)

更新:

如果逗號(hào)分隔列表中的值數(shù)量有一些合理的限制(比如不超過5)，那么你可以嘗試使用這個(gè)查詢:

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)

這篇關(guān)于FIND_IN_SET() 與 IN()的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！