SQL MATCH AGAINST查询无法匹配衍生变体词问题咨询

Hey there, let's break down why your query isn't matching 'dagelijkse' and fix it step by step.

First, a quick clarification about your original query: in MySQL's default natural language mode, the % characters aren't wildcards—they're treated as regular text. So your query was actually searching for the exact string %dagelijks%, which isn't what you intended. That’s why it matched entries with 'dagelijks' or 'dagelijks ' (the full-text index extracts the 'dagelijks' word from the space-suffixed string), but not 'dagelijkse'—since that’s a distinct word with a different ending.

Here are two reliable solutions to get the results you want:

Solution 1: Use Boolean Mode with Suffix Wildcard #

If you just need to match any term starting with 'dagelijks' (like 'dagelijks', 'dagelijkse', etc.), switch to Boolean Mode and use the * suffix wildcard. This tells MySQL to target all words that begin with your base term.

Here's the corrected query:

SELECT * FROM fondslijst 
WHERE MATCH(Benaming, Verpakking, Auteur01, Auteur02, Auteur03, Auteur04, Auteur05, Auteur06, Auteur07, Auteur08, Auteur09, Auteur10) 
AGAINST('dagelijks*' IN BOOLEAN MODE);

• IN BOOLEAN MODE enables the flexible boolean full-text syntax.
• The * at the end acts as a suffix wildcard, so it will catch any word starting with 'dagelijks'.

Solution 2: Enable Dutch Stemming (Language-Aware Matching) #

For a more robust approach that handles Dutch word variations automatically (singular/plural, adjective endings, etc.), use MySQL's built-in Dutch stemmer. This requires setting up your full-text index with Dutch language support.

Step 1: Rebuild the Full-Text Index #

If your existing index wasn’t configured for Dutch, drop it first (if it exists) and recreate it with the Dutch parser (works for MySQL 8.0+):

-- Drop existing index if present
ALTER TABLE fondslijst 
DROP INDEX idx_fulltext_fondslijst;

-- Create new index with Dutch language support
ALTER TABLE fondslijst 
ADD FULLTEXT INDEX idx_fulltext_fondslijst (Benaming, Verpakking, Auteur01, Auteur02, Auteur03, Auteur04, Auteur05, Auteur06, Auteur07, Auteur08, Auteur09, Auteur10) 
LANGUAGE dutch;

Step 2: Query with Natural Language Mode #

Now you can use a simple natural language query, and MySQL will automatically match word variations that share the same root stem:

SELECT * FROM fondslijst 
WHERE MATCH(Benaming, Verpakking, Auteur01, Auteur02, Auteur03, Auteur04, Auteur05, Auteur06, Auteur07, Auteur08, Auteur09, Auteur10) 
AGAINST('dagelijks');

This will match 'dagelijks', 'dagelijkse', and other related Dutch word forms, since the stemmer reduces them to their common base root.

Quick Notes #

• Boolean Mode's * wildcard only works as a suffix (you can't use *dagelijks to match terms ending with 'dagelijks').
• Natural Language Mode ignores common stopwords (like 'de', 'het') and has a minimum word length threshold (default 4 characters, which 'dagelijks' easily exceeds).
• Ensure your MySQL version supports language-specific stemmers—MySQL 8.0 added improved support for multiple languages including Dutch.

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