MySQL 如何在连接字段中使用逗号分隔列表连接两个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6861258/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to join two tables using a comma-separated-list in the join field
提问by Katie
I have two tables, categoriesand movies.
我有两张桌子，categories和movies。
In moviestable I have a column categories. That column consists of the categories that movie fits in. The categories are IDs separated by a comma.
在movies表中我有一列categories。该列包含电影适合的类别。类别是用逗号分隔的 ID。
Here's an example:
下面是一个例子：
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
}
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?
现在到实际问题：是否可以执行从电影表中排除类别列的查询，而是从类别表中选择匹配的类别并在数组中返回它们？像连接一样，但问题是有多个用逗号分隔的类别，是否可以做某种正则表达式？
采纳答案by Johan
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.
在数据库字段中使用逗号分隔的列表是一种反模式，应该不惜一切代价避免。
因为在 SQL 中再次提取这些逗号分隔值是一个 PITA。
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)
Now you can do
现在你可以做
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)
Note that the last query only works if there are 2 or fewer categories per movie.
请注意，最后一个查询仅适用于每部电影有 2 个或更少类别的情况。
回答by Karolis
select
m.id,
group_concat(c.name)
from
movies m
join categories c on find_in_set(c.id, m.categories)
group by
m.id
The output should be something like this:
输出应该是这样的：
Table movies {
-id- -categories-
1 Comedy,Drama
2 Action,Drama
4 Other,Dance
}
回答by Lightness Races in Orbit
Brad is right; normalisation is the solution. Normalisation exists to solve this problem.It should be covered pretty well in your MySQL book if it's worth its salt.
布拉德是对的；规范化是解决方案。归一化就是为了解决这个问题。如果它物有所值，它应该在你的 MySQL 书中很好地涵盖。
If you really insist, though, you can fake the direct join by cross-matching with FIND_IN_SET(which conveniently expects a comma-delimited string of items).
但是，如果你真的坚持，你可以通过交叉匹配来伪造直接连接FIND_IN_SET（它方便地期望一个以逗号分隔的项目字符串）。
Now, MySQL can't return "an array" — that's what sets of results are for — but it can give you the category names separated by, say, a pipe (|):
现在，MySQL 无法返回“数组”——这就是结果集的用途——但它可以为您提供由管道 ( |)分隔的类别名称：
SELECT
`m`.`id`,
`m`.`name`,
GROUP_CONCAT(`c`.`name` SEPARATOR "|") AS `cats`
FROM
`movies` AS `m`,
`categories` AS `c`
WHERE
FIND_IN_SET(`c`.`id`, `m`.`categories`) != 0
GROUP BY
`m`.`id`;
Result:
结果：
id "name" "cats"
---------------------------------------------------
1 "Movie 1" "Comedy|Drama"
2 "Movie 2" "Action|Drama"
4 "Movie 4" "Dance"
回答by ryanprayogo
This isn't directly answering your question but what you have in the moviestable is really bad.
这并不能直接回答您的问题，但是您在movies表格中的内容非常糟糕。
Instead of combining categoriesusing comma, what you should be doing is to have each category on separate rows, eg:
categories您应该做的是将每个类别放在单独的行上，而不是使用逗号组合，例如：
Table movies {
-id- -categories-
1 2
1 4
2 1
2 4
4 3
4 5
}
