从MySQL表获取记录基于列中的逗号分隔值 [英] Fetch record from MySQL Table based on comma seperated value in column

问题描述

我从名为 Pizza_Table 的表中提取记录。

I have fetching record from table named Pizza_Table.

Pizza_table Schema

id | pizza name | pizza_topping_ids
1  | pizza1     |1,2,3
2  | pizza2     |2,3
3  | pizza3     |4,5,6

其实我的功能是根据 pizza_topping_ids
我在此搜索中有工作分配。但我找不到正确的解决方案。 。

Actually my functionality is searching pizza based on pizza_topping_ids I have work allot in this searching. but I can't find proper solution. .

我无法使用 IN 子句获取记录。

I can't use IN clause for fetching record.

我试过了：

1）

 SELECT * 
 FROM `pizza_table` 
 WHERE `pizza_topping_id` REGEXP '[[:<:]]2[[:>:]]'

问题：获取所有值 2的记录

2）

SELECT * 
FROM `pizza_table` 
WHERE `pizza_topping_id` REGEXP '[[:<:]]1,2,3,4[[:>:]]' 

但MySQL返回一个空结果集，

but MySQL returned an empty result set,

如果逗号分隔值不匹配任何记录，则返回最小值 topping_id 列。

If comma separated values are not match to any record then return result which is most having value in topping_id column.

EX：如果我运行上面的查询找出有1,2,3,4 topping_id的披萨，数据库没有比萨存在有这样的topping_id组合，则结果将被最被覆盖的id应该显示为像'pizza1'有 pizza_topping_id 1,2,3 。

EX: If i have run above query for find out pizza having 1,2,3,4 topping_id, but In database no pizza exist having such topping_id combination then result will be most covered id should be displayed like 'pizza1' having pizza_topping_id 1,2,3.

对不起英语和格式，但试图了解我的问题。
提前感谢。

Sorry for bad English as well as format, but try to understand my problem. Thanks in advance.

请帮助我。

推荐答案

p>只要尝试此 -

Just try this-

SELECT * FROM `pizza_table` WHERE  FIND_IN_SET(2,pizza_topping_id)

或REGEXP

SELECT * FROM `pizza_table` WHERE pizza_topping_id REGEXP '^2,|,2$|,2,' OR pizza_topping_id =2

请参阅演示：演示

这篇关于从MySQL表获取记录基于列中的逗号分隔值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！