如何在mySQL中的数据库表中搜索逗号分隔的字符串

How to search for a comma-separated string in a database table in mySQL.

如何在mySQL中的数据库表中搜索逗号分隔的字符串。

Suppose, I have a string in variable $facilities='breakfast,dinner,lunch' and in my database I have a string saved in a field called facilities having values breakfast,dinner,clothing,lunch,hot water.

假设,我在变量$ facilities ='早餐,晚餐,午餐'中有一个字符串,并且在我的数据库中,我有一个字符串保存在一个名为设施的字段中,其中包括早餐,晚餐,衣服,午餐,热水。

How do I get the row having values $facilities?

如何获得值为$ facilities的行?

3 个解决方案

#1

Work like this i hope this is your requirement

像这样工作我希望这是你的要求

select * from table where field_name like '%$fieldname%'

#2

You're going to need to break apart the comma separated string first, and include a clause for each, so for that input you'd generate SQL like (assuming you want rows that have all three, and maybe other flags set):

您将需要首先拆分逗号分隔的字符串,并为每个字符串包含一个子句,因此对于该输入,您将生成类似SQL(假设您希望所有三个行都设置,并且可能设置其他标志):

SELECT      *

FROM        YourTable

WHERE       FIND_IN_SET('breakfast', facilities)
    AND     FIND_IN_SET('dinner', facilities)
    AND     FIND_IN_SET('lunch', facilities)

You can break the string apart in PHP with something a little like:

您可以使用类似的东西在PHP中拆分字符串:

$facilities='breakfast,dinner,lunch';
$desired = explode ( "," , $facilities );

And just use a loop to build your SQL

只需使用循环来构建SQL

#3

SELECT  *
FROM    tableName
WHERE   FIND_IN_SET('lunch', 'breakfast,dinner,lunch')

EDIT:

SELECT * FROM tableName WHERE FIND_IN_SET('lunch', food_column)

SELECT * FROM tableName WHERE FIND_IN_SET('lunch',food_column)

food_column is the name of the column where the string 'breakfast,dinner,lunch' is stored

food_column是存储字符串'breakfast,dinner,lunch'的列的名称

#1