MYSQL[05]串連用法總結

1 -- MySQL串連簡介

MySQL支援的連線類型如下：
交叉串連、內串連、外串連（左外串連和右外串連）、自串連、聯合

2 -- 各種串連的使用方法

在示範各種串連的用法之前，我們先定義如下的資料庫表格，以後的示範就使用它們。

mysql> select * from t_users;+---------+-----------+---------+---------------------+| iUserID | sUserName | iStatus | dtLastTime |+---------+-----------+---------+---------------------+| 1 | baidu | 0 | 2010-06-27 15:04:03 || 2 | google | 0 | 2010-06-27 15:04:03 || 3 | yahoo | 0 | 2010-06-27 15:04:03 || 4 | tencent | 0 | 2010-06-27 15:04:03 |+---------+-----------+---------+---------------------+

mysql> select * from t_groups;+----------+------------+---------------------+| iGroupID | sGroupName | dtLastTime |+----------+------------+---------------------+| 1 | spring | 2010-06-27 15:04:03 || 2 | summer | 2010-06-27 15:04:03 || 3 | autumn | 2010-06-27 15:04:03 || 4 | winter | 2010-06-27 15:04:03 |+----------+------------+---------------------+

mysql> select * from t_users_groups;+---------+----------+---------------------+| iUserID | iGroupID | dtLastTime |+---------+----------+---------------------+| 1 | 1 | 2010-06-27 15:04:03 || 2 | 1 | 2010-06-27 15:04:03 || 4 | 3 | 2010-06-27 15:04:03 || 6 | 4 | 2010-06-27 15:04:03 |+---------+----------+---------------------+

1.交叉串連

2.內串連

3.外串連
外串連有什麼特點？簡而言之，外串連作用在通過某個key相串連的兩張表上，它首先從A表中依次讀出每行資料，然後到與之相串連的B表，尋找具有相同key值的記錄。如果有匹配行，A和B的對應記錄組成新結果行；如果沒有，A與一條各欄位為NULL的B記錄組成新結果行。
到底從哪個表中選擇所有行，SQL標準定義了左外串連和右外串連。

左外串連：mysql> SELECT * FROM t_users LEFT JOIN t_users_groups ON t_users.iUserID=t_users_groups.iUserID;+---------+-----------+---------+---------------------+---------+----------+---------------------+| iUserID | sUserName | iStatus | dtLastTime | iUserID | iGroupID | dtLastTime |+---------+-----------+---------+---------------------+---------+----------+---------------------+| 1 | baidu | 0 | 2010-06-27 15:04:03 | 1 | 1 | 2010-06-27 15:04:03 || 2 | google | 1 | 2010-06-27 15:46:51 | 2 | 1 | 2010-06-27 15:04:03 || 3 | yahoo | 1 | 2010-06-27 15:46:51 | NULL | NULL | NULL || 4 | tencent | 0 | 2010-06-27 15:04:03 | 4 | 3 | 2010-06-27 15:04:03 |+---------+-----------+---------+---------------------+---------+----------+---------------------+4 rows in set (0.00 sec)

t_users為上述描述中的A表，t_users_groups為B表。

右外串連：mysql> SELECT * FROM t_users RIGHT JOIN t_users_groups ON t_users.iUserID=t_users_groups.iUserID;+---------+-----------+---------+---------------------+---------+----------+---------------------+| iUserID | sUserName | iStatus | dtLastTime | iUserID | iGroupID | dtLastTime |+---------+-----------+---------+---------------------+---------+----------+---------------------+| 1 | baidu | 0 | 2010-06-27 15:04:03 | 1 | 1 | 2010-06-27 15:04:03 || 2 | google | 1 | 2010-06-27 15:46:51 | 2 | 1 | 2010-06-27 15:04:03 || 4 | tencent | 0 | 2010-06-27 15:04:03 | 4 | 3 | 2010-06-27 15:04:03 || NULL | NULL | NULL | NULL | 6 | 4 | 2010-06-27 15:04:03 |+---------+-----------+---------+---------------------+---------+----------+---------------------+4 rows in set (0.00 sec)

t_users_groups為上述描述中的A表，t_users為B表。

4.自串連

5.聯合
UNION運算子表示聯合，它用來把多個SELECT查詢的結果串連成一個單獨的結果集，但在串連時去除重複行。可以使用UNION串連儘可能多的SELECT查詢，但要謹記兩個基本條件。首先，每個SELECT查詢返回的欄位個數必須相同。第二，每個SELECT查詢的欄位類型必須依次相同。
我們舉個聯合例子：

mysql> SELECT iUserID,sUserName,dtLastTime FROM t_users-> UNION-> SELECT iGroupID,sGroupName,dtLastTime FROM t_groups;+---------+-----------+---------------------+| iUserID | sUserName | dtLastTime |+---------+-----------+---------------------+| 1 | baidu | 2010-06-27 15:04:03 || 2 | google | 2010-06-27 15:46:51 || 3 | yahoo | 2010-06-27 15:46:51 || 4 | tencent | 2010-06-27 15:04:03 || 1 | spring | 2010-06-27 15:04:03 || 2 | summer | 2010-06-27 15:04:03 || 3 | autumn | 2010-06-27 15:04:03 || 4 | winter | 2010-06-27 15:04:03 |+---------+-----------+---------------------+8 rows in set (0.01 sec)

對UNION的每個SELECT添加ORDER BY子句是沒有意義的，如果要排序則必須將其施加到最後的結果集上。比如我們要對上面的例子中的iUserID進行排序，應該使用如下的SQL語句：

mysql> (SELECT iUserID,sUserName,dtLastTime FROM t_users)-> UNION-> (SELECT iGroupID,sGroupName,dtLastTime FROM t_groups)-> ORDER BY iUserID ASC;+---------+-----------+---------------------+| iUserID | sUserName | dtLastTime |+---------+-----------+---------------------+| 1 | baidu | 2010-06-27 15:04:03 || 1 | spring | 2010-06-27 15:04:03 || 2 | google | 2010-06-27 15:46:51 || 2 | summer | 2010-06-27 15:04:03 || 3 | yahoo | 2010-06-27 15:46:51 || 3 | autumn | 2010-06-27 15:04:03 || 4 | tencent | 2010-06-27 15:04:03 || 4 | winter | 2010-06-27 15:04:03 |+---------+-----------+---------------------+8 rows in set (0.02 sec)

3 -- 資料庫增量同步處理例子

假設我們還有一個db_src.t_users，它與db_tar.t_users結構完全相同。現網的應用策略，要求先操作db_src.t_users，確認無誤後再同步到db_tar.t_users。所以兩邊資料只會存在三種情況，後面我們將分別介紹如何使用SQL串連在資料庫間增量同步處理資料。

mysql> select * from t_users;+---------+-----------+---------+---------------------+| iUserID | sUserName | iStatus | dtLastTime |+---------+-----------+---------+---------------------+| 1 | baidu | 0 | 2010-06-27 15:04:03 || 2 | google | 1 | 2010-06-27 15:46:51 || 3 | yahoo | 1 | 2010-06-27 15:46:51 || 4 | tencent | 0 | 2010-06-27 15:04:03 || 5 | netease | 0 | 2010-06-27 15:04:03 |+---------+-----------+---------+---------------------+5 rows in set (0.01 sec)

情況1：db_src.t_users存在某條記錄，db_tar.t_users中不存在。策略->將前者中的記錄插入到後者中。

解決該問題的SQL語句為：

INSERT INTO db_tar.t_users select db_src.t_users.*FROM db_src.t_users LEFT JOIN db_tar.t_users ON db_src.t_users.iUserID=db_tar.t_users.iUserIDWHERE db_tar.t_users.iUserID IS NULL; 

同步原理其實很簡單，核心SQL語句就是SELECT db_src.t_users.iUserID, db_src.t_users.sUserName, db_tar.t_users.* FROM db_src.t_users LEFT JOIN db_tar.t_users ON db_src.t_users.iUserID=db_tar.t_users.iUserID WHERE db_tar.t_users.iUserID IS NULL。如果不使用where條件檢索資料，執行SQL得到結果如下：

mysql> SELECT db_src.t_users.iUserID, db_src.t_users.sUserName, db_tar.t_users.*-> FROM db_src.t_users-> LEFT JOIN db_tar.t_users ON db_src.t_users.iUserID=db_tar.t_users.iUserID;+---------+-----------+---------+-----------+---------+---------------------+| iUserID | sUserName | iUserID | sUserName | iStatus | dtLastTime |+---------+-----------+---------+-----------+---------+---------------------+| 1 | baidu | 1 | baidu | 0 | 2010-06-27 15:04:03 || 2 | google | 2 | google | 1 | 2010-06-27 15:46:51 || 3 | yahoo | 3 | yahoo | 1 | 2010-06-27 15:46:51 || 4 | tencent | 4 | tencent | 0 | 2010-06-27 15:04:03 || 5 | netease | NULL | NULL | NULL | NULL |+---------+-----------+---------+-----------+---------+---------------------+5 rows in set (0.00 sec)

加入where語句後，我們就能得到"iUserID=5"這條記錄不在目標資料表中，使用insert語句可以將其插入到目標資料庫中，從而完成同步操作。

情況2：db_src.t_users存在某條記錄，db_tar.t_users中也存在。策略->如果兩者資料不同，則修改後者的記錄。

這裡有個問題，就是如何判斷這兩條記錄是否不同。我們可以採用逐個欄位比較的方法，但欄位較多的時候就不好使用了。簡單點的方法是規定修改db_src.t_users時，必須同時將db_src.t_users.dtLastTime修改為目前時間，以後就可以通過比較dtLastTime來判斷資料是否不同。

根據上述判斷方法，解決該問題的SQL語句為：

select CONCAT('UPDATE db_src.t_users SET ','iUserID=\'', db_src.t_users.iUserID, '\',','sUserName=\'', db_src.t_users.sUserName, '\',','iStatus=\'', db_src.t_users.iStatus, '\',','dtLastTime=\'', db_src.t_users.dtLastTime, '\' ','WHERE db_tar.t_users.iUserID=\'', db_src.t_users.iUserID, '\';')from db_src.t_users left join db_tar.t_users on db_src.t_users.iUserID = db_tar.t_users.iUserIDwhere db_tar.t_users.iUserID is not null anddb_src.t_users.dtLastTime > db_tar.t_users.dtLastTime;

直接執行上述語句得到只是一組UPDATE的SQL，並不能直接修改db_tar.t_users，我們可以在shell命令列上執行下面的這個命令完成同步功能：

mysql -Nse select CONCAT('UPDATE db_src.t_users SET ','iUserID=\'', db_src.t_users.iUserID, '\',','sUserName=\'', db_src.t_users.sUserName, '\',','iStatus=\'', db_src.t_users.iStatus, '\',','dtLastTime=\'', db_src.t_users.dtLastTime, '\' ','WHERE db_tar.t_users.iUserID=\'', db_src.t_users.iUserID, '\';')from db_src.t_users left join db_tar.t_users on db_src.t_users.iUserID = db_tar.t_users.iUserIDwhere db_tar.t_users.iUserID is not null anddb_src.t_users.dtLastTime > db_tar.t_users.dtLastTime; | mysql; 

情況3：db_src.t_users不存在某條記錄，但在db_tar.t_users中存在。策略->將該記錄從後者中刪除。
解決該問題的SQL語句為：

SELECT CONCAT('DELETE FROM db_tar.t_users WHERE db_tar.t_users.iUserID=',db_tar.t_users.iUserID, ';')FROM db_tar.t_users LEFT JOIN db_src.t_users ON db_src.t_users.iUserID=db_tar.t_users.iUserIDWHERE db_src.t_users.iUserID IS NULL;

該SQL語句的情況和第二種情況相似，也不能直接執行。所以需要採用同樣的方式使用SQL語句才能完成工作。可能已經有人看出了，還有更為簡單的方法可以處理這種情況。對，確實存在！當然可以使用下面更為簡潔的SQL語句：

DELETE FROM db_tar.t_usersWHERE db_tar.t_users.UserID NOT IN (SELECT db_src.t_users.iUserID FROM db_src.t_users);