SELECT name
FROM orders,company
WHERE orderID = 1
AND companyID IN (attachedCompanyIDs)
attachedCompanyIDs
是一个标量值,被转换为INT
(类型companyID
).
强制转换仅返回第一个非数字之前的数字(在您的情况下为逗号)。
Thus,
companyID IN ('1,2,3') ≡ companyID IN (CAST('1,2,3' AS INT)) ≡ companyID IN (1)
In PostgreSQL
,您可以将字符串转换为数组(或者首先将其存储为数组):
SELECT name
FROM orders
JOIN company
ON companyID = ANY (('{' | attachedCompanyIDs | '}')::INT[])
WHERE orderID = 1
这甚至会使用索引companyID
.
不幸的是,这不适用于MySQL
因为后者不支持数组。
您可能会发现这篇文章很有趣(请参阅#2
):
- MySQL 中的 10 件事(不会按预期工作) http://explainextended.com/2010/11/03/10-things-in-mysql-that-wont-work-as-expected/
Update:
如果逗号分隔列表中的值的数量有一些合理的限制(例如,不超过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)