SQL 語法 SELECT Full Text Index + Sub-query (Subselect)

SQL 可以透過 SELECT + WHERE + LIKE 可以針對 CHAR, VARCHAR, 以及 TEXT 指定欄位 (COLUMN) 做關鍵字來比對搜尋 ( 需注意資料庫字元編碼 Character Sets 與 文字排序 Collations ,請參考資料庫編碼 – http://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 VARCHAR(20),Dept VARCHAR(20),JobTitle VARCHAR(20),Expertise VARCHAR(99) , Salary INT(11),FULLTEXT Index1(Name , Dept , JobTitle , Expertise)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.004 sec)

先塞入一些簡單資料.

MariaDB [testdb]> INSERT INTO Employee VALUES ('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');
Query OK, 4 rows affected (0.002 sec)
Records: 4  Duplicates: 0  Warnings: 0

Full Text Index

使用 Full Text Index 的搜尋方式.

MariaDB [testdb]> SELECT * FROM Employee WHERE MATCH (Name , Dept , JobTitle , Expertise) AGAINST ('Redhat' 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 |
+------+------------+-----------------+----------------------------------------+--------+
2 rows in set (0.001 sec)

更多關於 SELECT Full Text Index MATCH AGAINST 用法請參考 – http://benjr.tw/101644

Full Text Index + Sub-Query

子查詢 (sub-query) 為 一段 SQL 語法中內還包含一段 SELECT ( SELECT 敘述需置於 左右刮號 中).適合使用於當 AGAINST 搜尋相關字串的內容是儲存在另外一個資料表 (Table) 時.

建立另一個資料表,內含我要查詢的資料.

MariaDB [testdb]> CREATE TABLE SContent (K1 INT(11), SName1 VARCHAR(20) , SName2 VARCHAR(20));
Query OK, 0 rows affected (0.009 sec)

先塞入一些簡單資料.

MariaDB [testdb]> INSERT INTO SContent VALUES ('1' , 'Redhat RHEL' , '') , ('2' , 'Redhat' , 'RHEL');
Query OK, 2 rows affected (0.002 sec)
Records: 2  Duplicates: 0  Warnings: 0

利用子查詢 Sun-Query 來當 Full Text Index 的關鍵字.

MariaDB [testdb]> SELECT SName1 FROM SContent WHERE K1=2;
+--------+
| SName1 |
+--------+
| Redhat |
+--------+
1 row in set (0.001 sec)

MariaDB [testdb]> SELECT * FROM Employee WHERE MATCH (Name , Dept , JobTitle , Expertise) AGAINST ((SELECT SName1 FROM SContent WHERE K1=2) 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 |
+------+------------+-----------------+----------------------------------------+--------+
2 rows in set (0.001 sec)

但當 SELECT 中包含多個欄位 columns 時,不能直接使用.

MariaDB [testdb]> SELECT SName1 , SName2 FROM SContent WHERE K1=2;
+--------+--------+
| SName1 | SName2 |
+--------+--------+
| Redhat | RHEL   |
+--------+--------+
1 row in set (0.001 sec)

MariaDB [testdb]> SELECT * FROM Employee WHERE MATCH (Name , Dept , JobTitle , Expertise) AGAINST ((SELECT SName1 ,SName2 FROM SContent WHERE K1=2) IN BOOLEAN MODE);
ERROR 1241 (21000): Operand should contain 1 column(s)

可以利用函數 CONCAT_WS 函數 (請參考 http://benjr.tw/101970 ) 可以將兩個或更多個字串連接在一起,並加上固定的分隔字元.

MariaDB [testdb]> SELECT * FROM Employee WHERE MATCH (Name , Dept , JobTitle , Expertise) AGAINST ((SELECT CONCAT_WS(" ", SName1 ,SName2) FROM SContent WHERE K1=2) 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 |
+------+------------+-----------------+----------------------------------------+--------+
2 rows in set (0.001 sec)

或是利用多次的 MATCH () AGAINST () + MATCH () AGAINST ()

MariaDB [testdb]> SELECT * FROM testdb.Employee WHERE MATCH (Name , Dept , JobTitle , Expertise) AGAINST ((SELECT SName1 FROM SContent WHERE K1=2) IN BOOLEAN MODE) + MATCH (Name , Dept , JobTitle , Expertise) AGAINST ((SELECT SName2 FROM SContent WHERE K1=2) 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 |
+------+------------+-----------------+----------------------------------------+--------+
2 rows in set (0.001 sec)
沒有解決問題,試試搜尋本站其他內容

發佈留言

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

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