測試環境為 CentOS 7 x86_64 虛擬機.
規劃資料庫欄位很重要.
下面範例有兩個 Table.
- project (存放專案名稱與相對應人員名單)
- staff (存放員工姓名與部門)
[root@localhost ~]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 25 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
project 這個資料表主要儲存專案的相關資料.
MariaDB [testdb]> CREATE TABLE project (K1 int(11) NOT NULL auto_increment , name varchar(199) NOT NULL , member varchar(199) NOT NULL, PRIMARY KEY (K1)); Query OK, 0 rows affected (0.02 sec) MariaDB [testdb]> DESCRIBE project; +--------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+----------------+ | K1 | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(199) | NO | | NULL | | | member | varchar(199) | NO | | NULL | | +--------+--------------+------+-----+---------+----------------+ 3 rows in set (0.02 sec)
staff 這個資料表主要儲存員工的相關資料.
MariaDB [testdb]> CREATE TABLE staff (K1 int(11) NOT NULL auto_increment , name varchar(199) NOT NULL , dept varchar(199) NOT NULL, PRIMARY KEY (K1)); Query OK, 0 rows affected (0.02 sec) MariaDB [testdb]> DESCRIBE staff; +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | K1 | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(199) | NO | | NULL | | | dept | varchar(199) | NO | | NULL | | +-------+--------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
並在 staff Table 建立了員工 Ben , Jason , Thomas , Jack , Andy , Chuck 與其相對應的部門資料.
MariaDB [testdb]> INSERT INTO staff (name , dept) VALUES('Ben','HR');
Query OK, 1 row affected (0.01 sec)
MariaDB [testdb]> INSERT INTO staff (name , dept) VALUES('Jason','HW');
Query OK, 1 row affected (0.00 sec)
MariaDB [testdb]> INSERT INTO staff (name , dept) VALUES('Thomas','Test');
Query OK, 1 row affected (0.01 sec)
MariaDB [testdb]> INSERT INTO staff (name , dept) VALUES('Jack','PM');
Query OK, 1 row affected (0.00 sec)
MariaDB [testdb]> INSERT INTO staff (name , dept) VALUES('Andy','Test');
Query OK, 1 row affected (0.00 sec)
MariaDB [testdb]> INSERT INTO staff (name , dept) VALUES('Chuck','SW');
Query OK, 1 row affected (0.00 sec)
MariaDB [testdb]> SELECT * FROM staff;
+----+--------+------+
| K1 | name | dept |
+----+--------+------+
| 1 | Ben | HR |
| 2 | Jason | HW |
| 3 | Thomas | Test |
| 4 | Jack | PM |
| 5 | Andy | Test |
| 6 | Chuck | SW |
+----+--------+------+
6 rows in set (0.01 sec)
在 project Table 建立了專案 A , B , C 與其相對應的成員 (專案相對應的成員儲存在 member 欄位,多名人員時使用 “,” 來隔開).
MariaDB [testdb]> INSERT INTO project (name , member) VALUES('A','Ben,Jason,Thomas');
Query OK, 1 row affected (0.01 sec)
MariaDB [testdb]> INSERT INTO project (name , member) VALUES('B','Jack,Andy,Chuck');
Query OK, 1 row affected (0.00 sec)
MariaDB [testdb]> INSERT INTO project (name , member) VALUES('C','Jack,Jason,Chuck');
Query OK, 1 row affected (0.01 sec)
MariaDB [testdb]> SELECT * FROM project;
+----+------+------------------+
| K1 | name | member |
+----+------+------------------+
| 1 | A | Ben,Jason,Thomas |
| 2 | B | Jack,Andy,Chuck |
| 3 | C | Jack,Jason,Chuck |
+----+------+------------------+
3 rows in set (0.00 sec)
現在問題來了,我要怎麼透過 SELECT 把專案 A 與其成員和單位顯示出來.
一開始想到可以用 WHERE 的 IN 功能,但是無法達成,網路查詢可用 FIND_IN_SET() 函數來達成,使用很簡單,回傳值為 string 位於 string_list (須以 , 分隔)的哪一個位置(不存在時,回傳 0 ).
FIND_IN_SET(string, string_list)
直接來看範例.
MariaDB [(none)]> SELECT FIND_IN_SET('b' , 'a,b,c');
+----------------------------+
| FIND_IN_SET('b' , 'a,b,c') |
+----------------------------+
| 2 |
+----------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> SELECT FIND_IN_SET('f' , 'a,b,c');
+----------------------------+
| FIND_IN_SET('f' , 'a,b,c') |
+----------------------------+
| 0 |
+----------------------------+
1 row in set (0.00 sec)
透過這個方式可以得到專案 A 與其成員和單位.
MariaDB [testdb]> SELECT 'A' as Project, name , dept FROM testdb.staff WHERE FIND_IN_SET(name, (SELECT member FROM testdb.project WHERE name LIKE 'A')); +---------+--------+------+ | Project | name | dept | +---------+--------+------+ | A | Ben | HR | | A | Jason | HW | | A | Thomas | Test | +---------+--------+------+ 3 rows in set (0.00 sec)
在上網查詢的過程中,不少文章提到使用 FIND_IN_SET 函數可以解決欄位中的 “,( comma separated )” 所造成的搜尋問題,也不建議儲存這樣的欄位內容.不少文章有提到資料庫正規化 (Database normalization) 重新定義 Table 與其相關性.
下面重新定義 project 的 Tables, 先把 project 舊資料移除.
MariaDB [testdb]> DELETE FROM project; Query OK, 3 rows affected (0.01 sec)
這次不把所有的成員資料儲存在 member 欄位,而是紀錄專案與個別成員資料成為單一筆資料.
MariaDB [testdb]> INSERT INTO project (name , member) VALUES('A','Ben');
Query OK, 1 row affected (0.01 sec)
MariaDB [testdb]> INSERT INTO project (name , member) VALUES('A','Jason');
Query OK, 1 row affected (0.00 sec)
MariaDB [testdb]> INSERT INTO project (name , member) VALUES('A','Thomas');
Query OK, 1 row affected (0.00 sec)
MariaDB [testdb]> INSERT INTO project (name , member) VALUES('B','Jack');
Query OK, 1 row affected (0.00 sec)
MariaDB [testdb]> INSERT INTO project (name , member) VALUES('B','Andy');
Query OK, 1 row affected (0.00 sec)
MariaDB [testdb]> INSERT INTO project (name , member) VALUES('B','Chuck');
Query OK, 1 row affected (0.00 sec)
MariaDB [testdb]> INSERT INTO project (name , member) VALUES('C','Jack');
Query OK, 1 row affected (0.01 sec)
MariaDB [testdb]> INSERT INTO project (name , member) VALUES('C','Jason');
Query OK, 1 row affected (0.00 sec)
MariaDB [testdb]> INSERT INTO project (name , member) VALUES('C','Chuck');
Query OK, 1 row affected (0.00 sec)
MariaDB [testdb]> SELECT * FROM project;
+----+------+--------+
| K1 | name | member |
+----+------+--------+
| 4 | A | Ben |
| 5 | A | Jason |
| 6 | A | Thomas |
| 7 | B | Jack |
| 8 | B | Andy |
| 9 | B | Chuck |
| 10 | C | Jack |
| 11 | C | Jason |
| 12 | C | Chuck |
+----+------+--------+
9 rows in set (0.00 sec)
現在透過 Join ( https://benjr.tw/101855 ) 的方式查詢即可得到 專案與其成員和單位.
MariaDB [testdb]> SELECT project.name , project.member , staff.dept FROM project , staff WHERE project.member=staff.name; +------+--------+------+ | name | member | dept | +------+--------+------+ | A | Ben | HR | | A | Jason | HW | | A | Thomas | Test | | B | Jack | PM | | B | Andy | Test | | B | Chuck | SW | | C | Jack | PM | | C | Jason | HW | | C | Chuck | SW | +------+--------+------+ 9 rows in set (0.00 sec)
查詢 專案 A 與其成員和單位.
MariaDB [testdb]> SELECT project.name , project.member , staff.dept FROM project , staff WHERE project.member=staff.name AND project.name='A'; +------+--------+------+ | name | member | dept | +------+--------+------+ | A | Ben | HR | | A | Jason | HW | | A | Thomas | Test | +------+--------+------+ 3 rows in set (0.00 sec)
沒有解決問題,試試搜尋本站其他內容