MySQL全文搜索：如何结合通配符实现距离查询？

Great to hear you've got the wildcard * working with BOOLEAN MODE already—let's tackle how to add proximity (distance-based) matching to that mix.

Key Background: MySQL's Proximity Operator #

First, note that MySQL 8.0.16 and later introduced the NEAR operator, which lets you specify the maximum number of words between two terms. For older versions, you're limited to workarounds (more on that later), but let's start with the modern, efficient approach.

Using NEAR with Wildcards #

You can combine the wildcard * directly with the NEAR operator in BOOLEAN MODE. The basic syntax looks like this:

SELECT * FROM news 
WHERE MATCH (content) AGAINST ('quick NEAR/[max_distance] horse*' IN BOOLEAN MODE);

• Replace [max_distance] with the maximum number of words you want between quick and any term matching horse* (e.g., horse, horses).
• If you omit the distance (just NEAR), MySQL uses a default of 10 words.
• The NEAR operator is bidirectional, so quick NEAR horse* works the same as horse* NEAR quick.

Example for Your Dataset

If you want quick and horse* to be within 2 words of each other, your query would be:

SELECT * FROM news 
WHERE MATCH (content) AGAINST ('quick NEAR/2 horse*' IN BOOLEAN MODE);

This will return these rows from your sample data:

The quick brown horse jumps over the lazy dog
The quick brown horses jumps over the dog

Because in both cases, quick and horse/horses are only separated by one word (brown). Rows without both terms (like "The horse is brown." or "quick as a mouse was the spider.") won't be included.

For MySQL Versions Before 8.0.16 #

If you're stuck on an older version, MySQL doesn't have a native proximity operator that works with wildcards. Your options are:

• Post-filter in your application: First run your existing query (+quick +horse*) to get all relevant rows, then use code to calculate the word distance between quick and horse* matches in each content field.
• Use regex (not recommended): You could use REGEXP to match patterns where quick and horse/horses are close, but this bypasses the full-text index and will be slow on large datasets.

Important Notes #

• Wildcards (*) can only be placed at the end of a term (e.g., horse* works, but *horse or hor*se don't) in MySQL full-text search.
• Adding + before terms (like +quick +(horse* NEAR quick)) makes the match mandatory, which is redundant with NEAR (since NEAR requires both terms to exist) but can make your query more explicit if you prefer.

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