mysql查询从数据库字段中搜索逗号分隔的字符串数据[重复]

This question already has an answer here:

这个问题在这里已有答案：

MySQL query finding values in a comma separated string 10 answers
MySQL查询以逗号分隔的字符串10回答查找值

I have comma separated string data in my database field name 'status' for example:("2,5,6,9,16") and i have to search whose status is exactly '6'.

我在数据库字段名称'status'中有逗号分隔的字符串数据，例如:(“2,5,6,9,16”），我必须搜索其状态正好是'6'。

I am doing like this.

我这样做。

   $offers = DB::query("select id,status from table  WHERE (status LIKE '%6%')")->execute()->as_array();
        $countOffer = 0;
        if($offers){
        foreach ($offers as $offer){
            $checks= explode(',',$offer['status']);
            foreach($checks as $check){
              if($check == '6'){
                $countOffer = $countOffer+1;  
              }  
            }
        }
        }

thanks in advance.

提前致谢。

2 个解决方案

#1

This should help you:

这应该可以帮到你：

select id,status from table  WHERE (',' || status || ',' LIKE '%,6,%');

Another option would be:

另一种选择是：

 SELECT  STATUS FROM TABLE WHERE 
 (    STATUS LIKE '%,6,%'  
   OR STATUS LIKE '%,6' 
   OR STATUS LIKE '6,%' 
   OR STATUS='6');

I would recommend the first option personally!

我个人推荐第一个选项！

#2

The usual approach to this kind of query is the use of FIND_IN_SET:

这种查询的常用方法是使用FIND_IN_SET：

select id, status from table  WHERE FIND_IN_SET('6',status) > 0;

Note:

注意：

Storing comma separated lists in columns is almost ever asking for trouble. Please consider normalizing your database.

在列中存储以逗号分隔的列表几乎都在寻找麻烦。请考虑规范化您的数据库。

#1