MySQL布尔模式下二进制排序规则全文检索短语匹配失效问题

问题描述 #

实现简单全文检索时，遇到布尔模式与短语组合使用的异常：涉及列采用utf8_bin排序规则（表未使用该规则），预期包含短语Lorem Ipsum的记录能匹配到短语查询，但实际匹配结果为0。

测试环境 #

CREATE TABLE `test` (
  `test_id` int(11) NOT NULL AUTO_INCREMENT,
  `text_bin` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`test_id`),
  FULLTEXT KEY `text_bin` (`text_bin`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test` (`test_id`, `text_bin`) VALUES
(1, 'Lorem Ipsum Dolor Sit Amet.'),
(2, 'Consectetuer Adipiscing Elit.'),
(3, 'Amet Sit Dolor Ipsum Lorem.')
;

执行查询 #

SELECT t.test_id, t.text_bin,
  MATCH(t.text_bin) AGAINST ('Lorem Ipsum' IN BOOLEAN MODE) as m_Words,
  MATCH(t.text_bin) AGAINST ('"Lorem Ipsum"' IN BOOLEAN MODE) as m_Phrase,
  MATCH(t.text_bin) AGAINST ('Lorem' IN BOOLEAN MODE) as m_Lorem,
  MATCH(t.text_bin) AGAINST ('Ipsum' IN BOOLEAN MODE) as m_Ipsum
FROM test t;

查询结果 #

（注：数值已缩短至4位小数）

原因分析 #

这并非MySQL的bug，而是列排序规则与查询字符串默认排序规则不一致导致的问题：

• 列text_bin使用utf8_bin（大小写敏感的二进制排序规则），而表默认字符集为utf8，查询字符串默认采用utf8_general_ci排序规则。
• 布尔模式下的短语匹配逻辑对排序规则一致性要求更高：单个词匹配时，全文索引直接匹配词项即可；但短语匹配需要验证词的连续性，同时会进行字符串比较，不同排序规则下的比较逻辑差异导致匹配失败。

解决方法 #

1. 查询时指定匹配的排序规则：将查询字符串的排序规则与列保持一致，修改后的短语匹配语句如下：

MATCH(t.text_bin) AGAINST ('"Lorem Ipsum"' COLLATE utf8_bin IN BOOLEAN MODE) as m_Phrase

2. 统一列与表的排序规则：如果不需要大小写敏感的全文检索，可将列text_bin的排序规则改为utf8_general_ci，与表默认规则一致。
3. 确保全链路排序规则一致：若需保留utf8_bin的大小写敏感特性，所有涉及该列的全文检索查询都需指定COLLATE utf8_bin。

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