MySQL全文检索中Like索引的实现

迷南。 2022-06-12 11:45 235阅读 0赞

转自 http://database.51cto.com/art/200908/144024.htm

在实际业务中，很难避免MySQL全文检索并Like索引的这种需求。比如模糊搜索用户帐号，昵称之类。本文向您介绍如何在MySQL全文检索中实现Like索引。

AD：

在数据库使用中，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 >= '０' && ch <= '９' || ch >= 'Ａ' && ch <= 'Ｚ' 
    		|| ch == '_' || ch == '-')
    		{
    			output.append(Integer.toHexString(ch)).append(' ');
    		}
    		else if (ch >= 'a' && ch <= 'z' || ch >= 'ａ' && ch <= 'ｚ') 
    		{
    			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语句进行搜索

<table> 
 <tbody> 
  <tr> 
   <td style="margin-top:0px; margin-right:0px; margin-bottom:0px; margin-left:0px; padding-top:0px; padding-right:0px; padding-bottom:0px; padding-left:0px; color:rgb(51,51,51)"> <pre style="margin-top:0px; margin-bottom:1em; padding-top:0px; padding-right:0px; padding-bottom:0px; padding-left:0px; font-family:'Courier New',monospace; width:589px; overflow-x:auto; overflow-y:auto; background-color:rgb(230,230,230)">select * from tbl_search where match(name) against('"76ae 9f20 32"' in boolean mode) 
</pre> </td> 
  </tr> 
 </tbody> 
</table>

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