求助：MyISAM数据库FULLTEXT索引匹配失败问题

Hey there, sorry to hear you're stuck with this frustrating FULLTEXT index issue. Let's walk through the most likely causes and fixes based on your setup:

1. Your MATCH() column list doesn't match the FULLTEXT index exactly #

MyISAM enforces a strict rule for multi-column FULLTEXT indexes: the columns you specify in your MATCH() clause must exactly match all the columns defined in the index (order doesn't matter, but you can't omit any).

For example, if your index is FULLTEXT(title,summary,overview,specList), a query like MATCH(title,summary) won't work—MySQL can't use the index because you're not including all four columns. Even if you only want to search one column, you still need to list all index columns in MATCH() (you can target specific terms to columns using boolean operators in AGAINST() if needed).

Valid query example:

SELECT * FROM Shop 
WHERE MATCH(title, summary, overview, specList) AGAINST('your search term' IN BOOLEAN MODE);

2. Double-check the table engine is still MyISAM #

Even though you mentioned this is a MyISAM database, it's worth confirming the table engine wasn't accidentally changed during your ALTER command. Run this to verify:

SHOW CREATE TABLE Shop;

Look for ENGINE=MyISAM in the output. If it switched to InnoDB (or another engine), the FULLTEXT index rules are different, which could cause this error. Switch it back with:

ALTER TABLE Shop ENGINE=MyISAM;

3. Check for typos or column name mismatches #

It sounds simple, but typos or case-sensitive mismatches (depending on your server's collation) can break the index match. Cross-reference the column names in your MATCH() clause with the exact names shown in phpMyAdmin's index details. For example, spec_list instead of specList would trigger this error.

4. Force MySQL to use the FULLTEXT index #

Sometimes the query optimizer might prioritize other indexes (like your primary key) over the FULLTEXT one, especially if your query includes additional WHERE conditions. Try forcing it to use your FULLTEXT index:

SELECT * FROM Shop FORCE INDEX (your_fulltext_index_name)
WHERE MATCH(title, summary, overview, specList) AGAINST('your search term');

Replace your_fulltext_index_name with the actual name of your FULLTEXT index (find this in phpMyAdmin's "Indexes" tab for the table).

5. Recreate the index to rule out corruption #

Rarely, an index might show up in phpMyAdmin but be corrupted under the hood. Drop and recreate it to fix this:

ALTER TABLE Shop DROP INDEX your_fulltext_index_name;
ALTER TABLE Shop ADD FULLTEXT(title, summary, overview, specList);

Hopefully one of these steps gets your FULLTEXT queries working as expected!

内容的提问来源于stack exchange，提问作者user1646428