MySQL :: MySQL 9.0 參考手冊 :: 14.9.2 布林全文檢索

版本 9.0

8.4 目前
8.0
5.7

8.0 日文

MySQL 9.0 參考手冊 / ... / 布林全文檢索

14.9.2 布林全文檢索

MySQL 可以使用 IN BOOLEAN MODE 修飾詞執行布林全文檢索。使用此修飾詞，某些字元在搜尋字串中字詞的開頭或結尾具有特殊含義。在下列查詢中，+ 和 - 運算子表示為了符合條件，字詞必須存在或不存在。因此，查詢會擷取所有包含字詞「MySQL」，但不包含字詞「YourSQL」的資料列

mysql> SELECT * FROM articles WHERE MATCH (title,body)
    -> AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
+----+-----------------------+-------------------------------------+
| id | title                 | body                                |
+----+-----------------------+-------------------------------------+
|  1 | MySQL Tutorial        | DBMS stands for DataBase ...        |
|  2 | How To Use MySQL Well | After you went through a ...        |
|  3 | Optimizing MySQL      | In this tutorial, we show ...       |
|  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ... |
|  6 | MySQL Security        | When configured properly, MySQL ... |
+----+-----------------------+-------------------------------------+

注意

在實作此功能時，MySQL 使用有時稱為隱含布林邏輯的功能，其中

+ 代表 AND
- 代表 NOT
[無運算子] 表示 OR

布林全文檢索具有下列特性

它們不會自動依相關性遞減的順序排序資料列。
InnoDB 表格需要在 MATCH() 運算式的所有欄上建立 FULLTEXT 索引，才能執行布林查詢。針對 MyISAM 搜尋索引的布林查詢即使沒有 FULLTEXT 索引也能運作，雖然以此方式執行的搜尋會相當緩慢。
最小和最大字詞長度全文參數適用於使用內建 FULLTEXT 剖析器和 MeCab 剖析器外掛程式建立的 FULLTEXT 索引。innodb_ft_min_token_size 和 innodb_ft_max_token_size 用於 InnoDB 搜尋索引。ft_min_word_len 和 ft_max_word_len 用於 MyISAM 搜尋索引。
最小和最大字詞長度全文參數不適用於使用 ngram 剖析器建立的 FULLTEXT 索引。ngram 符記大小是由 ngram_token_size 選項定義。
停用詞清單適用，由 innodb_ft_enable_stopword、innodb_ft_server_stopword_table 和 innodb_ft_user_stopword_table 控制 InnoDB 搜尋索引，並由 ft_stopword_file 控制 MyISAM 搜尋索引。
InnoDB 全文檢索不支援在單一搜尋字詞上使用多個運算子，例如：'++apple'。在單一搜尋字詞上使用多個運算子會傳回標準輸出的語法錯誤。MyISAM 全文檢索會成功處理相同的搜尋，並忽略緊鄰搜尋字詞之外的所有運算子。
InnoDB 全文檢索只支援前置加號或減號。例如，InnoDB 支援 '+apple'，但不支援 'apple+'。指定尾隨的加號或減號會導致 InnoDB 回報語法錯誤。
InnoDB 全文檢索不支援將前置加號與萬用字元 ('+*')、加號和減號組合 ('+-') 或前置的加號和減號組合 ('+-apple') 一起使用。這些無效的查詢會傳回語法錯誤。
InnoDB 全文檢索不支援在布林全文檢索中使用 @ 符號。@ 符號保留給 @distance 相近性搜尋運算子使用。
它們不會使用適用於 MyISAM 搜尋索引的 50% 閾值。

布林全文檢索功能支援下列運算子

+
開頭或結尾的加號表示此單字必須出現在每個傳回的資料列中。InnoDB 僅支援開頭的加號。
-
開頭或結尾的減號表示此單字不得出現在任何傳回的資料列中。InnoDB 僅支援開頭的減號。
注意：- 運算子僅用於排除原本會被其他搜尋條件匹配的資料列。因此，僅包含以 - 開頭的詞彙的布林模式搜尋會傳回空的結果。它不會傳回「除了包含任何排除詞彙之外的所有資料列。」
（無運算子）
預設情況下（當未指定 + 或 - 時），該單字是可選的，但包含該單字的資料列會被評分較高。這模擬了不帶 IN BOOLEAN MODE 修飾符的 MATCH() AGAINST() 的行為。
@距離
此運算子僅適用於 InnoDB 資料表。它會測試兩個或多個單字是否都在指定的距離內開始，距離以單字數衡量。請在 @距離 運算子之前，以雙引號字串指定搜尋的單字，例如 MATCH(col1) AGAINST('"word1 word2 word3" @8' IN BOOLEAN MODE)
> <
這兩個運算子用於更改單字對資料列所賦予的關聯性值的貢獻。> 運算子會增加貢獻，而 < 運算子會減少貢獻。請參閱此列表之後的範例。
( )
括號將單字分組為子表達式。括號分組可以巢狀。
~
開頭的波浪號充當否定運算子，使單字對資料列關聯性的貢獻為負值。這對於標記「雜訊」單字很有用。包含此類單字的資料列評分會低於其他資料列，但不會像使用 - 運算子那樣完全排除。
*
星號充當截斷（或萬用字元）運算子。與其他運算子不同，它是附加到要影響的單字。如果單字以 * 運算子之前的單字開頭，則會符合。
如果單字使用截斷運算子指定，即使它太短或是一個停用詞，也不會從布林查詢中移除。單字是否太短取決於 InnoDB 資料表的 innodb_ft_min_token_size 設定，或 MyISAM 資料表的 ft_min_word_len 設定。這些選項不適用於使用 ngram 剖析器的 FULLTEXT 索引。
帶有萬用字元的單字被視為必須出現在一個或多個單字開頭的前綴。如果最小單字長度為 4，搜尋 '+單字 +the*' 可能會傳回比搜尋 '+單字 +the' 更少的資料列，因為第二個查詢會忽略過短的搜尋詞 the。
"
以雙引號（"）字元括住的片語只會匹配包含文字上，如同輸入一樣的片語的資料列。全文引擎會將片語分割成單字，並在 FULLTEXT 索引中搜尋這些單字。非單字字元不必完全匹配：片語搜尋僅要求匹配項包含與片語完全相同的單字，且順序相同。例如，"測試片語" 會匹配 "測試，片語"。
如果片語中沒有任何單字在索引中，則結果為空。這些單字可能由於以下因素的組合而不在索引中：它們不存在於文字中、是停用詞，或短於索引單字的最小長度。

以下範例示範了一些使用布林全文運算子的搜尋字串

'蘋果香蕉'
尋找包含兩個單字中至少一個的資料列。
'+蘋果 +果汁'
尋找包含兩個單字的資料列。
'+蘋果 macintosh'
尋找包含「蘋果」單字的資料列，但如果也包含「macintosh」，則會將資料列的排名提高。
'+蘋果 -macintosh'
尋找包含「蘋果」單字但不包含「macintosh」的資料列。
'+蘋果 ~macintosh'
尋找包含「蘋果」單字的資料列，但如果資料列也包含「macintosh」單字，則將其評分低於不包含該單字的資料列。這比搜尋 '+apple -macintosh' 來的「柔和」，因為對於 '+apple -macintosh'，「macintosh」的存在會導致該資料列完全不傳回。
'+蘋果 +(>營業額 <蘋果捲)'
尋找包含「蘋果」和「營業額」或「蘋果」和「蘋果捲」單字的資料列（順序不拘），但「蘋果營業額」的排名會高於「蘋果蘋果捲」。
'蘋果*'
尋找包含諸如「蘋果」、「蘋果們」、「蘋果醬」或「小蘋果」等單字的資料列。
'"一些文字"'
尋找包含確切片語「一些文字」的資料列（例如，包含「一些智慧文字」但不包含「一些雜訊文字」的資料列）。請注意，括住片語的 " 字元是界定片語的運算子字元。它們不是括住搜尋字串本身的引號。

InnoDB 布林模式搜尋的關聯性排名

InnoDB 全文搜尋以 Sphinx 全文搜尋引擎為模型，且使用的演算法基於 BM25 和 TF-IDF 排名演算法。因此，InnoDB 布林全文搜尋的關聯性排名可能與 MyISAM 關聯性排名不同。

InnoDB 使用「詞彙頻率-反向文件頻率」（TF-IDF）加權系統的變體，來對文件針對給定的全文搜尋查詢的相關性進行排名。TF-IDF 加權基於單字在文件中出現的頻率，並由單字在集合中所有文件中出現的頻率抵銷。換句話說，單字在文件中出現的頻率越高，且單字在文件集合中出現的頻率越低，則文件的排名越高。

如何計算關聯性排名

詞彙頻率（TF）值是單字在文件中出現的次數。單字的反向文件頻率（IDF）值使用以下公式計算，其中 total_records 是集合中的記錄數，而 matching_records 是搜尋詞彙出現的記錄數。

${IDF} = log10( ${total_records} / ${matching_records} )

當文件多次包含單字時，IDF 值會乘以 TF 值

${TF} * ${IDF}

使用 TF 和 IDF 值，可以使用此公式計算文件的關聯性排名

${rank} = ${TF} * ${IDF} * ${IDF}

以下範例示範了該公式。

單字搜尋的關聯性排名

此範例示範了單字搜尋的關聯性排名計算。

mysql> CREATE TABLE articles (
    ->   id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ->   title VARCHAR(200),
    ->   body TEXT,
    ->   FULLTEXT (title,body)
    ->)  ENGINE=InnoDB;
Query OK, 0 rows affected (1.04 sec)

mysql> INSERT INTO articles (title,body) VALUES
    ->   ('MySQL Tutorial','This database tutorial ...'),
    ->   ("How To Use MySQL",'After you went through a ...'),
    ->   ('Optimizing Your Database','In this database tutorial ...'),
    ->   ('MySQL vs. YourSQL','When comparing databases ...'),
    ->   ('MySQL Security','When configured properly, MySQL ...'),
    ->   ('Database, Database, Database','database database database'),
    ->   ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    ->   ('MySQL Full-Text Indexes', 'MySQL fulltext indexes use a ..');
Query OK, 8 rows affected (0.06 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> SELECT id, title, body, 
    ->   MATCH (title,body) AGAINST ('database' IN BOOLEAN MODE) AS score
    ->   FROM articles ORDER BY score DESC;
+----+------------------------------+-------------------------------------+---------------------+
| id | title                        | body                                | score               |
+----+------------------------------+-------------------------------------+---------------------+
|  6 | Database, Database, Database | database database database          |  1.0886961221694946 |
|  3 | Optimizing Your Database     | In this database tutorial ...       | 0.36289870738983154 |
|  1 | MySQL Tutorial               | This database tutorial ...          | 0.18144935369491577 |
|  2 | How To Use MySQL             | After you went through a ...        |                   0 |
|  4 | MySQL vs. YourSQL            | When comparing databases ...        |                   0 |
|  5 | MySQL Security               | When configured properly, MySQL ... |                   0 |
|  7 | 1001 MySQL Tricks            | 1. Never run mysqld as root. 2. ... |                   0 |
|  8 | MySQL Full-Text Indexes      | MySQL fulltext indexes use a ..     |                   0 |
+----+------------------------------+-------------------------------------+---------------------+
8 rows in set (0.00 sec)

總共有 8 筆記錄，其中 3 筆記錄符合「資料庫」搜尋詞彙。第一筆記錄（id 6）包含搜尋詞彙 6 次，關聯性排名為 1.0886961221694946。此排名值是使用 TF 值 6（「資料庫」搜尋詞彙在記錄 id 6 中出現 6 次）和 IDF 值 0.42596873216370745 計算得出，計算方式如下（其中 8 是記錄總數，而 3 是搜尋詞彙出現的記錄數）

${IDF} = LOG10( 8 / 3 ) = 0.42596873216370745

然後將 TF 和 IDF 值輸入到排名公式中

${rank} = ${TF} * ${IDF} * ${IDF}

在 MySQL 命令列用戶端中執行計算會傳回 1.088696164686938 的排名值。

mysql> SELECT 6*LOG10(8/3)*LOG10(8/3);
+-------------------------+
| 6*LOG10(8/3)*LOG10(8/3) |
+-------------------------+
|       1.088696164686938 |
+-------------------------+
1 row in set (0.00 sec)

注意

您可能會注意到 SELECT ... MATCH ... AGAINST 陳述式和 MySQL 命令列用戶端傳回的排名值略有不同（1.0886961221694946 對 1.088696164686938）。差異的原因是 InnoDB 內部執行整數與浮點數/雙精度數之間的轉換方式（以及相關的精確度和捨入決策），以及在其他地方（例如 MySQL 命令列用戶端或其他類型的計算機中）執行的方式。

多字搜尋的關聯性排名

此範例示範了基於先前範例中使用的 articles 資料表和資料的多字全文搜尋的關聯性排名計算。

如果搜尋多個單字，則關聯性排名值是每個單字的關聯性排名值的總和，如此公式所示

${rank} = ${TF} * ${IDF} * ${IDF} + ${TF} * ${IDF} * ${IDF}

搜尋兩個詞彙（'mysql 教學'）會傳回以下結果

mysql> SELECT id, title, body, MATCH (title,body)  
    ->   AGAINST ('mysql tutorial' IN BOOLEAN MODE) AS score
    ->   FROM articles ORDER BY score DESC;
+----+------------------------------+-------------------------------------+----------------------+
| id | title                        | body                                | score                |
+----+------------------------------+-------------------------------------+----------------------+
|  1 | MySQL Tutorial               | This database tutorial ...          |   0.7405621409416199 |
|  3 | Optimizing Your Database     | In this database tutorial ...       |   0.3624762296676636 |
|  5 | MySQL Security               | When configured properly, MySQL ... | 0.031219376251101494 |
|  8 | MySQL Full-Text Indexes      | MySQL fulltext indexes use a ..     | 0.031219376251101494 |
|  2 | How To Use MySQL             | After you went through a ...        | 0.015609688125550747 |
|  4 | MySQL vs. YourSQL            | When comparing databases ...        | 0.015609688125550747 |
|  7 | 1001 MySQL Tricks            | 1. Never run mysqld as root. 2. ... | 0.015609688125550747 |
|  6 | Database, Database, Database | database database database          |                    0 |
+----+------------------------------+-------------------------------------+----------------------+
8 rows in set (0.00 sec)

在第一筆記錄（id 8）中，'mysql' 出現一次，而 '教學' 出現兩次。有六筆符合 'mysql' 的記錄和兩筆符合 '教學' 的記錄。當將這些值插入多字搜尋的排名公式時，MySQL 命令列用戶端會傳回預期的排名值

mysql> SELECT (1*log10(8/6)*log10(8/6)) + (2*log10(8/2)*log10(8/2));
+-------------------------------------------------------+
| (1*log10(8/6)*log10(8/6)) + (2*log10(8/2)*log10(8/2)) |
+-------------------------------------------------------+
|                                    0.7405621541938003 |
+-------------------------------------------------------+
1 row in set (0.00 sec)

注意

先前範例中說明了 SELECT ... MATCH ... AGAINST 陳述式和 MySQL 命令列用戶端傳回的排名值的微小差異。