I have a product table which contains a field called 'categories' to save product related category ids as comma separated values. I am using regexp to search products from a category.
我有一个产品表,其中包含一个名为“类别”的字段,用于将产品相关类别ID保存为逗号分隔值。我正在使用正则表达式来搜索某个类别的产品。
Assume there is record containing 4,24,1,31 my expression is,
假设我的表达式中包含4,24,1,31的记录,
..WHERE categories REGEXP ',?4,?'
but this returns both product of category 4 and 24
但这会返回第4类和第24类的产品
I just need to display only category 4.
我只需要显示第4类。
Am I missing something?
我错过了什么吗?
1 个解决方案
#1
7
Use
WHERE categories REGEXP "(^|,)4(,|$)"
This matches 4 if surrounded by commas or at the start/end of the string.
如果用逗号或字符串的开头/结尾包围,则匹配4。
In your present version, both commas are entirely optional, so the 4 in 24 matches.
在您目前的版本中,两个逗号都是完全可选的,因此24个匹配中的4个。
#1
7
Use
WHERE categories REGEXP "(^|,)4(,|$)"
This matches 4 if surrounded by commas or at the start/end of the string.
如果用逗号或字符串的开头/结尾包围,则匹配4。
In your present version, both commas are entirely optional, so the 4 in 24 matches.
在您目前的版本中,两个逗号都是完全可选的,因此24个匹配中的4个。