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

Question

我有两个表，categories和movies。

在movies表中我有一列categories。该列由电影适合的类别组成。这些类别是用逗号分隔的ID。

这是一个例子：

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
}

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

Answer 1

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

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

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

返回您的问题
或者可以使用数据

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个或更少的类别时，最后一个查询才有效。