我在一张桌子上运行一个简单的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/