我已经开始研究我的第一个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%'
我遇到的问题是这个查询在过滤之前会检索所有表的全部,我收集这些表的效率非常低.在显示所有相关类型时,是否有更好的方法可以搜索一种类型?
解决方法:
试试这个:
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%'