文件首頁
MySQL 8.4 參考手冊
相關文件
MySQL 8.4 版本資訊
下載本手冊
PDF (美式信紙) - 39.9Mb
PDF (A4) - 40.0Mb
手冊頁 (TGZ) - 258.5Kb
手冊頁 (Zip) - 365.5Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb
MySQL 8.4 參考手冊  /  ...  /  布林全文搜尋

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() 的行為。

  • @distance

    此運算子僅適用於 InnoDB 資料表。它測試兩個或多個單字是否都在指定的距離內開始，距離單位為單字。請在 @distance 運算子之前的雙引號字串內指定搜尋單字，例如 MATCH(col1) AGAINST('"word1 word2 word3" @8' IN BOOLEAN MODE)

  • > <

    這兩個運算子用於變更單字對指派給列的相關性值的貢獻。> 運算子會增加貢獻，而 < 運算子會減少貢獻。請參閱此清單後面的範例。

  • ( )

    括號會將單字分組為子運算式。括號群組可以巢狀化。

  • ~

    開頭的波浪符號會作為否定運算子，導致單字對列相關性的貢獻為負值。這對於標記「雜訊」單字很有用。包含此類單字的列會被評為低於其他列，但不會像使用 - 運算子那樣完全排除。

  • *

    星號用作截斷（或萬用字元）運算子。與其他運算子不同，它是附加到要影響的單字。如果單字以 * 運算子之前的單字開頭，則單字會符合。

    如果使用截斷運算子指定單字，即使它太短或是一個停止詞，也不會從布林查詢中刪除。判斷單字是否太短是根據 InnoDB 資料表的 innodb_ft_min_token_size 設定，或 MyISAM 資料表的 ft_min_word_len 設定。這些選項不適用於使用 ngram 剖析器的 FULLTEXT 索引。

    萬用字元單字被視為必須出現在一個或多個單字開頭的前置詞。如果最小單字長度為 4，則搜尋 '+word +the*' 可能會傳回比搜尋 '+word +the' 更少的列，因為第二個查詢會忽略過短的搜尋詞 the。

  • "

    以雙引號 (") 字元括住的片語僅符合包含字面上，依照輸入方式片語的列。全文引擎會將片語拆分為單字，並在 FULLTEXT 索引中搜尋單字。非單字字元不需要完全符合：片語搜尋僅要求符合項包含與片語完全相同的單字，且順序相同。例如，"test phrase" 符合 "test, phrase"。

    如果片語不包含索引中的任何單字，則結果會是空的。單字可能不在索引中，原因可能是多個因素的組合：如果它們不存在於文字中、是停止詞，或是短於索引單字的最小長度。

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

  • 'apple banana'

    尋找至少包含兩個單字之一的列。

  • '+apple +juice'

    尋找包含兩個單字的列。

  • '+apple macintosh'

    尋找包含單字「apple」的列，但如果它們也包含「macintosh」，則將列的排名提高。

  • '+apple -macintosh'

    尋找包含單字「apple」但不包含「macintosh」的列。

  • '+apple ~macintosh'

    尋找包含單字「apple」的列，但如果列也包含單字「macintosh」，則其評級會低於沒有包含的列。這比搜尋 '+apple -macintosh' 來得「溫和」，因為 「macintosh」 的存在會導致列根本不會傳回。

  • '+apple +(>turnover <strudel)'

    尋找包含單字「apple」和「turnover」，或「apple」和「strudel」（以任何順序）的列，但「apple turnover」的排名高於「apple strudel」。

  • 'apple*'

    尋找包含「apple」、「apples」、「applesauce」或「applet」等單字的列。

  • '"some words"'

    尋找包含確切片語「some words」的列（例如，包含「some words of wisdom」但不包含「some noise words」的列）。請注意，括住片語的 " 字元是分隔片語的運算子字元。它們不是括住搜尋字串本身的引號。

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 筆符合「database」搜尋詞。第一筆記錄 (id 6) 包含搜尋詞 6 次，相關性排名為 1.0886961221694946。此排名值是使用 6 的 TF 值（「database」搜尋詞在記錄 id 6 中出現 6 次），以及 0.42596873216370745 的 IDF 值計算得出的，其計算方式如下（其中 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 tutorial」）會傳回以下結果

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」出現一次，「tutorial」出現兩次。「mysql」有六筆相符記錄，「tutorial」有兩筆相符記錄。當將這些值插入多個單字搜尋的排名公式時，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 命令列用戶端傳回的排名值中的些微差異在前面的範例中已說明。


PREV   HOME   UP   NEXT
相關文件
MySQL 8.4 版本資訊
下載本手冊
PDF (美式信紙) - 39.9Mb
PDF (A4) - 40.0Mb
手冊頁 (TGZ) - 258.5Kb
手冊頁 (Zip) - 365.5Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb