如何获取逗号分隔值的相应记录？

I have field called features as i am storing the value as comma separated values, now while selecting i need to fetch the corresponding name of the comma separated vale

我有字段称为功能,因为我将值存储为逗号分隔值,现在选择时我需要获取逗号分隔值的相应名称

table1- feature

id    feature_names
1        A
2        B
3        C
4        D
5        E

table2- car

id   car    feature_ids

1    Zen    2,3,4   // features = B,C,D
2    Benz   1,2     // features = A,B
3    Audi   4,5     // features  = D,E

How to get the corresponding names of the comma separated values while selecting the car table?

如何在选择汽车表时获取逗号分隔值的相应名称?

3 个解决方案

#1

Redesign your database.

重新设计您的数据库。

feature - id, name

feature - id,name

car - id, name

车 - id,名字

car_feature - car_id, feature_id

car_feature - car_id,feature_id

After that, you can use a JOIN in your query. Something like this:

之后,您可以在查询中使用JOIN。像这样的东西:

SELECT car.*, feature.name FROM car
    JOIN car_feature ON car_feature.car_id = car.id
    JOIN feature ON feature.id = car_feature.feature_id

You should not use comma-separated fields like that in relational databases. Certainly not, if you want to write queries based on that field. Even if it seems to work, it will quickly become extremely inefficient and unmaintainable.

您不应该在关系数据库中使用这样的逗号分隔字段。当然不是,如果你想根据该字段编写查询。即使它似乎工作,它将很快变得非常低效和不可维护。

Your scenario is called a Many-to-many relationship between the tables - cars can have any features and a feature can belong to any cars. The technique I outlined is called Cross-reference table. I have given all the keywords for you to search for the right MySQL tutorials and articles, for example:

您的方案称为表之间的多对多关系 - 汽车可以具有任何功能,并且功能可以属于任何汽车。我概述的技术称为交叉引用表。我已经为您提供了所有关键字,以搜索正确的MySQL教程和文章,例如:

How to handle a Many-to-Many relationship with PHP and MySQL

如何处理与PHP和MySQL的多对多关系

MYSQL: Many To Many Relationships

MYSQL:多对多的关系

#2

You could try the following:

您可以尝试以下方法:

select 
    a.id,
    a.car,
    group_concat(b.feature_names) as Features
from
    car a
        join feature b
            on b.id in (cast(b.feature_ids as char));

Having said that, you really should not use CSV within your database. Take one row per feature and have some extra rows in your car table.

话虽如此,您确实不应该在数据库中使用CSV。每个功能一行,并在您的车牌表中添加一些额外的行。

#3

-1

SELECT car FROM table2 WHERE feature_ids LIKE '%3%'

#1