MATCH AGAINST中的多列 [英] Multiple columns in MATCH AGAINST

问题描述

我正在尝试从该查询中获取结果

I'm trying to get results from this query

SELECT ID FROM table1 WHERE MATCH(column1, column2) AGAINST ('text')

但是我收到此错误#1191 - Can't find FULLTEXT index matching the column list

当我在MATCH中仅放置一列有效时，MATCH中的两列或更多列不起作用.我见过人们正在这样做.

When I put only one column in MATCH it is working, two or more columns within MATCH doesn't work. I've seen people are doing it.

推荐答案

在MATCH()内部命名的列必须与先前为FULLTEXT索引定义的列相同.也就是说，索引中的列集必须与对MATCH()的调用中的列相同.

The columns named inside MATCH() must be the same columns defined previously for a FULLTEXT index. That is, the set of columns must be the same in your index as in your call to MATCH().

因此，要搜索两列，必须在同一两列中以相同顺序定义FULLTEXT索引.

So to search two columns, you must define a FULLTEXT index on the same two columns, in the same order.

以下是可以的:

ALTER TABLE tabl1 ADD FULLTEXT INDEX (column1, column2);

SELECT ID FROM table1 WHERE MATCH(column1, column2) AGAINST ('text')

以下内容是错误的，因为MATCH()引用了两列，但索引仅定义为一列.

The following is wrong because the MATCH() references two columns but the index is defined for only one column.

ALTER TABLE tabl1 ADD FULLTEXT INDEX (column1);

SELECT ID FROM table1 WHERE MATCH(column1, column2) AGAINST ('text')

以下内容是错误的，因为MATCH()引用了两列，但为三列定义了索引.

The following is wrong because the MATCH() references two columns but the index is defined for three columns.

ALTER TABLE tabl1 ADD FULLTEXT INDEX (column1, column2, column3);

SELECT ID FROM table1 WHERE MATCH(column1, column2) AGAINST ('text')

以下内容是错误的，因为MATCH()引用了两列，但每个索引都为一列定义.

The following is wrong because the MATCH() references two columns but each index is defined for one column.

ALTER TABLE tabl1 ADD FULLTEXT INDEX (column1);
ALTER TABLE tabl1 ADD FULLTEXT INDEX (column2);

SELECT ID FROM table1 WHERE MATCH(column1, column2) AGAINST ('text')

以下是错误的，因为MATCH()引用了两列，但顺序错误:

The following is wrong because the MATCH() references two columns but in the wrong order:

ALTER TABLE tabl1 ADD FULLTEXT INDEX (column1, column2);

SELECT ID FROM table1 WHERE MATCH(column2, column1) AGAINST ('text')

总而言之，使用MATCH()必须以相同的顺序引用与一个全文索引定义完全相同的列.

In summary, use of MATCH() must reference exactly the same columns, in the same order, as one fulltext index definition.

这篇关于MATCH AGAINST中的多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！