如何在SQL全文检索查询中排除所有未指定词汇？

1. 基础方案：全文索引+词汇数量校验 #

你的原查询能确保记录包含所有目标词汇，但没法排除额外词汇。可以在原查询基础上，添加词汇数量匹配的条件，确保字段里的词汇总数和用户输入的完全一致：

-- 示例：用户输入「чорна сосна」（2个词汇）
SELECT t.*
FROM `table` t
WHERE 
  -- 确保包含所有输入词汇
  MATCH(t.`field`) AGAINST('+чорна +сосна' IN BOOLEAN MODE)
  -- 统计字段拆分后的词汇数量，确保等于输入的词汇数
  AND (SELECT COUNT(*) FROM JSON_TABLE(
    CONCAT('["', REPLACE(t.`field`, ' ', '","'), '"]'),
    '$[*]' COLUMNS(word VARCHAR(255) PATH '$')
  ) AS words) = 2;

如果字段包含标点或特殊字符，先通过正则清洗：把非乌克兰语字母的字符替换成空格，再拆分统计。

2. 严格集合匹配：确保无额外词汇 #

要更精准地保证字段的词汇集合和输入完全一致，没有多余词汇，可以用以下方式：

-- 先定义输入词汇和数量
SET @input_words = 'чорна,європейська,сосна';
SET @input_count = 3;

SELECT t.*
FROM `table` t
WHERE 
  MATCH(t.`field`) AGAINST('+чорна +європейська +сосна' IN BOOLEAN MODE)
  AND (
    -- 统计字段中属于输入词汇的数量
    SELECT COUNT(DISTINCT word) 
    FROM JSON_TABLE(
      CONCAT('["', REPLACE(REGEXP_REPLACE(t.`field`, '[^а-яА-ЯїЇєЄіІґҐ ]', ''), ' ', '","'), '"]'),
      '$[*]' COLUMNS(word VARCHAR(255) PATH '$')
    ) AS words
    WHERE word IN (SELECT TRIM(value) FROM JSON_TABLE(CONCAT('["', @input_words, '"]'), '$[*]' COLUMNS(value VARCHAR(255) PATH '$')))
  ) = @input_count;

3. 百万级数据性能优化 #

针对数百万条记录的场景，上述子查询可能效率不足，建议提前预处理数据：

• 新增word_count字段，存储每条记录的词汇总数：

  -- 新增字段
  ALTER TABLE `table` ADD COLUMN word_count INT UNSIGNED NOT NULL DEFAULT 0;
  
  -- 初始化数据（后续新增/修改记录时同步更新该字段）
  UPDATE `table` SET word_count = (SELECT COUNT(*) FROM JSON_TABLE(
    CONCAT('["', REPLACE(REGEXP_REPLACE(`field`, '[^а-яА-ЯїЇєЄіІґҐ ]', ''), ' ', '","'), '"]'),
    '$[*]' COLUMNS(word VARCHAR(255) PATH '$')
  ) AS words);
  

• 优化后的查询会快很多：

  SET @input_count = 3;
  SELECT * FROM `table`
  WHERE 
    MATCH(`field`) AGAINST('+чорна +європейська +сосна' IN BOOLEAN MODE)
    AND word_count = @input_count;
  

4. 复合词场景处理 #

如果俗名存在带空格的复合词，空格拆分法不适用，建议用标签关联表优化：

• 新建关联表存储每个物种的独立标签：

  CREATE TABLE species_tags (
    species_id INT UNSIGNED NOT NULL,
    tag VARCHAR(255) NOT NULL,
    PRIMARY KEY(species_id, tag),
    INDEX idx_tag(tag)
  );
  

• 查询时通过关联匹配，同时校验标签总数：

  SELECT s.*
  FROM `table` s
  JOIN species_tags st ON s.id = st.species_id
  WHERE st.tag IN ('чорна', 'європейська', 'сосна')
  GROUP BY s.id
  HAVING COUNT(DISTINCT st.tag) = 3
  AND (SELECT COUNT(*) FROM species_tags WHERE species_id = s.id) = 3;
  

这种方案性能最优，适合大规模数据，且能完美处理复合词问题。

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