如何在MariaDB多列全文索引中搜索多词前缀匹配条目？

问题分析 #

你遇到的核心问题是：使用布尔模式全文检索时，+de* +vr*仅匹配了同一字段内同时包含两个前缀的记录（如Delphine Vriaud），但未匹配跨字段的目标记录（如Albert De Vries）。主要原因有两点：

1. 短词未被索引：修改innodb_ft_min_token_size后未重建全文索引，旧索引仍不包含"De"这类短词；
2. 停用词拦截："De"属于InnoDB默认停用词列表中的常见短词，即使长度符合要求也不会被纳入索引。

解决方案 #

1. 调整全文检索配置 #

• 修改最小词长：将innodb_ft_min_token_size设置为2（需重启数据库生效，因为该参数为静态参数）。
• 自定义停用词表：避免"De"被拦截，需创建自定义停用词表并配置数据库使用它：

  -- 创建自定义停用词表（按需添加真正需要过滤的停用词，不要包含"de"）
  CREATE TABLE your_database.stopwords (value VARCHAR(180)) ENGINE=InnoDB;
  INSERT INTO stopwords (value) VALUES ('the'), ('and'), ('or');
  

  在数据库配置文件（如my.cnf）中添加：

  innodb_ft_server_stopword_table = 'your_database/stopwords'
  

  重启数据库使配置生效。

2. 重建全文索引 #

配置修改后，必须重建表的全文索引，确保新配置生效：

ALTER TABLE person DROP INDEX first_name_last_name;
ALTER TABLE person ADD FULLTEXT KEY first_name_last_name (first_name, last_name);

3. 执行目标查询 #

此时使用原布尔模式查询即可得到期望结果：

SELECT * FROM person
WHERE MATCH (first_name, last_name) AGAINST ('+de* +vr*' IN BOOLEAN MODE)
ORDER BY last_name, first_name;

该查询会返回所有同时包含以de开头和以vr开头词汇的记录，不管这两个词汇分别在first_name还是last_name字段中。

验证结果 #

执行上述步骤后，查询将返回以下3条目标记录：

• Delphine Vriaud
• Albert De Vries
• Georges De Vries

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