MySQL全文檢索中Like索引的實現
在數據庫使用中，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語句進行搜索
select * from tbl_search where match(name) against('"76ae 9f20 32"' in boolean mode)
|
這樣就基本實現了MySQL全文檢索中的Like索引。
【編輯推薦】
