如何在连接字段中使用逗号分隔列表连接两个表

2021-11-20 00:00:00 join csv mysql

我有两个表，categories 和 movies.

在movies 表中，我有一列categories.该列由电影所属的类别组成.类别是用逗号分隔的 ID.

In movies table I have a column categories. That column consists of the categories that movie fits in. The categories are IDs separated by a comma.

这是一个例子:

Table categories {
  -id-       -name-
  1          Action
  2          Comedy
  4          Drama
  5          Dance
}

Table movies {
  -id-       -categories-  (and some more columns ofc)
  1          2,4
  2          1,4
  4          3,5
}

现在到实际问题:是否可以执行从电影表中排除类别列的查询，而是从类别表中选择匹配的类别并在数组中返回它们?像join一样，但问题是有多个用逗号分隔的类别，是否可以做某种正则表达式?

Now to the actual question: Is it possible to perform a query that excludes the categories column from the movies table, and instead selects the matching categories from the categories table and returns them in an array? Like a join, but the problem is there are multiple categories separated by comma, is it possible to do some kind of regex?

推荐答案

在数据库字段中使用逗号分隔列表是一种反模式，应不惜一切代价避免.
因为在 SQL 中再次提取这些逗号分隔的值是一个 PITA.

Using comma separated lists in a database field is an anti-pattern and should be avoided at all costs.
Because it is a PITA to extract those comma separated values out agian in SQL.

相反，您应该添加一个单独的链接表来表示类别和电影之间的关系，如下所示:

Instead you should add a separate link table to represent the relationship between categories and movies, like so:

Table categories
  id integer auto_increment primary key
  name varchar(255)

Table movies
  id integer auto_increment primary key
  name varchar(255)

Table movie_cat
  movie_id integer foreign key references movies.id
  cat_id integer foreign key references categories.id
  primary key (movie_id, cat_id)

现在可以了

SELECT m.name as movie_title, GROUP_CONCAT(c.name) AS categories FROM movies m
INNER JOIN movie_cat mc ON (mc.movie_id = m.id)
INNER JOIN categories c ON (c.id = mc.cat_id)
GROUP BY m.id

回到你的问题
或者，您可以使用您的数据

Back to your question
Alternativly using your data you can do 

SELECT m.name as movie_title
  , CONCAT(c1.name, if(c2.name IS NULL,'',', '), ifnull(c2.name,'')) as categories 
FROM movies m
LEFT JOIN categories c2 ON 
 (replace(substring(substring_index(m.categories, ',', 2),
  length(substring_index(m.categories, ',', 2 - 1)) + 1), ',', '') = c2.id)
INNER JOIN categories c1 ON 
 (replace(substring(substring_index(m.categories, ',', 1), 
  length(substring_index(m.categories, ',', 1 - 1)) + 1), ',', '') = c1.id)

请注意，最后一个查询仅适用于每部电影有 2 个或更少类别的情况.

Note that the last query only works if there are 2 or fewer categories per movie.

相关文章