如果列有多个逗号分隔值，如何过滤 mysql 数据? [英] How can i filter mysql data if a column has multiple comma separated values?

问题描述

我想问一下，如果条件检查了一个有几个逗号分隔值的列，我如何过滤mysql数据.我给你举个例子-

我有下表说tbitems"-

id item_names item_types item_features1 项 1 8,6 10,5,4,9,8,62 项 2 8,6 10,5,8

现在假设我想获取项目类型为 8 或 6 但 item_features 完全等于我们指定的(例如 10、5 和 4)的记录(项目).

我知道我们可以使用 IN 运算符的第一个条件(项目类型).

但我不知道如何根据 item_features 过滤记录.

我可以这样写查询 -

SELECT * FROMtbitemsWHEREitem_typesIN (8) ANDitem_features` 操作员应该在这里只选择这些值(4、5、10)

如果我像第一个条件一样使用 IN ，那么上表的第二行也会被提取，因为 5 和 10 在集合 (10,5,8) 中可用，但我只想提取第一行.有什么想法吗?

编辑 -

抱歉，您的解决方法无效.

我只想得到这张表的第一行 -

但现在它产生了一些我不想要的额外行.而且我不能使用这么多 FIND_IN_SET 因为逗号分隔的值可能很大.我也不能在我的 PHP 代码中使用这个 sql.

解决方案

对于您当前的场景，您可以在每次需要匹配 item_features 时使用 FIND_IN_SET 来实现

SELECT * FROM表格1在哪里项目类型IN (8) AND FIND_IN_SET(4,item_features)AND FIND_IN_SET(5,item_features)AND FIND_IN_SET(10,item_features)

小提琴演示

<块引用>

但我问你是否可以更改架构然后先规范化您的结构见数据库规范化，将所有关系存储在单独的表中，就像 item_types_relations 的连接表，它存储当前表 id 和连接表中的所有项目 id 作为一对多和 item_features_relation 的相同连接表

编辑 根据@Ravinder 的评论

SELECT * FROM表格1在哪里FIND_IN_SET(8,item_types)AND FIND_IN_SET(4,item_features)AND FIND_IN_SET(5,item_features)AND FIND_IN_SET(10,item_features)

I want to ask how can i filter mysql data if the condition is checked for a column with several comma separated values. I give you an example -

I have following table say "tbitems" -

id     item_names     item_types    item_features
1      item 1        8,6          10,5,4,9,8,6
2      item 2        8,6          10,5,8

Now suppose i want to fetch the records (items) that have item types either 8 or 6 but having item_features exactly equal to what we specify for example 10, 5, and 4.

I know for the first condition (item types) we can use IN operator.

But I don't know how we can filter the record based on item_features.

I can write the query like this -

SELECT * FROMtbitemsWHEREitem_typesIN (8) ANDitem_features` WHAT OPERATOR SHOULD COM HERE TO SELECT ONLY THESE VALUES ( 4, 5, 10)

If i use IN like i did for first condition then the second row of above table is also fetched because 5 and 10 are available in the set (10,5,8) but i want to extract only first row. Any idea?

EDIT -

Sorry, your workaround didn't work.

I wanted to get only first row of this table -

But now it is yielding some extra rows that i didn't want. And moreover i cant use so many FIND_IN_SET since there comma-separated values may be large in numbers. Also i couldn't use this sql in my PHP code.

解决方案

For you current scenario you can do so by using FIND_IN_SET each time you need to match the item_features

SELECT * FROM
table1
WHERE
item_types
IN (8) AND FIND_IN_SET(4,item_features)
AND FIND_IN_SET(5,item_features)
AND FIND_IN_SET(10,item_features) 

Fiddle Demo

But i ask youif its possible to change the schema then first normalize your structure see Database normalization,store all the relations in a separate table ,like a junction table for item_types_relations which store the current table id and all the items id in a junction table as one-to-many and a same junction table for item_features_relation

EDIT As per @Ravinder's comment

SELECT * FROM
table1
WHERE
FIND_IN_SET(8,item_types)
AND FIND_IN_SET(4,item_features)
AND FIND_IN_SET(5,item_features)
AND FIND_IN_SET(10,item_features) 

这篇关于如果列有多个逗号分隔值，如何过滤 mysql 数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！