mysql 列匹配,在MySQL中使用多列进行匹配

I am trying to create a simplified search box that will match multiple columns against a keyword or keywords the user inputs. The following code is my attempt at using MySQL's MATCH/AGAINST. However, I cannot get the query to execute properly when there are two columns specified, in this case 'topic, country'. It will execute when I run the code as either 'country' or 'topic', but not both.

Is there a secret to this?

SELECT * ,

MATCH (

topic, country

)

AGAINST (

'China'

) AS score

FROM reports2

WHERE MATCH (

topic, country

)

AGAINST (

'China'

)

ORDER BY score DESC

yields:

#1191 - Can't find FULLTEXT index matching the column list

Which I find to be an inaccurate description of the error, because there are FULLTEXT indices for both of these. I even copied the table, turned it into MyISAM from INNODB, in order to do so.

Any suggestions would be appreciated.

解决方案

You don't need fulltext indices for each column, but a single fulltext index covering both columns

FULLTEXT_INDEX(topic, country)

Having a single index on each column will not work

FULLTEXT(topic); FULLTEXT(country); /* will not work as expected */

I also think that order is important, but I could be mistaken in that regard