以逗号分隔的列值搜索帖子数组 [英] Search post array in comma separated column value

问题描述

表格 - 列数据

id | filter_data |
---|---------------
1  |2,3,45,67,4  |
2  |2,3,55,33,5,7|

从表单发布变量

$search_filter = {2,3,4,5}

如何获取搜索过滤器中的id

How can I get the id which are in the search filter

推荐答案

解决方案 1:

正如@Jens 评论的那样，将值存储为 CSV 是一种糟糕的数据库设计.因此，第一个解决方案是更改您的数据库设计，因为我真的不知道您在存储什么以及您的数据库/代码的目的是什么，我无法编写方案或提供任何有意义的建议.

As @Jens commented, it's a bad DB design to store values as CSV. So the first solution would be to change your DB design since I don't really know what you're storing and what is the purpose of your DB/code I can't write a scheme or give any meaningful suggestion.

解决方案 2:

使用 MySQL 的 find_in_set 函数.

Use MySQL's find_in_set function.

如果字符串 str 在字符串列表 strlist 由 N 个子字符串组成.字符串列表是一个由,"字符分隔的子串组成的字符串.如果第一个参数是一个常量字符串，第二个是一个类型的列SET，FIND_IN_SET() 函数被优化为使用位算术.如果 str 不在 strlist 中或 strlist 是空字符串，则返回 0.如果任一参数为 NULL，则返回 NULL.这个功能不起作用如果第一个参数包含逗号(，")字符，则正确.

Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by "," characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit arithmetic. Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function does not work properly if the first argument contains a comma (",") character.

SELECT id FROM table
WHERE FIND_IN_SET(searchFilterHere, filter_data)

解决方案 3:

您可以使用 LIKE 运算符.(有些成员可能会因为建议而杀了我，但如果你不想改变你的数据库设计 - 这是一个创造性的选择).

You can use the LIKE operator. (Some members probably will kill me for suggesting it, but if you don't want to change your DB design - this is a creative option).

让我们检查以下代码:

SELECT id FROM table WHERE filter_data LIKE '%2,%'

问题是它会返回 filter_data 列中带有 22, 的字段的 id.因此，您必须更改该列下的数据，以便 , 也将显示为第一个和最后一个字符.例如:

The problem is that it will return the id of a field with 22, in the filter_data's column. Therefore, you'll have to change the data under that column so , will appear also as the first and last characters. For instance:

id | filter_data   |
---|---------------
1  |,2,3,45,67,4,  |
2  |,2,3,55,33,5,7,|

现在您可以执行以下操作:

And now you can do the following:

SELECT id FROM table WHERE filter_data LIKE '%,2,%'

如果您有多个搜索过滤器"您可以将 LIKE 与 OR 运算符结合起来.

If you have multiple "search filters" you can combine the LIKEs with an OR operator.

这篇关于以逗号分隔的列值搜索帖子数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！