我在一张桌子上运行一个简单的LIKE搜索,但是出于某种原因,我似乎无法获得多个结果。在下面的查询示例中,我包含了“永不后退”和“灵魂冲浪者”等词,但我只得到其中一部电影。我的数据库里有那些电影,还有很多其他的。
以下是问题:

SELECT movies . * , GROUP_CONCAT( categories.name ) AS categories, GROUP_CONCAT( categories.id ) AS categories_id
FROM movies
LEFT JOIN movies_categories ON ( movies.id = movies_categories.movie_id )
LEFT JOIN categories ON ( movies_categories.category_id = categories.id )
WHERE movies.movie_title LIKE  '%surfer%'
OR movies.movie_title LIKE  '%date%'
OR movies.movie_title LIKE  '%never%'
OR movies.movie_title LIKE  '%back%'
OR movies.movie_title LIKE  '%down%'
OR movies.movie_title LIKE  '%surfer%'
OR movies.movie_title LIKE  '%soul%'
OR movies.movie_title LIKE  '%the%'
OR movies.movie_title LIKE  '%transporter%'
LIMIT 0 , 30

我想一定是查询出了问题,否则它会返回所有结果?我知道有些词在like从句中重复了,但这不重要。我错过什么了吗?
修好了!
我使用的group_concat()函数导致查询只返回一个结果,并连接所有电影的类别和类别id。我刚刚删除了组的concat(),现在它可以工作了。

最佳答案

您正在使用GROUP_CONCAT这是一个聚合函数,并将结果折叠为一个函数。
增加一个GROUP BY条款:

SELECT movies . * , GROUP_CONCAT( categories.name ) AS categories, GROUP_CONCAT( categories.id ) AS categories_id
FROM movies
LEFT JOIN movies_categories ON ( movies.id = movies_categories.movie_id )
LEFT JOIN categories ON ( movies_categories.category_id = categories.id )
WHERE movies.movie_title LIKE  '%surfer%'
OR movies.movie_title LIKE  '%date%'
OR movies.movie_title LIKE  '%never%'
OR movies.movie_title LIKE  '%back%'
OR movies.movie_title LIKE  '%down%'
OR movies.movie_title LIKE  '%surfer%'
OR movies.movie_title LIKE  '%soul%'
OR movies.movie_title LIKE  '%the%'
OR movies.movie_title LIKE  '%transporter%'
GROUP BY
        movies.id
LIMIT 0 , 30

此外,如果movies表是MyISAM,则可以运行以下命令:
SELECT  movies.*,
        GROUP_CONCAT(categories.name) AS categories,
        GROUP_CONCAT(categories.id) AS categories_id
FROM    movies
LEFT JOIN
        movies_categories
ON      movies_categories.movie_id = movies.id
LEFT JOIN
        categories
ON      categories.id = movies_categories.category_id
WHERE   MATCH(movie_title) AGAINST ("never back down" IN BOOLEAN MODE)
GROUP BY
        movies.id
LIMIT  0, 30

如果创建FULLTEXT索引,则此查询将运行得更快:
CREATE FULLTEXT INDEX fx_movies_title ON movies (movie_title)

关于mysql - MySQL:这不应该返回更多行吗?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/6876081/

10-12 12:41
查看更多