mysql WHERE SET数据类型包含item

I have a table that uses a SET datatype for one of the fields, and to check if the field contains a specific element I use

我有一个表使用其中一个字段的SET数据类型,并检查该字段是否包含我使用的特定元素

SELECT * FROM table WHERE myset LIKE %value%;

This works most of the time, but two of the potential values have the same word, i.e. one possible element in the set is Poodle and another is Toy Poodle. If I do

这大部分时间都有效,但两个潜在的值具有相同的单词,即集合中的一个可能元素是Poodle,另一个是玩具贵宾犬。如果我做

SELECT * FROM table WHERE myset LIKE %Poodle%;

It returns all rows that have either Poodle or Toy Poodle. I want it to only return if the field contains Poodle. If I remove the wildcards then it will only return the rows that have ONLY Poodle. So basically, if the table was:

它返回所有具有Poodle或Toy Poodle的行。如果该字段包含Poodle,我希望它只返回。如果我删除通配符,那么它将只返回只有Poodle的行。所以基本上,如果表是:

id | myset
-------------------------
1  | "Poodle"
2  | "Toy Poodle"
3  | "Poodle","Toy Poodle"
4  | "Toy Poodle","Poodle"

I need a select statement that would return 1,3, and 4 but not 2. Is this possible?

我需要一个返回1,3和4而不是2的select语句。这可能吗?

4 个解决方案

#1

How about using the FIND_IN_SET method?

使用FIND_IN_SET方法怎么样?

#2

You need to do: SELECT * FROM table WHERE FIND_IN_SET('Poodle',myset)>0 as described in documentation

您需要这样做:SELECT * FROM表WHERE FIND_IN_SET('Poodle',myset)> 0,如文档中所述

#3

SELECT * FROM table WHERE FIND_IN_SET( '"Poddle"', myset ) > 0

looking for " would be important to eliminate record #2

寻找“对于消除记录#2很重要

#4

sorry if I'm completely missing the point here, but are the quotations in your resultset actually stored in the db?

对不起,如果我完全忽略了这一点,但结果集中的引用是否实际存储在数据库中?

what I mean is, does the actual value of the first row equal...

我的意思是,第一行的实际值是否相等......

"Poodle"

...or have you just quoted these values for presentation purposes, in which case the first value would actually be...

...或者您刚刚引用这些值用于演示目的,在这种情况下,第一个值实际上是......

Poodle

Only reason I ask is, why not just do a query like...

我问的唯一原因是,为什么不做一个像...这样的查询

SELECT * FROM table WHERE myset LIKE '%"Poodle"%';

Apologies if I've massively missed the point or over-simplified beyond the point of recognition.

如果我已经大量错过了这一点,或者超出了认可点而过度简化,那就道歉了。

good luck with a fix anyway!

祝你好运,无论如何!

#1