订单具有以下结构:
OrderID | attachedCompanyIDs
------------------------------------
1 1,2,3
2 2,4
公司具有以下结构:
CompanyID | name
--------------------------------------
1 Company 1
2 Another Company
3 StackOverflow
4 Nothing
要获得订单的公司名称,我可以这样查询:
SELECT name FROM orders,company
WHERE orderID = 1 AND FIND_IN_SET(companyID, attachedCompanyIDs)
该查询工作正常,但以下查询无效。
SELECT name FROM orders,company
WHERE orderID = 1 AND companyID IN (attachedCompanyIDs)
为什么第一个查询有效但第二个查询不起作用?
name
---------------
Company 1
Another Company
StackOverflow
第二个查询仅返回:
name
---------------
Company 1
为什么,为什么第一个查询返回所有公司,但是第二个查询只返回第一个?
#1 楼
SELECT name
FROM orders,company
WHERE orderID = 1
AND companyID IN (attachedCompanyIDs)
attachedCompanyIDs
是一个标量值,已强制转换为INT
(companyID
的类型)。强制转换仅返回直到第一个非数字的数字(您的逗号case)。
因此,
companyID IN ('1,2,3') ≡ companyID IN (CAST('1,2,3' AS INT)) ≡ companyID IN (1)
在
PostgreSQL
中,您可以将字符串转换为数组(或将其存储为第一个数组)位置):SELECT name
FROM orders
JOIN company
ON companyID = ANY (('{' | attachedCompanyIDs | '}')::INT[])
WHERE orderID = 1
甚至会在
companyID
上使用索引。不幸的是,这在
MySQL
中不起作用,因为后者不支持数组。您可能会发现本文很有趣(请参阅
#2
):MySQL中的10件事(无法按预期工作)
更新:
如果用逗号分隔的列表中的值数量有合理的限制(例如,不超过
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)
评论
感谢您的解释。我没有意识到AttachedCompanyIDs字段已强制转换为INT。在MySQL中,有什么办法可以解决? FIND_IN_SET有效,但不使用索引,并且在Company表中包含大量信息时可能会变慢。
–火箭危险品
2010年11月11日15:43
你能解释一下这个更新吗?这样做到底是做什么的,因为它似乎有效。
–火箭危险品
2010年11月11日在16:16
@Rocket:它从CVS的开头剥离pos项目,并将其余的项目转换为整数。
– Quassnoi
2010年11月11日在16:20
赞成(y)MySQL中的10件事(无法按预期工作)
– NullPointer
2014-2-28在6:12
@Quassnoi,为什么要写CROSS JOIN?它们在MySQL中不是都一样吗?
–起搏器
2015年4月18日在7:08
#2 楼
AttachedCompanyIDs是一个大字符串,因此mysql尝试在此公司中将其强制转换为整数,当您在其中使用where in
时,如果comapnyid = 1:
companyID IN ('1,2,3')
返回true
,但是如果数字1不在首位
companyID IN ('2,3,1')
/>它返回false
#3 楼
要获取所有相关的公司名称,而不是基于特定的ID。SELECT
(SELECT GROUP_CONCAT(cmp.cmpny_name)
FROM company cmp
WHERE FIND_IN_SET(cmp.CompanyID, odr.attachedCompanyIDs)
) AS COMPANIES
FROM orders odr
#4 楼
因为第二个查询正在寻找ID为1或2或3的行,第一个查询正在寻找一个以逗号分隔的值存在于companyID中,
和另一个问题这是您没有将表连接到您所在位置的公共键上,因此您将获得行的突变,即= count(table1)* count(table2);
您的问题确实存在与我的答案的第二部分。 (与第二个查询一起)
评论
两个表中的行都比我显示的多。在这两个表中,都以您登录时所使用的用户的ID身份存在,是否可以加入该帮助?
–火箭危险品
2010年11月11日15:30
好吧,如果您的第一个查询未返回预期结果,则只需更改任何内容。如果第一个查询返回的是您想要的结果,那么实际上没有问题。我以为您很好奇为什么2个显示的结果不一样。
–超速
2010年11月11日15:34
@superfro,我很好奇为什么2个没有显示相同的结果。
–火箭危险品
2010年11月11日15:39
您的第二个查询使用的是where IN(值),其中“值”部分来自表及其字符串。该字符串被评估为布尔值true,其中= 1,这就是为什么它仅显示第一行的原因。
–超速
2010年11月11日15:43
如果您担心性能,则可能应该考虑更改数据库结构。您可以添加一个包含2个值的联合表order_ID和company_ID,而不是在订单表中使用逗号分隔列表。这样,您就可以从company.company_ID = order_companies.company_ID上的左公司order_order.order_ID = order.order_ID上左公司的左联接订单中选择名称。这将使用索引。
–超速
2010年11月11日在16:05
#5 楼
让我解释一下何时使用FIND_IN_SET和何时使用IN。让我们的Take表A的列名为“ aid”,“ aname”。 bid”,“ bname”,“ aids”。
现在表A和表B中有伪值,如下所示。
表A
援助姓名
1苹果
2香蕉
3芒果
表B
出价bname辅助工具
1苹果1,2
2香蕉2,1
3芒果3,1,2
<案例1:如果要从表b中获取那些在辅助列中存在1值的记录,则必须使用FIND_IN_SET。
查询:选择*从A JOIN B ON FIND_IN_SET(A.aid,b.aids)中,其中A.aid = 1;
案例2:如果要从具有1或2或3的表a中获取这些记录
查询:从A JOIN B ON A.aid IN(b.aids)中选择*;
现在到这里你那个通过mysql查询需要什么。
评论
这个问题已经解决了。另外,我不认为您的第二个示例(使用IN)可以正常工作……这基本上是我一开始要解决的问题。
–火箭危险品
17年9月15日在17:37
#6 楼
SELECT o.*, GROUP_CONCAT(c.name) FROM Orders AS o , Company.c
WHERE FIND_IN_SET(c.CompanyID , o.attachedCompanyIDs) GROUP BY o.attachedCompanyIDs
评论
欢迎来到SO!没有解释的代码很少有帮助。在这种情况下,它甚至不会尝试回答“为什么……?”的问题。另外请注意,这个特定问题已经有一个可以接受的答案,给出了好评(> 80票!)的答案。作为新用户,最好专注于未回答的问题和/或自己问好问题。
–cfi
16-10-21在7:13
评论
AttachedCompanyIDs是一个大字符串,因此mysql尝试在此方法中将公司转换为整数我认为这是最好的示例mysqltutorial.org/mysql-find_in_set