問題描述
我的數(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)!