MariaDB布尔全文搜索否定逻辑：如何实现全集匹配？

Let's break down what's happening here first, then solve your problem.

Why -veli alone returns nothing #

MariaDB's full-text search in Boolean Mode has a key rule: you can't use a negation (-) as the only query term. The engine needs a "base set" of rows to filter from—without a positive match term, it doesn't know which rows to exclude -veli from, so it returns an empty set by design.

That's why lauri -veli works: first it finds all rows matching lauri, then removes any that have veli in the indexed columns.

How to get the equivalent of NOT MATCH(...) AGAINST('-veli') #

To replicate the "all rows except those with veli" result, you need to add a positive term that matches all valid rows (i.e., all rows with non-empty indexed columns, which is exactly what your NOT query targets).

The simplest way is to use the wildcard * as your positive match term. In Boolean Mode, * acts as a prefix match that will hit every row with at least one indexable word in first_name or last_name:

SELECT id, first_name, last_name 
FROM user 
WHERE MATCH (first_name, last_name) AGAINST ('* -veli' IN BOOLEAN MODE);

Why this works #

• The * tells MariaDB to grab all rows with indexed content (matching the scope of your original NOT query).
• The -veli then filters out any rows where veli appears in either first_name or last_name.

This query will return the exact same 213 rows as your NOT MATCH(...) statement.

Quick sanity check #

If you have rows where both first_name and last_name are empty (and thus not indexed), both queries will exclude them—so the behavior is identical.

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