全文搜尋函數

MATCH (col1,col2,...) AGAINST (expr [IN BOOLEAN MODE | WITH QUERY EXPANSION])

MATCH()函數對一個字串進行資料庫內的自然語言搜索。一個資料集就是1個在FULLTEXT內包含有1個或2個資料列的集合。搜索字串由AGAINST()的參數指定。對於資料表中的每一行, MATCH() 回傳一個相關值,搜索字串和 MATCH()表中指定列中該行文字之間的一個相似性度量。

MySQL支援全文索引和搜索功能。MySQL中的全文索引是FULLTEXT index。 FULLTEXT 索引只能用在 MyISAM 資料表;建立資料表的時候,可以在 CREATE TABLE 命令中建立CHAR、 VARCHAR或TEXT列作為全文搜尋索引,或是隨後使用ALTER TABLE 或 CREATE INDEX加入全文搜尋索引。對於較大的數據集,將你的資料輸入一個沒有FULLTEXT索引的表中,然後建立索引,其速度比把資料輸入已經設定好FULLTEXT索引的速度更快。

全文搜索必須與MATCH()函數一起使用。
mysql> CREATE TABLE articles (
-> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
-> title VARCHAR(200),
-> body TEXT,
-> FULLTEXT (title,body)
-> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO articles (title,body) VALUES
-> ('MySQL Tutorial','DBMS stands for DataBase ...'),
-> ('How To Use MySQL Well','After you went through a ...'),
-> ('Optimizing MySQL','In this tutorial we will show ...'),
-> ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
-> ('MySQL vs. YourSQL','In the following database comparison ...'),
-> ('MySQL Security','When configured properly, MySQL ...');
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM articles
-> WHERE MATCH (title,body) AGAINST ('database');
+----+-------------------+------------------------------------------+
| id | title | body |
+----+-------------------+------------------------------------------+
| 5 | MySQL vs. YourSQL | In the following database comparison ... |
| 1 | MySQL Tutorial | DBMS stands for DataBase ... |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

在默認狀態下, 搜索的執行方式為不區分大小寫方式。然而,可以透過對編入索引的列使用二進制排序方式執行區分大小寫的全文搜索。例如,可以向一個使用latin1字元集的列給定latin1_bin 的排序方式,對全文搜索區分大小寫。

如上述所舉例子,當MATCH()被用在一個 WHERE 語句中時,相關值是非負浮點數。零相關的意思是沒有相似性。相關性的計算是基於該行中單詞的數目、該行中單獨字的數目,資料庫中單詞的總數,以及包含特殊詞的文件(行)數目。
要簡單的知道搜尋結果數量可以用下面的查詢:
mysql> SELECT COUNT(*) FROM articles
-> WHERE MATCH (title,body)
-> AGAINST ('database' IN NATURAL LANGUAGE MODE);
+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)

你可以將查詢語句改成下面:
mysql> SELECT
-> COUNT(IF(MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE), 1, NULL))
-> AS count
-> FROM articles;
+-------+
| count |
+-------+
| 2 |
+-------+
1 row in set (0.03 sec)

第一個查詢會因為集合函數會對比對結果關連性進行排序,第二個查詢則不會。但是第二個查詢會對整個資料表進行比對,第一個則不會。在資料列比較少的情況下,使用第一個查詢也許會比較快一些,但是第二個查詢在資料列比較多的狀況下會比較快,因為資料列多的時候,不管什麼樣的查詢都會讀取許多列。

對於自然語言全文搜索,要求MATCH()函數中指定的列和你的表中的FULLTEXT索引包含的列相同。對於前述的查詢,注意,MATCH()函數(標題及全文)中所命名的列和文章表的FULLTEXT索引中的列相同。若要分別搜索標題和全文,應該對每個列建立FULLTEXT索引。
或者也可以運行Boolean搜索或使用查詢擴展進行搜索。

全文搜尋時MATCH()函數使用的索引只能在同一個資料表裡,因為建立索引不能跨資料表。Boolean搜尋能用在未索引的欄位(雖然比較慢),也就是說它能夠用在多個資料表中的欄位進行查詢。

前面的示範是一個關於MATCH()使用方式的基本描述,與資料列的關連性比較沒有關係。接下來的示範是展示如何查詢明確的關連性值。因為SELECT語句未包含WHERE和ORDER BY語句,因此回傳未排序的資料列。
mysql> SELECT id, MATCH (title,body) AGAINST ('Tutorial')
-> FROM articles;
+----+-----------------------------------------+
| id | MATCH (title,body) AGAINST ('Tutorial') |
+----+-----------------------------------------+
| 1 | 0.65545833110809 |
| 2 | 0 |
| 3 | 0.66266459226608 |
| 4 | 0 |
| 5 | 0 |
| 6 | 0 |
+----+-----------------------------------------+
6 rows in set (0.00 sec)

下面的範例更複雜,這個查詢回傳關連性值並且使用關連性值遞減排序資料列。為了這個結果,必須使用兩次MACTH():一次是在SELECT列出關連性,第二次是在WHERE做為條件。這樣不會造成重覆運算,MySQL最佳化時已經考慮到兩個相同參數的MATCH()同時呼叫的情況,只會進行一次檢索。
mysql> SELECT id, body, MATCH (title,body) AGAINST
-> ('Security implications of running MySQL as root') AS score
-> FROM articles WHERE MATCH (title,body) AGAINST
-> ('Security implications of running MySQL as root');
+----+-------------------------------------+-----------------+
| id | body | score |
+----+-------------------------------------+-----------------+
| 4 | 1. Never run mysqld as root. 2. ... | 1.5219271183014 |
| 6 | When configured properly, MySQL ... | 1.3114095926285 |
+----+-------------------------------------+-----------------+

MySQL FULLTEXT 執行將任何單字字元原形 (字母、數字和下劃線部分)的序列視為一個單詞。這個序列或許也包含單引號 ('),但在一行中不會超過一個。 這意味著 aaa'bbb 會被視為一個單詞,而 aaa''bbb則被視為2個單詞。位於單詞之前或其後的單引號會被FULLTEXT分析程序去掉; 'aaa'bbb' 會變成 aaa'bbb。

FULLTEXT分析程序會通過尋找某些分隔符來確定單詞的起始位置和結束位置,例如' ' (間隔符號)、 , (逗號)以及 . (句號 )。假如單詞沒有被分隔符分開,(例如在中文裡 ), 則 FULLTEXT 分析程序不能確定一個詞的起始位置和結束位置。為了能夠在這樣的語言中向FULLTEXT 索引添加單詞或其它編入索引的術語,你必須對它們進行預處理,使其被一些諸如"之類的任意分隔符分隔開。
一些詞在全文搜索中會被忽略:
  • 任何過於短的詞都會被忽略。 全文搜索所能找到的詞的默認最小長度為 4個字元。
  • 停止字中的詞會被忽略。禁用詞就是一個像「the」 或「some」 這樣過於平常而被認為是不具語義的詞。存在一個內置的停止字, 但它可以通過用戶自定義列表被改寫。

詞庫和詢問中每一個正確的單詞根據其在詞庫和詢問中的重要性而被衡量。通過這種方式,一個出現在許多文件中的單詞具有較低的重要性(而且甚至很多單詞的重要性為零),原因是在這個特別詞庫中其語義價值較低。反之,假如這個單詞比較少見,那麼它會得到一個較高的重要性。然後單詞的重要性被組合,從而用來計算該行的相關性。

這項技術最適合同大型詞庫一起使用 (事實上, 此時它經過仔細的調整 )。對於很小的表,單詞分佈並不能充分反映它們的語義價值, 而這個模式有時可能會產生奇特的結果。例如, 雖然單詞 「MySQL」 出現在文章表中的每一行,但對這個詞的搜索可能得不到任何結果:
mysql> SELECT * FROM articles
-> WHERE MATCH (title,body) AGAINST ('MySQL');
Empty set (0.00 sec)

這個搜索的結果為空,原因是單詞 「MySQL」 出現在至少全文的50%的行中。 因此, 它被列入停止字。對於大型數據集,使用這個操作最合適不過了----一個自然語言問詢不會從一個1GB 的表每隔一行回傳一次。對於小型數據集,它的用處可能比較小。

一個符合表中所有行的內容的一半的單詞查找相關文檔的可能性較小。事實上, 它更容易找到很多不相關的內容。我們都知道,當我們在因特網上試圖使用搜索引擎尋找資料的時候,這種情況發生的頻率頗高。可以推論,包含該單詞的行因其所在特別數據集 而被賦予較低的語義價值。 一個給定的詞有可能在一個數據集中擁有超過其50%的域值,而在另一個數據集卻不然。

全文搜尋的限制

  • 全文搜索只適用於 MyISAM 資料表。
  • 全文搜索可以同大多數多字節字元集一起使用。Unicode 屬於例外情況,可使用 utf8 字元集 , 而非 ucs2 字元集。
  • 諸如漢語和日語這樣的表意語言沒有文字分界符號。因此, 在這些或其它的這類語言中 FULLTEXT 分析程序不能確定詞的起始和結束的位置。
  • 若支援在一個單獨資料表中使用多字元集,則所有 FULLTEXT 索引中的列 必須使用同樣的字元集和對照方式。
  • MATCH()資料列列表必須與該資料表中 FULLTEXT 索引定義中的資料列列表完全相同,除非MATCH()在IN BOOLEAN MODE。
  • AGAINST() 的參數必須是一個常數字串。

AES_DECRYPT() 最安全的解密函數

AES_DECRYPT(crypt_str,key_str)
  • crypt_str 要解密的密文
  • key_str 解密密碼

這個函數允許使用官方AES(Advanced Encryption Standard)演算法進行資料加密和解密,就是有名的「Rijndael」。 保密金鑰的長度為128bit,不過你可以透過修改原始檔將其延伸到 256bit。使用128bit的原因是它的速度要快得多,且對於大多數用途而言這個保密程度已經足夠。
如果 AES_DECRYPT() 發現不合法的資料或不合法的填充字,將會回傳 NULL。當輸入的資料或金鑰是錯誤時,AES_DECRYPT()還是有可能回傳non-NULL的資料(有可能是垃圾資料)。
AES_ENCRYPT()和AES_DECRYPT() 可以被視為 MySQL 中普遍通用最安全的密碼的加密函數。

AES_ENCRYPT() 最安全的加密函數

AES_ENCRYPT(str,key_str)
  • str 傳入的明文
  • key_str 保密金鑰

這個函數允許使用官方AES(Advanced Encryption Standard)演算法進行資料加密和解密,就是有名的「Rijndael」。 保密金鑰的長度為128bit,不過你可以透過修改原始檔將其延伸到 256bit。使用128bit的原因是它的速度要快得多,且對於大多數用途而言這個保密程度已經足夠。
輸入參數可以為任何長度。若任何一個參數為NULL,則函數的結果也是NULL。
因為 AES 是以區塊為基礎的演算法,將會把不均衡長度字串補滿至可以編碼為止,回傳值長度的計算方式是:
16 * (trunc(string_length / 16) + 1)。
可以透過修改查詢文字將資料加密儲存在你的欄位裡面。
INSERT INTO t VALUES (1,AES_ENCRYPT('text','password'));
AES_ENCRYPT()和AES_DECRYPT() 可以被視為 MySQL 中普遍通用最安全的密碼的加密函數。

DECODE() 解密函數

DECODE(crypt_str,pass_str)
  • crypt_str 傳入的密文字串
  • pass_str 密碼字串

使用 pass_str 作為解密密碼,解密被加密的字串 crypt_str, crypt_str 應該是由ENCODE()回傳的字串。

ENCODE() 加密函數

ENCODE(str,pass_str)
  • str 輸入的明文字串
  • pass_str 密碼字串

使用 pass_str 作為加密密碼,回傳值是一個和str長度相同的二進制字串。若你想要將結果儲存在一個列中,可使用 BLOB 列型態。
使用DECODE()解密。

DES_DECRYPT() 解密函數

DES_DECRYPT(crypt_str[,key_str])
  • crypt_str 加密後的密文
  • key_str 解密金鑰

使用DES_ENCRYPT()加密一個字串。若出現錯誤,這個函數會回傳 NULL。
注意,這個函數只有當MySQL在支援SSL的環境下才能運作。
假如沒有提供 key_str 參數,DES_DECRYPT() 會首先檢查加密字串的第一個字元,來確定用來加密原始字串的DES密碼金鑰數字,之後從DES金鑰檔案中讀取金鑰並解密資料。為了讓解密能夠運作,使用者必須有 SUPER 權限。可以在伺服器選項--des-key-file服務器指定金鑰檔案。
假如你向這個函數傳遞一個 key_str 參數,該字串被用作解密資料的金鑰。
若 crypt_str 參數看起來不是一個加密字串, MySQL 會回傳原來的 crypt_str。