使用FULLTEXT MATCH AGAINST查询VARCHAR列增量ID子串的问题

这问题我之前处理电商订单搜索时也碰到过，咱们来拆解原因和可行的解决方案：

为什么当前搜索不生效？ #

你的增量ID格式是AB-BC0000123456，而MySQL默认的全文搜索（MATCH AGAINST）有几个核心限制：

1. 分词逻辑限制：默认会把字符串按非字母数字字符（比如这里的-）分割，所以BC0000257891会被当成一个完整的索引词。你单独搜257891或0000257891时，这些片段并没有被单独索引，自然匹配不到。
2. 短语搜索不适用：加引号的短语搜索要求完全匹配连续的短语，但你的字段里是AB-BC0000257891，并没有单独的"0000257891"短语，所以这种方式也没用。
3. 最小词长限制：MySQL默认的ft_min_word_len是4，虽然你的数字片段长度够，但如果是更短的数字会被直接忽略，不过你这个场景主要还是分词逻辑的问题。

解决方案 #

方案1：改用LIKE/REGEXP（简单直接，适合小数据量） #

如果订单表数据量不是特别大，直接用模糊匹配或正则表达式就能快速解决：

• 匹配任意位置包含257891的增量ID：

SELECT `main_table`.* FROM `sales_order_grid` AS `main_table` 
WHERE `main_table`.increment_id LIKE '%257891%';

• 更精准匹配增量ID后缀的数字部分（比如只匹配BCxxxxxx里的数字段）：

SELECT `main_table`.* FROM `sales_order_grid` AS `main_table` 
WHERE `main_table`.increment_id REGEXP 'BC[0-9]{6}257891$';

注意：LIKE前缀带%会导致索引失效，如果表数据量极大，这种方式可能会变慢，这时可以考虑方案2。

方案2：调整全文搜索配置（适合必须用全文搜索的场景） #

方法A：使用Ngram分词器（MySQL 8.0+支持）

Ngram分词器会把字符串拆分成指定长度的字符片段（默认是2），这样数字片段会被单独索引，就能支持部分内容匹配：

1. 先给字段添加带Ngram解析器的全文索引：

ALTER TABLE `sales_order_grid` ADD FULLTEXT INDEX idx_increment_ngram (increment_id) WITH PARSER ngram;

2. 用布尔模式搜索数字片段：

SELECT `main_table`.* FROM `sales_order_grid` AS `main_table` 
WHERE MATCH(`main_table`.increment_id) AGAINST('257891' IN BOOLEAN MODE);

方法B：拆分字段存储

把增量ID里的数字部分单独提取到一个新字段（比如increment_number），然后给这个字段建全文索引：

1. 添加字段并更新数据：

ALTER TABLE `sales_order_grid` ADD COLUMN increment_number VARCHAR(20);
-- 提取`-`后面的完整部分
UPDATE `sales_order_grid` SET increment_number = SUBSTRING_INDEX(increment_id, '-', -1);
-- 或者更精准提取BC后面的纯数字：SUBSTRING(SUBSTRING_INDEX(increment_id, '-', -1), 3)

2. 给新字段建全文索引：

ALTER TABLE `sales_order_grid` ADD FULLTEXT INDEX idx_increment_number (increment_number);

3. 搜索时直接针对数字字段：

SELECT `main_table`.* FROM `sales_order_grid` AS `main_table` 
WHERE MATCH(`main_table`.increment_number) AGAINST('257891');

方案3：针对Magento场景优化（如果是Magento系统） #

因为sales_order_grid是Magento的订单网格表，你还可以：

• 后台配置：检查Stores > Configuration > Catalog > Catalog Search里的搜索配置，切换到Elasticsearch（如果已部署），Elasticsearch的分词和部分匹配能力远强于MySQL全文搜索。
• 扩展插件：使用第三方搜索扩展增强订单网格的搜索功能，原生支持部分匹配。

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