MySQL全文检索中Like索引的实现
创始人
2024-04-29 18:01:35
0

在数据库使用中,DBA都会告诉大家SQL的LIKE条件为%XXX%号时,由于不能使用索引,当数据量变大时(比如超过百万条),全表扫描会导致性能很差。

但是在实际业务中,很难避免MySQL全文检索并Like索引的这种需求。比如模糊搜索用户帐号,昵称之类。既然这个需求必须做,但又不可以直接用LIKE。这里我和大家分享一下我们关于这种需求的一种解决方案。当然别人也可能采用过类似的办法,我不是很清楚。所以也用一下“原创”吧。

MySQL数据库很早就支持全文索引,但是全文索引和LIKE语句是不同的。具体点说,全文索引的单位是词,耳LIKE匹配的是字符。当然实际的区别更大,比如“老鼠爱大米”这段文本用全文搜索的话,条件“老鼠爱大米”,“老鼠和大米”,“大米老鼠”,“大米与老鼠”会搜索到内容,但是“爱”,“鼠爱”,“爱大”不会搜索到内容。反之,使用LIKE搜索时,“老鼠和大米”,“大米老鼠”,“大米与老鼠”不会找到内容,而“爱”,“鼠爱”,“爱大”会找到内容。我们这里不讨论两种方式的优劣,根据实际情况每种功能都会有各自的实际需求。比如对于大段文本,全文检索是最好的方法,但是对于姓名,帐号,昵称等很短的通常无意义文本,LIKE会更合适一些。

虽然全文检索和LIKE搜索不同,但是在特殊情况下,可以用全文搜索功能来实现LIKE搜索。具体就是每个字符作为一个词,而且使用双引号来限制词精确匹配(简单点说就是老鼠大米和大米老鼠不同),这样可以实现LIKE搜索的功能。

下面还是说一下具体的做法吧。

首先,数据库指定 --ft_min_word_len=2 --ft_stopword_file=""。第一个参数是告诉数据库,小于2个字符的词忽略。第二个是告诉数据库不忽略任何特殊词。这些设置是给实现功能创造条件。

然后建搜索表

CREATE TABLE  tbl_search (
  id int(10) unsigned NOT NULL auto_increment,
  name varchar(500),
  PRIMARY KEY  (id),
  FULLTEXT KEY idx_name (name)
) ENGINE=InnoDB AUTO_INCREMENT=1;

 

static String encode(String input) {
if (input == null) return null;
StringBuilder output = new StringBuilder();
for (int i = 0, c = input.length(); i < c; ++i) {
char ch = input.charAt(i);

if (ch >= '0' && ch <= '9' || ch >= 'A' && ch <= 'Z' 
|| ch >= '0' && ch <= '9' || ch >= 'A' && ch <= 'Z' 
|| ch == '_' || ch == '-') {
output.append(Integer.toHexString(ch)).append(' ');

} else if (ch >= 'a' && ch <= 'z' || ch >= 'a' && ch <= 'z') {
output.append(Integer.toHexString((int)ch - 32)).append(' ');

} else {
Character.UnicodeBlock block = Character.UnicodeBlock.of(ch);
if (block == Character.UnicodeBlock.CJK_UNIFIED_IDEOGRAPHS
|| block == Character.UnicodeBlock.KATAKANA
|| block == Character.UnicodeBlock.HIRAGANA) {
output.append(Integer.toHexString(ch)).append(' ');
} else {
// do nothing
}
}

}

// trim blank
int last = output.length() - 1;
if (last > 0 && output.charAt(last) == ' ') {
output.deleteCharAt(last);
}

return output.toString();
}

使用上面的代码对要搜索的内容编码,比如内容是“蓝皮鼠2008”,编码后的结果是“84dd 76ae 9f20 32 30 30 38”。将编码后的内容存入name字段。
 
使用如下SQL语句进行搜索

select * from tbl_search where match(name) against('"76ae 9f20 32"' in boolean mode) 

这样就基本实现了MySQL全文检索中的Like索引。

 

【编辑推荐】

  1. MySQL蚕食Oracle市场 六成IT设施使用开源软件
  2. 使用调度和锁定进行MySQL查询优化
  3. MySQL基本调度策略浅析
  4. MySQL左连接、右连接和内连接详解

相关内容

热门资讯

如何允许远程连接到MySQL数... [[277004]]【51CTO.com快译】默认情况下,MySQL服务器仅侦听来自localhos...
如何利用交换机和端口设置来管理... 在网络管理中,总是有些人让管理员头疼。下面我们就将介绍一下一个网管员利用交换机以及端口设置等来进行D...
施耐德电气数据中心整体解决方案... 近日,全球能效管理专家施耐德电气正式启动大型体验活动“能效中国行——2012卡车巡展”,作为该活动的...
20个非常棒的扁平设计免费资源 Apple设备的平面图标PSD免费平板UI 平板UI套件24平图标Freen平板UI套件PSD径向平...
德国电信门户网站可实时显示全球... 德国电信周三推出一个门户网站,直观地实时提供其安装在全球各地的传感器网络检测到的网络攻击状况。该网站...
为啥国人偏爱 Mybatis,... 关于 SQL 和 ORM 的争论,永远都不会终止,我也一直在思考这个问题。昨天又跟群里的小伙伴进行...
《非诚勿扰》红人闫凤娇被曝厕所... 【51CTO.com 综合消息360安全专家提醒说,“闫凤娇”、“非诚勿扰”已经被黑客盯上成为了“木...
2012年第四季度互联网状况报... [[71653]]  北京时间4月25日消息,据国外媒体报道,全球知名的云平台公司Akamai Te...