MySQL全文檢索中Like索引的實(shí)現(xiàn)
在數(shù)據(jù)庫使用中，DBA都會(huì)告訴大家SQL的LIKE條件為%XXX%號(hào)時(shí)，由于不能使用索引，當(dāng)數(shù)據(jù)量變大時(shí)（比如超過百萬條），全表掃描會(huì)導(dǎo)致性能很差。
但是在實(shí)際業(yè)務(wù)中，很難避免MySQL全文檢索并Like索引的這種需求。比如模糊搜索用戶帳號(hào)，昵稱之類。既然這個(gè)需求必須做，但又不可以直接用LIKE。這里我和大家分享一下我們關(guān)于這種需求的一種解決方案。當(dāng)然別人也可能采用過類似的辦法，我不是很清楚。所以也用一下“原創(chuàng)”吧。
MySQL數(shù)據(jù)庫很早就支持全文索引，但是全文索引和LIKE語句是不同的。具體點(diǎn)說，全文索引的單位是詞，耳LIKE匹配的是字符。當(dāng)然實(shí)際的區(qū)別更大，比如“老鼠愛大米”這段文本用全文搜索的話，條件“老鼠愛大米”，“老鼠和大米”，“大米老鼠”，“大米與老鼠”會(huì)搜索到內(nèi)容，但是“愛”，“鼠愛”，“愛大”不會(huì)搜索到內(nèi)容。反之，使用LIKE搜索時(shí)，“老鼠和大米”，“大米老鼠”，“大米與老鼠”不會(huì)找到內(nèi)容，而“愛”，“鼠愛”，“愛大”會(huì)找到內(nèi)容。我們這里不討論兩種方式的優(yōu)劣，根據(jù)實(shí)際情況每種功能都會(huì)有各自的實(shí)際需求。比如對于大段文本，全文檢索是最好的方法，但是對于姓名，帳號(hào)，昵稱等很短的通常無意義文本，LIKE會(huì)更合適一些。
雖然全文檢索和LIKE搜索不同，但是在特殊情況下，可以用全文搜索功能來實(shí)現(xiàn)LIKE搜索。具體就是每個(gè)字符作為一個(gè)詞，而且使用雙引號(hào)來限制詞精確匹配（簡單點(diǎn)說就是老鼠大米和大米老鼠不同），這樣可以實(shí)現(xiàn)LIKE搜索的功能。
下面還是說一下具體的做法吧。
首先，數(shù)據(jù)庫指定 --ft_min_word_len=2 --ft_stopword_file=""。第一個(gè)參數(shù)是告訴數(shù)據(jù)庫，小于2個(gè)字符的詞忽略。第二個(gè)是告訴數(shù)據(jù)庫不忽略任何特殊詞。這些設(shè)置是給實(shí)現(xiàn)功能創(chuàng)造條件。
然后建搜索表
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();
}
|
使用上面的代碼對要搜索的內(nèi)容編碼，比如內(nèi)容是“藍(lán)皮鼠2008”，編碼后的結(jié)果是“84dd 76ae 9f20 32 30 30 38”。將編碼后的內(nèi)容存入name字段。
使用如下SQL語句進(jìn)行搜索
select * from tbl_search where match(name) against('"76ae 9f20 32"' in boolean mode)
|
這樣就基本實(shí)現(xiàn)了MySQL全文檢索中的Like索引。
【編輯推薦】
