MySQL多对多搜索

Question

我已经开始开发我的第一个MySQL数据库，我遇到了一个简单的问题。

我将我的电影按“多对多”关系进行分类：

'movies' Table
+---------+-------------------+
|movie_id |movie_title        |
+---------+-------------------+
|1        |Indiana Jones      |
|2        |Shaun of the Dead  |
+---------+-------------------+

'genres' table
+---------+-----------+
|genre_id |genre_name |
+---------+-----------+
|1        |adventure  |
|2        |comedy     |
|3        |horror     |
+---------+-----------+

'movie_genres' table
+---------+---------+
|movie_id |genre_id |
+---------+---------+
|1        |1        |
|2        |2        |
|2        |3        |
+---------+---------+

我试图做的是按类型搜索我的电影表，并显示每部电影的所有类型，例如：

Searching 'horror'

+------------------+---------------+
|movie_title       |genre_names    |
+------------------+---------------+
|Shaun of the Dead |comedy, horror |
+------------------+---------------+

这是一个我用来搜索恐怖电影的查询

    SELECT m.movie_title, GROUP_CONCAT(g.genre_name SEPARATOR ', ') as genre_names
    FROM movies m
    LEFT JOIN genre_movies gm ON gm.movie_id = m.movie_id
    LEFT JOIN genres g ON g.genre_id = gm.genre_id
    GROUP BY m.movie_id
    HAVING genre_names LIKE '%horror%'

我遇到的问题是，这个查询在筛选之前检索所有表的全部，我收集的结果非常低效。有没有更好的方法可以在显示所有相关类型的同时按一种类型搜索？

Answer 1

SELECT m.movie_title, GROUP_CONCAT(g.genre_name SEPARATOR ', ') as genre_names
FROM  movies m
      INNER JOIN
      (
        SELECT gm.movie_id
        FROM   genre_movies gm 
               INNER JOIN genres g 
                  ON g.genre_id = gm.genre_id
        WHERE  g.genre_name = 'horror'
        GROUP BY gm.movie_id
      ) a ON a.movie_id = m.movie_id
      INNER JOIN genre_movies gm ON gm.movie_id = m.movie_id
      INNER JOIN genres g ON g.genre_id = gm.genre_id
GROUP BY m.movie_id

• SQLFiddle演示

Answer 2

你为什么不把genre_id放在电影桌上？

大概是这样的：

'movies' Table
+---------+------------+---------------------+
|movie_id |movie_genre |movie_title          |
+---------+----------------------------------+
|1        |1           | Indiana Jones       |
|2        |2 ,3        | Shaun of the Dead   |
+---------+----------------------------------+

所以你只需要两张桌子。并且可以很容易地过滤查询中的数据。

我试图做的是按类型搜索我的电影表，并显示每部电影的所有类型，例如：

可能是这样的：

SELECT fields FROM table WHERE movie_genre LIKE %movie_genre_id%

Answer 3

试试这个：

SELECT m.movie_title, A.genre_names
FROM movies m
INNER JOIN (SELECT gm.movie_id, GROUP_CONCAT(g.genre_name SEPARATOR ', ') AS genre_names
    FROM genre_movies gm INNER JOIN genres g ON g.genre_id = gm.genre_id
    GROUP BY gm.movie_id) AS A ON m.movie_id = A.movie_id 
WHERE genre_names LIKE '%horror%'