mysql 支持索引模糊搜索%% 代替全文搜索 全文检索数据库

用户表有个昵称字段需要模糊搜索  而且搜索量巨大  而且必须支持特殊字符串

个人不喜欢架构复杂化  多用elasticsearch、solr、sphinx  很麻烦   

所以研究了个通用解决方案  mysql支持索引模糊搜索

优点: 部署方便 配置即用

缺点: 短文本才能用上索引    长文本索引失效

1.首先修改mysql配置  改成全文索引最小为1字符串

[mysqld]  
innodb_ft_min_token_size=1
ft_min_word_len=1

2.新建STORED 虚拟字段 nickname_index 绑定原始nickname字段变化跟着一起变

3.nickname_index 创建全文索引

CREATE TABLE `tb_user` (
  `uid` int unsigned NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `nickname` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '别名',
  `nickname_index` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin GENERATED ALWAYS AS (regexp_replace(`nickname`,_utf8mb4'(.)',_utf8mb4'$1 ')) STORED COMMENT '全文索引',

  PRIMARY KEY (`uid`) USING BTREE,
  FULLTEXT KEY `nickname_index` (`nickname_index`)
) ENGINE=InnoDB AUTO_INCREMENT=1001371 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='用户表';

4.客户端写SQL注意事项 需要用同样的方案

desc
SELECT 
* 
from tb_user 
where 

MATCH(nickname_index) AGAINST( regexp_replace('关键字','(.)','$1 ') )  

and 

nickname like CONCAT('%','关键字','%') 

成功使用索引了

实现方案是

1.用虚拟键绑定原始键(原始值变了 索引也会跟着自动变)

2.利用全文索引 (把虚拟键字符串逐字插入空格 强制用1字符串分词)

 regexp_replace('关键字','(.)','$1 ')

3.使用时先用全文索引 搜索分好词的关键字 再like一下原始键  数据就准确了

where 

MATCH(nickname_index) AGAINST( regexp_replace('关键字','(.)','$1 ') )  

and 

nickname like CONCAT('%','关键字','%') 

PS: 低版本mysql 没有regexp_replace  需要用编程语言先处理好再传进来 也可以支持 麻烦点

作者:LingMax  原创mysql支持索引模糊搜索  喜欢点个赞

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值