MySQL中MATCH AGAINST无结果但LIKE可匹配的问题求助

Hey there, let's dig into why your MATCH AGAINST query isn't returning any results even though the LIKE query finds 7 rows. Here are the most common culprits and how to fix them:

1. Verify the full-text index exists #

First off: MATCH() AGAINST() only works on columns with a full-text index applied. If you haven't set this up, that's a likely root cause.

Check if the index exists:

SHOW INDEX FROM site_plugin_products_cache_texts;

If no full-text index for item_text appears, create it with:

ALTER TABLE site_plugin_products_cache_texts ADD FULLTEXT INDEX ft_idx_item_text (item_text);

2. Your keyword is in MySQL's stopword list #

MySQL maintains a default list of "stopwords"—common words like "your", "the", or "and" that are ignored in full-text searches because they're too frequent. Since your query uses +your, if "your" is a stopword, MySQL treats it as non-existent, making your query effectively look for rows that must contain a term it doesn't recognize (hence no results).

Check stopwords for your engine: #

• For InnoDB:

  SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;
  

• For MyISAM:
  Check the stopword file path with:

  SHOW VARIABLES LIKE 'ft_stopword_file';
  

  (You can cross-reference the file content with MySQL's official default stopword list.)

Fix stopword issues: #

• Option 1: Disable stopwords temporarily (good for testing):
  Add this to your my.cnf/my.ini file, then restart MySQL:

  # For InnoDB
  innodb_ft_enable_stopword = OFF
  # For MyISAM
  ft_stopword_file = ''
  

• Option 2: Use a custom stopword list:
  Create a text file with only the words you want to exclude (one per line), then update your config:

  # For InnoDB
  innodb_ft_stopword_file = '/path/to/your_custom_stopwords.txt'
  # For MyISAM
  ft_stopword_file = '/path/to/your_custom_stopwords.txt'
  

After changing the config, rebuild your full-text index to apply changes:

ALTER TABLE site_plugin_products_cache_texts DROP INDEX ft_idx_item_text;
ALTER TABLE site_plugin_products_cache_texts ADD FULLTEXT INDEX ft_idx_item_text (item_text);

3. Your keyword is shorter than MySQL's minimum word length #

MySQL ignores words shorter than a set length:

• InnoDB default: 3 characters
• MyISAM default: 4 characters

Since "your" is 3 characters, if your table uses MyISAM, it'll be completely ignored.

Check the setting: #

# For InnoDB
SHOW VARIABLES LIKE 'innodb_ft_min_token_size';
# For MyISAM
SHOW VARIABLES LIKE 'ft_min_word_len';

Adjust the minimum word length: #

Update your my.cnf/my.ini file:

# For InnoDB
innodb_ft_min_token_size = 3
# For MyISAM
ft_min_word_len = 3

Restart MySQL, then rebuild the full-text index as shown earlier.

4. Validate your boolean mode syntax #

Your query uses +your +name, which means "return rows containing both 'your' AND 'name'". If either term is ignored (due to stopwords or length), the query will return nothing.

Test if "your" is being indexed with a simpler query:

SELECT * FROM site_plugin_products_cache_texts WHERE MATCH(item_text) AGAINST('your' IN BOOLEAN MODE);

If this returns nothing, it confirms "your" is the issue, not the syntax.

Final Test #

Once you've fixed the stopword or minimum length issue, re-run your original query:

SELECT * FROM site_plugin_products_cache_texts WHERE MATCH(item_text) AGAINST ('+your +name' IN BOOLEAN MODE);

It should now return the 7 rows you expect.

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