MySQL 5.7中MATCH AGAINST查询出现大小写匹配异常问题求助

问题根源 #

你的问题本质是MySQL 5.7对全文索引大小写敏感性的处理逻辑与5.6存在差异，且索引构建时的会话/系统排序规则会直接影响匹配行为，最终导致了大小写匹配的随机异常：

• MySQL 5.6中，全文索引布尔模式的通配符查询默认忽略大小写，即便列使用utf8_bin这类大小写敏感的排序规则；
• 5.7调整了该逻辑，全文索引的大小写匹配严格遵循索引构建时所使用的排序规则。

你的列CUSTOMER_NM采用utf8_bin（大小写敏感），但数据库/服务器的排序规则是utf8_general_ci（大小写不敏感）。重建索引时，若当前会话的collation_connection为utf8mb4_general_ci（如你环境变量所示），MySQL会将存储的大写词转换为小写存入索引；若重建时会话排序规则意外变为utf8_bin，索引则会保留原始大写词。这就是“昨日大写能查、今日小写能查”的核心原因。

具体解决方案 #

方案1：统一列排序规则为大小写不敏感（推荐） #

将CUSTOMER_NM列的排序规则改为与数据库/服务器一致的utf8_general_ci，确保全文索引构建时统一处理为大小写不敏感，无论用户输入大写还是小写都能匹配：

-- 修改列排序规则（注意替换xxx为实际字段长度）
ALTER TABLE customer_name_detail MODIFY COLUMN CUSTOMER_NM VARCHAR(xxx) CHARACTER SET utf8 COLLATE utf8_general_ci;
-- 重建全文索引
ALTER TABLE customer_name_detail DROP INDEX idx_ft_customer_nm;
ALTER TABLE customer_name_detail ADD FULLTEXT INDEX idx_ft_customer_nm(CUSTOMER_NM);

方案2：强制索引构建使用列的二进制排序规则 #

每次重建索引前，先将会话排序规则设置为列的utf8_bin，确保索引严格按存储的大小写构建；查询时统一将用户输入转换为大写（与存储内容一致）：

-- 重建索引前执行，确保会话排序规则匹配列属性
SET collation_connection = 'utf8_bin';
ALTER TABLE customer_name_detail DROP INDEX idx_ft_customer_nm;
ALTER TABLE customer_name_detail ADD FULLTEXT INDEX idx_ft_customer_nm(CUSTOMER_NM);

-- 查询时统一转换用户输入为大写
SELECT * 
FROM customer_name_detail cnd
WHERE MATCH(cnd.customer_nm) AGAINST(UPPER('pine* apple* pizza*') IN BOOLEAN MODE);

方案3：切换为ngram分词器（灵活匹配场景） #

若业务需要更宽松的大小写匹配，可使用ngram全文分词器，它默认忽略大小写且不受列排序规则影响：

-- 删除旧索引
ALTER TABLE customer_name_detail DROP INDEX idx_ft_customer_nm;
-- 用ngram重建全文索引
ALTER TABLE customer_name_detail ADD FULLTEXT INDEX idx_ft_customer_nm(CUSTOMER_NM) WITH PARSER ngram;

注意：ngram分词器更适合中文或短词匹配场景，调整后通配符的使用逻辑会变化，需测试适配。

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