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

订单具有以下结构:

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


为什么,为什么第一个查询返回所有公司,但是第二个查询只返回第一个?

评论

AttachedCompanyIDs是一个大字符串,因此mysql尝试在此方法中将公司转换为整数

我认为这是最好的示例mysqltutorial.org/mysql-find_in_set

#1 楼

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


attachedCompanyIDs是一个标量值,已强制转换为INTcompanyID的类型)。

强制转换仅返回直到第一个非数字的数字(您的逗号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