PHP+MySQLi中MATCH...AGAINST无结果异常排查求助

Hey there, let's break down why your query isn't returning the "zero code" record while it works perfectly with "zerx code". Here are the key troubleshooting steps to dig into:

1. Check if "zero" is a MySQL stopword #

MySQL keeps a list of stopwords—common, high-frequency words that get ignored in full-text searches (think "the", "and"). It’s super likely "zero" is in this default list, while "zerx" (a made-up term) isn’t.

To verify this:

• For InnoDB tables, run this query to view the default stopword list:

  SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;
  

• For MyISAM tables, first find the stopword file path:

  SHOW VARIABLES LIKE 'ft_stopword_file';
  

  Then check the contents of that file (usually stored in your MySQL data directory).

If "zero" is present, you have a few fixes:

• Customize the stopword list by updating the ft_stopword_file (MyISAM) or innodb_ft_server_stopword_table (InnoDB) system variable.
• Disable stopword filtering entirely (for MyISAM, set ft_stopword_file=''; for InnoDB, replace the stopword table with an empty one).

2. Verify full-text index minimum word length settings #

MySQL ignores words shorter than a specified length in full-text indexes. The default values are:

• innodb_ft_min_token_size = 3 (InnoDB)
• ft_min_word_len = 4 (MyISAM)

"zero" is 4 characters long, so it should be included with default MyISAM settings—but it’s worth double-checking:

-- For InnoDB
SHOW VARIABLES LIKE 'innodb_ft_min_token_size';

-- For MyISAM
SHOW VARIABLES LIKE 'ft_min_word_len';

If the value is higher than 4, that would explain the issue (though this would also block "zerx", so it’s probably not your root cause—but better to rule it out).

3. Fix your query’s search mode and wildcard usage #

Your current query uses the default natural language mode, where wildcards like * aren’t interpreted as you expect. The string *zero* is treated as a literal term, not a wildcard match.

If you want to use wildcards, switch to boolean mode—but note that MySQL only supports prefix wildcards (e.g., zero* to match words starting with "zero"). Suffix or wrapped wildcards (*zero or *zero*) won’t work as intended.

Try this adjusted query:

SELECT * FROM `conditions` WHERE MATCH(`desc`) AGAINST ('zero*' IN BOOLEAN MODE);

This will correctly match your "zero code" record.

4. Rebuild your full-text index #

Sometimes full-text indexes get out of sync if data was added/modified after the index was created, or if the index became corrupted. Rebuilding it can resolve unexpected behavior:

-- Replace `ft_desc` with your actual full-text index name
ALTER TABLE `conditions` DROP INDEX ft_desc;
ALTER TABLE `conditions` ADD FULLTEXT INDEX ft_desc(`desc`);

5. Test if MySQL recognizes "zero" as an indexable term #

Run this query to check if MySQL assigns a relevance score to your "zero code" record when searching for "zero":

SELECT id, `desc`, MATCH(`desc`) AGAINST ('zero') AS relevance_score
FROM `conditions`
WHERE id = [your_record_id];

If relevance_score is 0, that confirms MySQL isn’t treating "zero" as a valid search term—pointing straight back to stopword or minimum length issues.

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