开发者

MySQL full text search, why am I getting Incorrect arguments to MATCH

SELECT 
* 
FRO开发者_开发百科M 
company c
INNER JOIN 
city ci ON ci.city_id = c.city_id
INNER JOIN 
state s ON s.state_id = c.state_id
WHERE 
MATCH (
c.name, ci.name, c.zipcode, s.name
)
AGAINST (
'los angeles'
)


All columns in MATCH (...) must be from the same table, because they must be fulltext indexed, and MySQL cannot create one index for data in multiple tables.


I had the same problem and solved it like this:

SELECT 
* 
FROM 
company c
INNER JOIN 
city ci ON ci.city_id = c.city_id
INNER JOIN 
state s ON s.state_id = c.state_id
WHERE 
   MATCH (c.name) AGAINST ('los angeles')
OR MATCH (ci.name) AGAINST ('los angeles')
OR MATCH (c.zipcode) AGAINST ('los angeles')
OR MATCH (s.name) AGAINST ('los angeles')

But as I see, You are searching in simple fields like 'name' and 'zipcode'. As for me, it would be better to use LIKE and just concatenate them

SELECT 
* 
FROM 
company c
INNER JOIN 
city ci ON ci.city_id = c.city_id
INNER JOIN 
state s ON s.state_id = c.state_id
WHERE 
CONCAT_WS (' ', c.name, ci.name, c.zipcode, s.name)
LIKE ('%los angeles%')
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜