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 搜尋模式有三種,在使用前先來看一下哪些搜尋及其結果會被排除.
- FULLTEXT 斷字 (資料庫內容的資料,哪些符號會讓字串變成斷字)
- 單詞資料包含 字母 (Letter) ,數字 (Digit) 和下劃線( _ , underscore) 都會當作為單一詞 (PHP_Python 視同為一個單詞,搜尋 PHP 或是 Python 會找不到該筆資料,使用 IN BOOLEAN MODE + * ,搜尋關鍵字 PHP* 則可以找到).
- 空白 , 逗號 (,) , 與點 (.) 字串會被斷成兩個單詞 (如 : Chris Hemsworth , Chris,Hemsworth , Chris.Hemsworth 都是兩個單詞,搜尋可以使用這兩個單詞 Chris 與 Hemsworth 來找資料),如不想把含有 點 (.) 的字串被斷成兩個單詞,可改用 IN BOOLEAN MODE + “keyword” (雙引號) 來搜尋.
- 預設搜尋關鍵字長度小於 4 個字符（包4個）的單詞會被忽略掉,不列入搜尋關鍵字中,可以更改 ft_min_word_length 來調整此值.
- 預設搜尋關鍵字長度超過 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)
- 停用詞 (Stopwords – https://mariadb.com/kb/en/library/full-text-index-stopwords/ ) 當關鍵字搜尋時會被忽略掉,可改用 IN BOOLEAN MODE.
- 如果一個關鍵字搜尋出來的結果,佔所有資料的一半時則不顯示其搜索結果,可改用 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
沒有解決問題,試試搜尋本站其他內容