SQL 語法 SELECT Full Text Index

Loading

SQL 可以透過 SELECT + WHERE + LIKE 可以針對 CHAR, VARCHAR, 以及 TEXT 指定欄位 (COLUMN) 做關鍵字來比對搜尋 ( 需注意資料庫字元編碼 Character Sets 與 文字排序 Collations ,請參考資料庫編碼 – https://benjr.tw/102156 ).

MariaDB [testdb]> SELECT * FROM Employee WHERE Expertise LIKE '%python%';

但比對的時候只能針對指定欄位來搜尋 (Search) ,有辦法可以針對所有欄位來做搜尋嗎? 這時後可以透過建立 Full Text Index 來做.在使用 SELECT 做搜尋資料時搭配 MATCH (col1,col2,…) AGAINST (expr [search_modifier]) 即可針對已建立 Full Text Index 的欄位來搜尋.

測試環境為 CentOS 7 x86_64 (虛擬機)

在測試 SELECT MATCH (col1,col2,…) AGAINST (expr [search_modifier]) 前,先來建立一下資料表,需注意我這邊 ENGINE 選擇的是 MyISAM, InnoDB 需要到 MySQL 5.6 之後的版本才有支援 FULLTEXT indexes.

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> USE testdb;
Database changed

建立資料表時一併建立 Full Text Index .

MariaDB [testdb]> CREATE TABLE Employee (Name char(20),Dept char(20),JobTitle char(20),Expertise varchar(99) , Salary int(11),FULLTEXT Index1(Name , Dept , JobTitle , Expertise)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

檢視一下剛剛建好的 Table 是否正確.

MariaDB [testdb]> SHOW TABLE STATUS\G
*************************** 1. row ***************************
           Name: Employee
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 281474976710655
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2019-06-13 21:05:22
    Update_time: 2019-06-13 21:05:22
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.01 sec)

如果是已經建立好的 Database.Table 事後要再建 FULL TEXT 可以透過以下的方式.

MariaDB [testdb]> ALTER TABLE Employee ADD FULLTEXT Index1 (Name , Dept , JobTitle , Expertise);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0
MariaDB [testdb]> DESCRIBE Employee; 
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| Name      | char(20)    | YES  | MUL | NULL    |       |
| Dept      | char(20)    | YES  |     | NULL    |       |
| JobTitle  | char(20)    | YES  |     | NULL    |       |
| Expertise | varchar(99) | YES  |     | NULL    |       |
| Salary    | int(11)     | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

MariaDB [testdb]> SHOW TABLES;
+------------------+
| Tables_in_testdb |
+------------------+
| Employee         |
+------------------+
1 row in set (0.00 sec)

MariaDB [testdb]> SHOW INDEX FROM Employee;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Employee |          1 | Index1   |            1 | Name        | NULL      |        NULL |     NULL | NULL   | YES  | FULLTEXT   |         |               |
| Employee |          1 | Index1   |            2 | Dept        | NULL      |        NULL |     NULL | NULL   | YES  | FULLTEXT   |         |               |
| Employee |          1 | Index1   |            3 | JobTitle    | NULL      |        NULL |     NULL | NULL   | YES  | FULLTEXT   |         |               |
| Employee |          1 | Index1   |            4 | Expertise   | NULL      |        NULL |     NULL | NULL   | YES  | FULLTEXT   |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

建立資料表時沒指定 Engine 時,預設使用的是 InnoDB ,要修改成為 MyISAM 的方式如下.

先透過以下的指令來看你的資料庫支援哪一些 Engine .

MariaDB [testdb]> SHOW ENGINES\G

透過下面指令可以修改 Engine

MariaDB [testdb]> ALTER TABLE Employee ENGINE = MYISAM;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

先塞入一些簡單資料.

MariaDB [testdb]> INSERT INTO Employee VALUES ('Ben' , 'Validation' , 'Sensor Engineer' , 'Linux Redhat RHEL Ubuntu MySQL MariaDB' , '54000');
Query OK, 1 row affected (0.00 sec)

MariaDB [testdb]> INSERT INTO Employee VALUES ('Cars' , 'Validation' , 'Senior Engineer', 'Linux Kernel Driver Redhat RHEL Ubuntu' , '52000');
Query OK, 1 row affected (0.01 sec)

MariaDB [testdb]> INSERT INTO Employee VALUES ('Joe' , 'Validation' , 'Engineer' , 'Windows PowerShell PHP Python Javascript CSS' , '48000');
Query OK, 1 row affected (0.00 sec)

MariaDB [testdb]> INSERT INTO Employee VALUES ('Bob' , 'Validation' , 'Engineer' , 'Windows Linux HTML Javascript CSS MariaDB' ,'45000');
Query OK, 1 row affected (0.01 sec)

透過一般的搜尋方式.

MariaDB [testdb]> SELECT * FROM Employee WHERE Expertise LIKE '%python%';
+------+------------+----------+----------------------------------------------+--------+
| Name | Dept       | JobTitle | Expertise                                    | Salary |
+------+------------+----------+----------------------------------------------+--------+
| Joe  | Validation | Engineer | Windows PowerShell PHP Python Javascript CSS |  48000 |
+------+------------+----------+----------------------------------------------+--------+
1 row in set (0.01 sec)

使用 Full Text Index 的搜尋方式.

MariaDB [testdb]> SELECT * FROM Employee WHERE MATCH (Name , Dept , JobTitle , Expertise) AGAINST ('python');
+------+------------+----------+----------------------------------------------+--------+
| Name | Dept       | JobTitle | Expertise                                    | Salary |
+------+------------+----------+----------------------------------------------+--------+
| Joe  | Validation | Engineer | Windows PowerShell PHP Python Javascript CSS |  48000 |
+------+------------+----------+----------------------------------------------+--------+
1 row in set (0.00 sec)

Full Text Index 搜尋模式有三種,在使用前先來看一下哪些搜尋及其結果會被排除.

  1. FULLTEXT 斷字 (資料庫內容的資料,哪些符號會讓字串變成斷字)
    1. 單詞資料包含 字母 (Letter) ,數字 (Digit) 和下劃線( _ , underscore) 都會當作為單一詞 (PHP_Python 視同為一個單詞,搜尋 PHP 或是 Python 會找不到該筆資料,使用 IN BOOLEAN MODE + * ,搜尋關鍵字 PHP* 則可以找到).
    2. 空白 , 逗號 (,) , 與點 (.) 字串會被斷成兩個單詞 (如 : Chris Hemsworth , Chris,Hemsworth , Chris.Hemsworth 都是兩個單詞,搜尋可以使用這兩個單詞 Chris 與 Hemsworth 來找資料),如不想把含有 點 (.) 的字串被斷成兩個單詞,可改用 IN BOOLEAN MODE + “keyword” (雙引號) 來搜尋.
  2. 預設搜尋關鍵字長度小於 4 個字符(包4個)的單詞會被忽略掉,不列入搜尋關鍵字中,可以更改 ft_min_word_length 來調整此值.
  3. 預設搜尋關鍵字長度超過 84 個字符的單詞會被忽略掉,不列入搜尋關鍵字中,可以更改 ft_max_word_length 來調整此值.
    修改 ft_min_word_length 與 ft_max_word_length 需編輯 /etc/my.cnf 在 [mysqld] 區塊下加入.

    [root@localhost ~]# vi /etc/my.cnf
    [mysqld]
    ft_min_word_len = 3
    ft_max_word_len = 50
    [root@localhost ~]# systemctl restart mariadb
    

    Index 須重建才會生效.

    MariaDB [testdb]> REPAIR TABLE Employee;
    +-----------------+--------+----------+----------+
    | Table           | Op     | Msg_type | Msg_text |
    +-----------------+--------+----------+----------+
    | testdb.Employee | repair | status   | OK       |
    +-----------------+--------+----------+----------+
    1 row in set (0.01 sec)
    

    可以透過下面方式來檢視.

    MariaDB [testdb]> SHOW VARIABLEs LIKE '%word_len%';
    +-----------------+-------+
    | Variable_name   | Value |
    +-----------------+-------+
    | ft_max_word_len | 50    |
    | ft_min_word_len | 3     |
    +-----------------+-------+
    2 rows in set (0.01 sec)
    
  4. 停用詞 (Stopwords – https://mariadb.com/kb/en/library/full-text-index-stopwords/ ) 當關鍵字搜尋時會被忽略掉,可改用 IN BOOLEAN MODE.
  5. 如果一個關鍵字搜尋出來的結果,佔所有資料的一半時則不顯示其搜索結果,可改用 IN BOOLEAN MODE.

Full-Text search 模式

Employee 資料表如下.

MariaDB [testdb]> select * from Employee;
+------+------------+-----------------+----------------------------------------------+--------+
| Name | Dept       | JobTitle        | Expertise                                    | Salary |
+------+------------+-----------------+----------------------------------------------+--------+
| Ben  | Validation | Sensor Engineer | Linux Redhat RHEL Ubuntu MySQL MariaDB       |  54000 |
| Cars | Validation | Senior Engineer | Linux Kernel Driver Redhat RHEL Ubuntu       |  52000 |
| Joe  | Validation | Engineer        | Windows PowerShell PHP Python Javascript CSS |  48000 |
| Bob  | Validation | Engineer        | Windows Linux HTML Javascript CSS MariaDB    |  45000 |
+------+------------+-----------------+----------------------------------------------+--------+
4 rows in set (0.00 sec)
  • NATURAL LANGUAGE MODE (預設)
    同時符合 python php 兩個關鍵字的紀錄 (Record).

    MariaDB [testdb]> SELECT * FROM Employee WHERE MATCH (Name , Dept , JobTitle , Expertise) AGAINST ('python php');
    MariaDB [testdb]> SELECT * FROM Employee WHERE MATCH (Name , Dept , JobTitle , Expertise) AGAINST ('python php' IN NATURAL LANGUAGE MODE);
    +------+------------+----------+----------------------------------------------+--------+
    | Name | Dept       | JobTitle | Expertise                                    | Salary |
    +------+------------+----------+----------------------------------------------+--------+
    | Joe  | Validation | Engineer | Windows PowerShell PHP Python Javascript CSS |  48000 |
    +------+------------+----------+----------------------------------------------+--------+
    1 row in set (0.00 sec)
    

    奇怪搜尋 Linux 的卻沒有顯示任何資料,因為 Linux 這一個關鍵字在資料庫中有一半以上資料都包含,所以不顯示其搜索結果,可改用 IN BOOLEAN MODE.

    MariaDB [testdb]> SELECT * FROM Employee WHERE MATCH (Name , Dept , JobTitle , Expertise) AGAINST ('linux');
    Empty set (0.00 sec)
    
  • BOOLEAN MODE
    剛剛使用 NATURAL LANGUAGE MODE ,搜尋 Linux 這關鍵字無顯示其搜索結果(資料庫中有一半以上資料都包含),用 IN BOOLEAN MODE 可正常顯示.

    MariaDB [testdb]> SELECT * FROM Employee WHERE MATCH (Name , Dept , JobTitle , Expertise) AGAINST ('linux' IN BOOLEAN MODE);
    +------+------------+-----------------+-------------------------------------------+--------+
    | Name | Dept       | JobTitle        | Expertise                                 | Salary |
    +------+------------+-----------------+-------------------------------------------+--------+
    | Ben  | Validation | Sensor Engineer | Linux Redhat RHEL Ubuntu MySQL MariaDB    |  54000 |
    | Cars | Validation | Senior Engineer | Linux Kernel Driver Redhat RHEL Ubuntu    |  52000 |
    | Bob  | Validation | Engineer        | Windows Linux HTML Javascript CSS MariaDB |  45000 |
    +------+------------+-----------------+-------------------------------------------+--------+
    3 rows in set (0.00 sec)
    

    BOOLEAN MODE 當有多關鍵字時,可以指定關鍵字搜尋語法 (Operator).

    MariaDB [testdb]> SELECT * FROM Employee WHERE MATCH (Name , Dept , JobTitle , Expertise) AGAINST ('+javascript -php' IN BOOLEAN MODE);
    +------+------------+----------+------------------------------------------+--------+
    | Name | Dept       | JobTitle | Expertise                                | Salary |
    +------+------------+----------+------------------------------------------+--------+
    | Bob  | Validation | Engineer | Windows Linux HTML Javascript CSS MariaDB |  45000 |
    +------+------------+----------+------------------------------------------+--------+
    1 row in set (0.00 sec)
    
    Operator Description
    不指定 任意含有搜尋字串的資料.
    ‘javascript php’ – 搜尋包含 javascript 或是 php 的資料(包含兩種都成立的).
    + 資料一定要包含該搜尋字串.
    ‘+javascript +php’ – 同時包含 javascript 與 php 這兩個單詞的資料.
    ‘+javascript php’ – 搜索包含單詞 javascript 的資料,但如果它們也包含 php ,則相關性排名更高.
    MariaDB [testdb]> SELECT Expertise,MATCH (Name , Dept , JobTitle , Expertise) AGAINST ('+javascript php' IN BOOLEAN MODE) AS RELEVANCE FROM Employee;
    +----------------------------------------------+--------------------+
    | Expertise                                    | RELEVANCE          |
    +----------------------------------------------+--------------------+
    | Linux Redhat RHEL Ubuntu MySQL MariaDB       |                  0 |
    | Linux Kernel Driver Redhat RHEL Ubuntu       |                  0 |
    | Windows PowerShell PHP Python Javascript CSS | 1.3333333730697632 |
    | Windows Linux HTML Javascript CSS MariaDB    |                  1 |
    +----------------------------------------------+--------------------+
    4 rows in set (0.00 sec)
    
    資料不能包含該搜尋字串.
    ‘+javascript -php’ – 搜尋包含 javascript 但不包含 php 的資料.
    < 該搜尋字串比其他字串具有較低的相關性.
    ‘+javascript +(<php >linux)’ – 資料包含 javascript 與 linux 的相關性會高於 javascript php.
    MariaDB [testdb]> SELECT Expertise,MATCH (Name , Dept , JobTitle , Expertise) AGAINST ('+javascript +(<php >linux)' IN BOOLEAN MODE) AS RELEVANCE FROM Employee;
    +----------------------------------------------+--------------------+
    | Expertise                                    | RELEVANCE          |
    +----------------------------------------------+--------------------+
    | Linux Redhat RHEL Ubuntu MySQL MariaDB       |                  0 |
    | Linux Kernel Driver Redhat RHEL Ubuntu       |                  0 |
    | Windows PowerShell PHP Python Javascript CSS | 0.8333333730697632 |
    | Windows Linux HTML Javascript CSS MariaDB    |               1.25 |
    +----------------------------------------------+--------------------+
    4 rows in set (0.00 sec)
    
    > 該搜尋字串比其他字串具有更高的相關性,範例如上.
    () 用於子表達式時,範例如上.
    ~ ~後面接的的單詞會得到較低的相關性 (不同於 – 運算符明確地排除了該單詞).
    +javascript ~php – 搜尋包含 javascript 或是 php 的資料(包含兩種都成立的),但包含 php 會得到較低的相關性.
    MariaDB [testdb]> SELECT Expertise,MATCH (Name , Dept , JobTitle , Expertise) AGAINST ('+javascript ~php' IN BOOLEAN MODE) AS RELEVANCE FROM Employee;
    +----------------------------------------------+--------------------+
    | Expertise                                    | RELEVANCE          |
    +----------------------------------------------+--------------------+
    | Linux Redhat RHEL Ubuntu MySQL MariaDB       |                  0 |
    | Linux Kernel Driver Redhat RHEL Ubuntu       |                  0 |
    | Windows PowerShell PHP Python Javascript CSS | 0.8333333134651184 |
    | Windows Linux HTML Javascript CSS MariaDB    |                  1 |
    +----------------------------------------------+--------------------+
    4 rows in set (0.00 sec)
    
    * 萬用字元 * 用以表示零個或多個字符,但只能出現在一個單詞的末尾,功能同傳統 SQL 資料搜尋時 Like 搭配百分號(%).
    java* – 以 java 開頭的任何資料.
    “雙引號”內包含的任何內容都被視為一個整體,可以避免掉 含有點 (.) 或是 空白 的字串會被斷成兩個單詞的問題.
    “javascript css” – 會搜尋到包含 javascript css 完整字串的資料.

    更多關於 BOOLEAN 的特殊運算符請參考 – https://www.w3resource.com/mysql/mysql-full-text-search-functions.php 或是 https://dev.mysql.com/doc/refman/8.0/en/fulltext-boolean.html

  • AGAINST 模式(WITH QUERY EXPANSION)??
    官方資料說明 https://mariadb.com/kb/en/library/full-text-index-overview/ ,搜尋 Database 時,會使用關聯把含有 MariaDB 字串 (資料庫 Database 的一種)資料帶出來,但我沒試成功 (需注意資料庫字元編碼 Character Sets 與 文字排序 Collations ,請參考資料庫編碼 – https://benjr.tw/102156).

    MariaDB [testdb]> SELECT * FROM Employee WHERE MATCH (Name , Dept , JobTitle , Expertise) AGAINST ('database' WITH QUERY EXPANSION);
    

搜尋的關聯比

Full Text Index 還可以看搜尋的關聯比.

  • NATURAL LANGUAGE MODE
    因為 Linux engineer 這兩個關鍵字在資料庫中有一半以上資料都包含,所以不顯示其搜索結果 (RELEVANCE=0).

    MariaDB [testdb]> SELECT *,MATCH (Name , Dept , JobTitle , Expertise) AGAINST ('linux engineer' IN NATURAL LANGUAGE MODE) AS RELEVANCE FROM Employee;
    +----+------+------------+-----------------+----------------------------------------------+--------+-----------+
    | id | Name | Dept       | JobTitle        | Expertise                                    | Salary | RELEVANCE |
    +----+------+------------+-----------------+----------------------------------------------+--------+-----------+
    |  1 | Ben  | Validation | Sensor Engineer | Linux Redhat RHEL Ubuntu MySQL MariaDB       |  54000 |         0 |
    |  2 | Cars | Validation | Senior Engineer | Linux Kernel Driver Redhat RHEL Ubuntu       |  52000 |         0 |
    |  3 | Joe  | Validation | Engineer        | Windows PowerShell PHP Python Javascript CSS |  48000 |         0 |
    |  4 | Bob  | Validation | Engineer        | Windows Linux HTML Javascript CSS MariaDB    |  45000 |         0 |
    +----+------+------------+-----------------+----------------------------------------------+--------+-----------+
    4 rows in set (0.00 sec)
    

    搜尋 mysql 的關聯比.

    MariaDB [testdb]> SELECT *,MATCH (Name , Dept , JobTitle , Expertise) AGAINST ('mysql' IN NATURAL LANGUAGE MODE) AS RELEVANCE FROM Employee;
    +------+------------+-----------------+----------------------------------------------+--------+--------------------+
    | Name | Dept       | JobTitle        | Expertise                                    | Salary | RELEVANCE          |
    +------+------------+-----------------+----------------------------------------------+--------+--------------------+
    | Ben  | Validation | Sensor Engineer | Linux Redhat RHEL Ubuntu MySQL MariaDB       |  54000 | 0.9853024482727051 |
    | Cars | Validation | Senior Engineer | Linux Kernel Driver Redhat RHEL Ubuntu       |  52000 |                  0 |
    | Joe  | Validation | Engineer        | Windows PowerShell PHP Python Javascript CSS |  48000 |                  0 |
    | Bob  | Validation | Engineer        | Windows Linux HTML Javascript CSS MariaDB    |  45000 |                  0 |
    +------+------------+-----------------+----------------------------------------------+--------+--------------------+
    4 rows in set (0.00 sec)
    

    在 AGAINST 後面可以設定 RELEVANCE 要幾分以上 > 或是 以下 < .

    MariaDB [testdb]> SELECT *,MATCH (Name , Dept , JobTitle , Expertise) AGAINST ('mysql' IN NATURAL LANGUAGE MODE) AS RELEVANCE FROM Employee HAVING RELEVANCE > 0;
    +------+------------+-----------------+----------------------------------------------+--------+--------------------+
    | Name | Dept       | JobTitle        | Expertise                                    | Salary | RELEVANCE          |
    +------+------------+-----------------+----------------------------------------------+--------+--------------------+
    | Ben  | Validation | Sensor Engineer | Linux Redhat RHEL Ubuntu MySQL MariaDB       |  54000 | 0.9853024482727051 |
    +------+------------+-----------------+----------------------------------------------+--------+--------------------+
    1 rows in set (0.00 sec)
    
  • BOOLEAN MODE
    與前面範例使用相同的搜尋關鍵字 Linux engineer 但使用 BOOLEAN MODE.

    MariaDB [testdb]> SELECT *,MATCH (Name , Dept , JobTitle , Expertise) AGAINST ('linux engineer' IN BOOLEAN MODE) AS RELEVANCE FROM Employee;
    +----+------+------------+-----------------+----------------------------------------------+--------+-----------+
    | id | Name | Dept       | JobTitle        | Expertise                                    | Salary | RELEVANCE |
    +----+------+------------+-----------------+----------------------------------------------+--------+-----------+
    |  1 | Ben  | Validation | Sensor Engineer | Linux Redhat RHEL Ubuntu MySQL MariaDB       |  54000 |         2 |
    |  2 | Cars | Validation | Senior Engineer | Linux Kernel Driver Redhat RHEL Ubuntu       |  52000 |         2 |
    |  3 | Joe  | Validation | Engineer        | Windows PowerShell PHP Python Javascript CSS |  48000 |         1 |
    |  4 | Bob  | Validation | Engineer        | Windows Linux HTML Javascript CSS MariaDB    |  45000 |         2 |
    +----+------+------------+-----------------+----------------------------------------------+--------+-----------+
    4 rows in set (0.00 sec)
    
  • QUERY EXPANSION
    與前面範例使用相同的搜尋關鍵字 Linux engineer 但使用 QUERY EXPANSION.

    MariaDB [testdb]> SELECT *,MATCH (Name , Dept , JobTitle , Expertise) AGAINST ('linux engineer' WITH QUERY EXPANSION) AS RELEVANCE FROM Employee;
    +----+------+------------+-----------------+----------------------------------------------+--------+--------------------+
    | id | Name | Dept       | JobTitle        | Expertise                                    | Salary | RELEVANCE          |
    +----+------+------------+-----------------+----------------------------------------------+--------+--------------------+
    |  1 | Ben  | Validation | Sensor Engineer | Linux Redhat RHEL Ubuntu MySQL MariaDB       |  54000 | 2.9559073448181152 |
    |  2 | Cars | Validation | Senior Engineer | Linux Kernel Driver Redhat RHEL Ubuntu       |  52000 | 3.9412097930908203 |
    |  3 | Joe  | Validation | Engineer        | Windows PowerShell PHP Python Javascript CSS |  48000 |                  0 |
    |  4 | Bob  | Validation | Engineer        | Windows Linux HTML Javascript CSS MariaDB    |  45000 |                  0 |
    +----+------+------------+-----------------+----------------------------------------------+--------+--------------------+
    4 rows in set (0.01 sec)
    

關於這三種的搜尋方式請參考 https://mariadb.com/kb/en/library/full-text-index-overview/ 或 https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html 的說明.

移除 Full-Text Index

MariaDB [testdb]> SHOW INDEX FROM Employee;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Employee |          1 | Index1   |            1 | Name        | NULL      |        NULL |     NULL | NULL   | YES  | FULLTEXT   |         |               |
| Employee |          1 | Index1   |            2 | Dept        | NULL      |        NULL |     NULL | NULL   | YES  | FULLTEXT   |         |               |
| Employee |          1 | Index1   |            3 | JobTitle    | NULL      |        NULL |     NULL | NULL   | YES  | FULLTEXT   |         |               |
| Employee |          1 | Index1   |            4 | Expertise   | NULL      |        NULL |     NULL | NULL   | YES  | FULLTEXT   |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

如果當初沒有定 Index Name 時,預設會使用第一個欄位為名稱.

MariaDB [testdb]> ALTER TABLE Employee DROP INDEX Index1; 
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
MariaDB [testdb]> SHOW INDEX FROM Employee;
Empty set (0.00 sec)

建 FULL TEXT Index

如果是已經建立好的 Database.Table 事後要再建 FULL TEXT 可以透過以下的方式.

MariaDB [testdb]> ALTER TABLE Employee ADD FULLTEXT Index1 (Name , Dept , JobTitle , Expertise);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [testdb]> SHOW INDEX FROM Employee;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Employee |          1 | Index1   |            1 | Name        | NULL      |        NULL |     NULL | NULL   | YES  | FULLTEXT   |         |               |
| Employee |          1 | Index1   |            2 | Dept        | NULL      |        NULL |     NULL | NULL   | YES  | FULLTEXT   |         |               |
| Employee |          1 | Index1   |            3 | JobTitle    | NULL      |        NULL |     NULL | NULL   | YES  | FULLTEXT   |         |               |
| Employee |          1 | Index1   |            4 | Expertise   | NULL      |        NULL |     NULL | NULL   | YES  | FULLTEXT   |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

錯誤訊息

需要修改你的 Database.Table 的 Engine 為 MyISAM,如 InnoDB Engine 需要到 MySQL 5.6 之後的版本才有支援 FULLTEXT indexes.

MariaDB [testdb]> ALTER TABLE employee ADD FULLTEXT(Name , Dept , JobTitle);
ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes

透過下面指令可以修改 Engine.

MariaDB [testdb]> ALTER TABLE employee ENGINE = MYISAM;
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

檢視你的資料庫改成 MyISAM Engine.

MariaDB [testdb]> show table status\G
*************************** 1. row ***************************
           Name: employee
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 85
 Avg_row_length: 308
    Data_length: 26208
Max_data_length: 281474976710655
   Index_length: 4096
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2019-06-11 00:19:43
    Update_time: 2019-06-11 00:19:43
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

利用 SELECT Full Text Index + Sub-query (Subselect) 請參考 – https://benjr.tw/102209

沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步了解 Akismet 如何處理網站訪客的留言資料