MySQL 全文檢索實現 LIKE 模糊搜尋：N-gram 編碼方案

MySQL 全文檢索實現 LIKE 模糊搜尋：N-gram 編碼方案

標準 LIKE '%keyword%' 無法使用索引，資料量超過百萬時全表掃描效能崩潰。本文分享一種利用 MySQL 全文索引（Full-text Index）配合字元級 N-gram 編碼，實現高效模糊搜尋的創新方案。

一、核心問題

• LIKE 'abc%' 可用索引（前綴匹配）
• LIKE '%abc'、LIKE '%abc%' 無法用索引 → 全表掃描
• 全文索引基於「詞」，MATCH AGAINST 搜尋「老鼠愛大米」可匹配「老鼠」、「大米」，但無法匹配「愛」、「鼠愛」等子字串

二、解決思路：字元級 N-gram + 全文索引

1. 將每個字元視為一個「詞」
2. 用空格分隔所有字元，構建「詞」序列
3. 全文索引建立在這個序列上
4. 搜尋時同樣將關鍵字拆解為字元序列，用雙引號包裹精確匹配

三、MySQL 配置

[mysqld]
ft_min_word_len = 1          -- 最小詞長設為 1（預設 4），允許單字元索引
ft_stopword_file = ""        -- 禁用停用詞表，不忽略任何詞

重啟 MySQL 生效。

四、建立搜尋表

CREATE TABLE `tbl_search` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(500) NOT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `idx_name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

注意：InnoDB 5.6+ 也支援全文索引，但需設定 innodb_ft_min_token_size=1。

五、編碼與搜尋實戰（Java 範例）

1. 編碼函數：字元 → 十六進位 + 空格

public static String encode(String input) {
    if (input == null) return null;
    StringBuilder output = new StringBuilder();
    for (int i = 0, len = input.length(); i < len; ++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(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(' ');
            }
            // 其他字元忽略
        }
    }
    // 移除尾部空格
    if (output.length() > 0 && output.charAt(output.length() - 1) == ' ') {
        output.deleteCharAt(output.length() - 1);
    }
    return output.toString();
}

2. 範例

• 原文：藍皮鼠2008
• 編碼後：84dd 76ae 9f20 32 30 30 38
• 存入 name 欄位

3. 搜尋 SQL

-- 搜尋包含「皮鼠2」的記錄
SELECT * FROM `tbl_search` 
WHERE MATCH(`name`) AGAINST('"76ae 9f20 32"' IN BOOLEAN MODE);

• IN BOOLEAN MODE：啟用布林模式，支援雙引號精確短語匹配
• 雙引號內的空格分隔「詞」必須連續、順序一致出現
• 相當於 LIKE '%皮鼠2%' 但可使用全文索引

六、優缺點分析

七、適用場景

• 用戶名、暱稱、帳號、商品標題等短文本模糊搜尋
• 資料量 > 100 萬，LIKE '%...%' 明顯緩慢
• 不想引入 Elasticsearch/Solr 等外部搜尋引擎
• 寫入頻率中等，可接受編碼額外開銷

八、替代方案對比