说到全文检索的分词,多半讲到的是中(日韩)文分词,少有英文等拉丁文系语言,因为英语单词天然就是分词的。
但更少讲到阿拉伯数字。比如金额,手机号码,座机号码等等。
以下不是传统的从0开始针对mysql全文索引前世今生讲起。
我更喜欢从一个小问题入手,见缝插针的将相关的知识点,以非时间线性顺序零散穿插起来。
从一个线上的BUG说起
我们有一张人口表,里面的数据有多种数据源合并而来,因此每个用户的手机号可能有多个。
这也很好理解,有的人就是有多个手机号,有的人就是经常换手机号,对吧。
现在有个功能需要通过手机号去关联用户。
因为手机号有多个,所以要么使用like进行模糊匹配。用户表有上千万条记录,这样的效率肯定是不能接受的。
select * from t_user where phone like '%13112345678%'
select u.id,u.username,u.phone from t_user u LEFT JOIN t_user_phone p on u.id = p.user_id where p.phone = '13112345678'
最终选用全文索引。(mysql 5.7.6+)
先在用户表针对手机号创建一个全文索引。
使用内置分词引擎ngram。
CREATE FULLTEXT INDEX idx_full_text_phone ON t_user (phone) WITH PARSER ngram;
当使用手机模糊查询关联用户时可使用以下语句。
- 布尔模式模糊检索
select * from t_user where match(phone) AGAINST('13996459860' in boolean mode)
- 自然语言模式。mysql默认为此模式,所以第2条sql没有显式指定时,仍然为自然语言模式。
select * from t_user where match(phone) AGAINST('13996459860' in NATURAL LANGUAGE mode)
或
select * from t_user where match(phone) AGAINST('13996459860')
根据我们的需求,查询手机号需要全匹配才算命中。所以选择布尔模式。
自然语言模式做不到。
关于布尔模式和自然语言模式的区别,后面做介绍。
以上算是简单的背景介绍。
但是
万恶的但是,虽迟但到
有一天产品过来告诉我,某个手机号关联出来上百个人。
他问,这种情况是正常的吗?
他如果直接说你这里有个bug,我可能直接就怼回去了(bushi
但是他说得这么委婉,我反而没底了。
不要对一个程序员说:你的代码有Bug。他的第一反应是:①你的环境有问题吧;②S13你会用吗?
如果你委婉地说:你这个程序和预期的有点不一致,你看看是不是我的使用方法有问题?
他本能地会想:woco!是不是出Bug了!
直觉告诉我这不正常,不然这个人是搞电诈或者海王吗?
我拿手机号去数据库里查询。使用布尔模式全文检索,确实关联出来多个人。
但也确实是个BUG.
我们来完整地模拟一下。
先创建一张测试用户表。
phone字段加上全文索引,使用ngram分词器。
CREATE TABLE `t_user` (
`id` int(11) NOT NULL,
`username` varchar(10