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 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 用法請參考 – https://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 函數 (請參考 https://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)
沒有解決問題,試試搜尋本站其他內容