MySQL从逗号分隔的字符串中搜索

I have Structure like this table.

我有像这张桌子的结构。

          ID                           Category            Publisher

           1                             1,2                  A
           2                             1                    B
           3                             2                    C
           4                             1                    D
           5                             2                    E
           6                             2,3,1                F

I want a query, when i search for category for 1,3 then it's return following

我想要一个查询,当我搜索1,3的类别然后它返回以下

I tried but don't get any optimized way.

我试过,但没有得到任何优化的方式。

          ID                           Category            Publisher

           1                             1,2                  A
           2                             1                    B
           4                             1                    D
           6                             2,3,1                F

2 个解决方案

#1

select * from your_table
where find_in_set(1, category) > 0
or find_in_set(3, category) > 0

But actually you should never store multiple values in a single column. Better change your table design.

但实际上,您绝不应该在一个列中存储多个值。更好地改变您的桌面设计。

#2

While pure SQL does not offer the tools to compare between two sets, this can be implemented in code (JavaScript/PHP) or using stored procedures.

虽然纯SQL不提供在两个集之间进行比较的工具,但这可以在代码(JavaScript / PHP)中或使用存储过程实现。

In order to use pure SQL, you could implement a stored procedure to return the comma-separated string as a single-column temporary table (this answer and this post can give you an idea for implementing this part).

为了使用纯SQL,您可以实现一个存储过程来将逗号分隔的字符串作为单列临时表返回(这个答案和这篇文章可以为您提供实现此部分的想法)。

And then, utilizing what you have created, assuming the function is implemented STR_SPLIT(<string>, <delimiter>) and returns a table with a single column named colName, you could write:

然后,利用您创建的内容,假设该函数已实现STR_SPLIT( , )并返回一个名为colName的单个列的表,您可以编写:

SELECT DISTINCT `tblName`.*
FROM `tblName`
INNER JOIN STR_SPLIT('1,3',',') `given`
   ON FIND_IN_SET(`given`.`colName`, `category`) > 0

This will take each value and match it separately, which will created duplicates that are going to be eliminated by the DISTINCT statement, making this a non-ideal solution.

这将获取每个值并单独匹配,这将创建将由DISTINCT语句消除的重复项,使其成为非理想的解决方案。

I'd recommend using a reference table in this case of data, which will have made this issue a breeze to handle, or implementing a partial solution and finishing code-side.

我建议在这种情况下使用参考表,这将使这个问题变得轻而易举,或者实现部分解决方案并完成代码端。

#1

select * from your_table
where find_in_set(1, category) > 0
or find_in_set(3, category) > 0