萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 全文搜尋 FULLTEXT 完整教學:建立、查詢與中文優化

什麼是全文搜尋?

傳統 LIKE '%關鍵字%' 在大量資料下效能極差且不支援相關性排序。MySQL FULLTEXT 全文索引 專為文字搜尋設計,支援:

  • 自然語言搜尋(Natural Language)
  • 布林模式搜尋(Boolean Mode)
  • 查詢擴展搜尋(Query Expansion)
  • 相關性評分排序

建立 FULLTEXT 索引

建表時直接建立

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    FULLTEXT KEY ft_title_content (title, content)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

現有表新增索引

ALTER TABLE articles ADD FULLTEXT INDEX ft_content (content);

三種搜尋模式實戰

1. 自然語言模式(預設)

類似搜尋引擎,按相關性排序,忽略常見停用詞。

SELECT id, title, 
       MATCH(title, content) AGAINST('MySQL 優化' IN NATURAL LANGUAGE MODE) AS relevance
FROM articles
WHERE MATCH(title, content) AGAINST('MySQL 優化' IN NATURAL LANGUAGE MODE)
ORDER BY relevance DESC;

2. 布林模式 - 最強大靈活

支援操作符:

操作符含義範例
+必須包含+MySQL +效能
-必須排除MySQL -MariaDB
*萬用字元(置於詞尾)optimiz* 匹配 optimize, optimization
"精確短語"查詢優化"
~降低相關性~slow 降低包含 slow 的權重
()分組+MySQL +(InnoDB MyISAM)
-- 必須包含 MySQL,排除 MariaDB,優先匹配 "查詢優化"
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+MySQL -MariaDB "查詢優化" optimiz*' IN BOOLEAN MODE);

3. 查詢擴展模式

先做自然語言搜尋,再用高相關文檔的關鍵詞二次搜尋。

SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('資料庫索引' WITH QUERY EXPANSION);

⚠️ 中文全文搜尋關鍵限制與解決方案

核心問題

MySQL 內建 parser **不支援中文分詞**!預設以空格/標點分詞,中文會被視為單一巨大詞元,導致:

  • 搜尋「資料庫」找不到「資料庫設計」
  • 索引體積膨脹、精度極低

解決方案對比

方案原理優點缺點推薦度
ngram parser (MySQL 5.7.6+) N-gram 切分(預設 bigram=2 字) 內建、免安裝、支援 InnoDB/MyISAM 索引大、精確度有限、無法自定義詞典 ⭐⭐⭐ 中小專案首選
MeCab parser (MySQL 5.7.6+) 整合 MeCab 日文/中文分詞庫 專業分詞、支援自定義詞典 需編譯安裝、維護複雜 ⭐⭐⭐⭐ 專業中文搜尋
外部搜尋引擎 Elasticsearch, OpenSearch, Typesense 功能最強、分散式、即時分析 架構複雜、額外維護成本 ⭐⭐⭐⭐⭐ 大規模/高要求
應用層分詞 + 索引表 jieba/THULAC 分詞存入關聯表 完全可控、無資料庫版本限制 開發成本高、需同步維護 ⭐⭐ 特殊需求

ngram 實戰配置

-- my.cnf 配置
[mysqld]
ngram_token_size=2  # 2=bigram(預設), 1=unigram

-- 建立 ngram 全文索引
CREATE TABLE articles_zh (
    id INT PRIMARY KEY,
    content TEXT,
    FULLTEXT INDEX ft_content (content) WITH PARSER ngram
) ENGINE=InnoDB;

-- 搜尋(自動按 2 字切分)
SELECT * FROM articles_zh
WHERE MATCH(content) AGAINST('資料庫優化' IN NATURAL LANGUAGE MODE);

效能優化建議

  • 最小詞長:ft_min_word_len=1 (MyISAM) / innodb_ft_min_token_size=1 (InnoDB)
  • 停用詞表:自定義 ft_stopword_file 排除無意義詞
  • 布林模式 大量資料下比自然語言模式快
  • 避免 SELECT *:只查詢需要欄位,減少 I/O
  • 大量寫入時:先 DROP INDEX,匯入完再 ADD FULLTEXT INDEX

完整 PHP 範例

<?php
$keyword = mysqli_real_escape_string($conn, $_GET['q']);
$sql = "SELECT id, title, LEFT(content, 200) AS excerpt,
               MATCH(title, content) AGAINST(? IN BOOLEAN MODE) AS score
        FROM articles
        WHERE MATCH(title, content) AGAINST(? IN BOOLEAN MODE)
        ORDER BY score DESC
        LIMIT 20";

$stmt = $conn->prepare($sql);
// 布林模式需手動加操作符,這裡簡單示範
$boolKeyword = '+' . str_replace(' ', ' +', $keyword); 
$stmt->bind_param("ss", $boolKeyword, $boolKeyword);
$stmt->execute();
$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
    echo "

{$row['title']}

"; echo "

{$row['excerpt']}... (相關度: {$row['score']})

"; } ?>

總結

MySQL FULLTEXT 適合中小型專案、簡單中文搜尋、預算有限的場景。搭配 ngram parser 可基本滿足中文需求。若需專業中文分詞、同義詞、模糊匹配、高併發、即時索引,請直接上 Elasticsearch/OpenSearch,別在資料庫層硬抗!

copyright © 萬盛學電腦網 all rights reserved