SQL 中的多个 LIKE
我想搜索多行并获取包含特定项目的行。
mySQL 中的表已设置为每个 id 每行都有一个唯一的值列表（以逗号分隔）。
例如：
id | order
1 | 1,3,8,19,34,2,38
2 | 4,7,2,190,38
现在，如果我想提取仅包含数字 19 的行，我该怎么做？我可以在具有 LIKE 条件的列表中计算出的可能性是：
19, <-- 19 at the start of the list
,19 <-- 19 at the end of the list
,19, <-- 19 inside the list
我尝试了以下操作，但无法获得任何结果，谢谢您的帮助！
SELECT *
FROM categories
WHERE order LIKE '19,%' OR '%,19%' OR '%,19%'
LIMIT 0 , 30
I wanted to search through multiple rows and obtain the row that contains a particular item.
The table in mySQL is setup so each id has a unique list (comma-delimited) of values per row.
Ex:
id | order
1 | 1,3,8,19,34,2,38
2 | 4,7,2,190,38
Now if I wanted to pull the row that contained just the number 19 how would I go about doing this? The possibilities I could figure in the list with a LIKE condition would be:
19, <-- 19 at the start of the list
,19 <-- 19 at the end of the list
,19, <-- 19 inside the list
I tried the following and I cannot obtain any results, Thank you for your help!
SELECT *
FROM categories
WHERE order LIKE '19,%' OR '%,19%' OR '%,19%'
LIMIT 0 , 30
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
首先，您不应该存储这样的值，您应该使用每个项目一行的子表，这将使您当前的查询更容易处理，并且也允许使用索引。
话虽如此，您想要的是这样的：
通过在
order字段中的值列表前面和后面添加一个逗号，您可以在该字段中查询您想要的值，并在每个值上添加一个逗号侧，并且不必处理该值是列表中的第一个或最后一个值的特殊情况。对于您列出的查询，您可以通过认识到
OR不会为LIKE子句提供更多参数来使其工作，它分隔完全不同的子句，因此像这样重写 SQL：注意第二个模式中缺少的逗号，它将匹配包含值 19 的字符串，并注意最后一个中删除的百分比字符，这将允许它匹配以下列表以19结束。
First of all, you should not store values like this, you should use a child table with one row per item, this would make your current query that much easier to handle, and would allow for indexes to be used as well.
Having said that, what you want is this:
By adding a comma in front of and after the list of values in the
orderfield, you can query that field for the value you want with a comma on each side, and not have to deal with the special case of the value being the first or last value in the list.With the query you listed, you could've made it work by realizing that
ORdoesn't give more arguments to theLIKEclause, it separates entirely different clauses, and thus rewritten the SQL like this:Note the missing comma in the second pattern there, which would match strings containing the value 19, and note the removed percentage character in the last ,which would allow it to match lists that ended with 19.
您可以使用
FIND_IN_SET以更简单的方式解决您的问题：一般来说，您应该尝试设计数据库，以便不需要这种类型的操作。
You can use
FIND_IN_SETto solve your problem in a simpler way:In general though you should try to design your database so that this type of operation is not necessary.