傳統 LIKE '%關鍵字%' 在大量資料下效能極差且不支援相關性排序。MySQL 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);
類似搜尋引擎,按相關性排序,忽略常見停用詞。
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;
支援操作符:
| 操作符 | 含義 | 範例 |
|---|---|---|
+ | 必須包含 | +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);
先做自然語言搜尋,再用高相關文檔的關鍵詞二次搜尋。
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 分詞存入關聯表 | 完全可控、無資料庫版本限制 | 開發成本高、需同步維護 | ⭐⭐ 特殊需求 |
-- 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 排除無意義詞DROP INDEX,匯入完再 ADD FULLTEXT INDEX<?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,別在資料庫層硬抗!