如何在MySQL中选择这些特定行？

Question

在MySQL表中，我有一个名为“Tag”的字段，它可能包含多个逗号分隔的值。

我想选择包含值“1”的Tag字段的行。

我该如何编写MySQL语句，以选择具有Tag值“1”和“1，Cars”的行，但排除具有Tag值“17”和“17，Cars”的行？

问题在于我使用了“LIKE”运算符，这导致所有四行都被选中。

谢谢。

- JMan

---

你为什么没有对你的模型进行规范化，有什么特别的原因吗？ - Jon Black

Answer 1

FIND_IN_SET函数可以用于查找逗号分隔的列表，例如：

SELECT FIND_IN_SET('b','a,b,c,d');

在这种情况下，大致可以表述为：

SELECT tag FROM table
WHERE FIND_IN_SET('1', tag) > 0

这个应该能解决问题。

编辑：实际上当没有匹配时返回0，因此NULL检查是错误的。

- bnaul

---

你真的需要在结尾处使用 IS NOT NULL 吗？ - gen_Eric

你其实不需要这样做，特别是因为它实际上并不返回NULL。感谢提醒。但我猜你的意思是可以使用WHERE FIND_IN_SET(...)吗？那也可以，只是我更喜欢更明确的条件。 - bnaul

如果这个速度很快，那么你的数据库很小 :-) 那就是一个好的解决方案。但要注意，在您的表变得更大时，对于函数在选择中的性能限制。 - paxdiablo

Answer 2

你可能需要做类似这样的事情

WHERE Tag LIKE "1,%"
OR Tag LIKE "%,1,%"
OR Tag LIKE "%,1"
OR Tag = "1"

这样应该就涵盖了所有选项。

你可能想要看看使用正则表达式。

- Adriaan Stander

Answer 3

如果你只想选择那些包含1的内容，可以使用以下代码：

where colm like '1,%'
   or colm like '%,1,%'
   or colm like '%,1'
   or colm = '1'

但你应该注意到这将是一个性能杀手。 如果你曾经发现自己需要操纵小于列的东西，那么你的数据库模式设置得很糟糕。上面查询不执行良好的原因是不可能使用索引快速定位满足查询条件的行。 它将需要完整的表格或索引扫描以获取行。

重新设计模式将适用于另一个表中的逗号分隔内容，这样会更好。

例如：

PrimaryTable:
    id          integer       primary key
    other_stuff varchar(250)
SecondaryTable:
    primary_id  integer       references PrimaryTable(id)
    int_val     integer
    char_val    varchar(20)
    primary key (primary_id,int_val)
    index       (int_val)

这将使您能够编写极快的查询，而不是您提出的慢速查询：

select p.id, p.other_stuff
from PrimaryTable p, SecondaryTable s
where p.id = s.primary_id
  and s.int_val = 1;

（或等效的显式连接语法）。

这个解决方案之所以更快，是因为它可以使用索引在SecondaryTable.int_val上快速检索相关行，并使用两个表的主键进行交叉匹配。

- paxdiablo

---

你忘记了处理标签列表只包含单个值的情况。请参考@astander的回答。 - Asaph

@Asaph：好的，我也会涉及到那个问题，但我的建议是真的不要那样做，因为那是一个次优的模式（尽可能委婉地说）。你最好完全绕过它，创建一个合适的模式。 - paxdiablo

绝对正确。我并不是想暗示这是一个好的解决方案，我只是希望它是正确的。我想点赞你的回答，但由于遗漏而无法实现。我认为你的回答仍可以通过解释“为什么”原始方法很慢以及替代方法为何“极快”来改进。如果程序员不能理解全表扫描的含义以及数据库中索引的重要性，他们将继续制作像本问题中的模式一样的模式。 - Asaph

Answer 4

您可以在正则表达式中使用 单词边界，例如：

SELECT TAG 
FROM TABLE
WHERE TAG REGEXP '[[:<:]]1[[:>:]]';

- codaddict