DB中笛卡爾積的用途

來自專欄 Java開發學習記錄

DB中笛卡爾積的作用

學習過資料庫和SQL知識的程序員都應該知道笛卡爾積是什麼，就是將兩張表中數據進行組合然後返回。我一直覺得笛卡爾積沒啥鳥用，只是學術上的東西，直到通宵加班時，遇到一個祖傳系統的需求。

祖傳系統中包含了一個表，裡面是群組信息。需求中需要新增一個表，保存群組和角色key以及角色是否啟用的標識。

簡化後的表結構如下：

mysql> describe t_group;+-------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || name | varchar(45) | NO | | NULL | |+-------+-------------+------+-----+---------+----------------+mysql> describe t_group_roles;+----------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| group_id | int(11) | NO | PRI | NULL | || role_key | varchar(45) | NO | PRI | NULL | || is_open | tinyint(4) | NO | | NULL | |+----------+-------------+------+-----+---------+-------+

t_group_roles中包含了群組id，角色key和是否啟用的標識，群組id和角色key構成聯合主鍵。

測試人員插入了一條測試數據，根據業務規則每個群組對應的角色啟用標識默認都是相同的，developer、cie、admin和sl都為1，表示啟用，其他角色默認不啟用。如下：

mysql> select * from t_group_roles;+----------+-----------+---------+| group_id | role_key | is_open |+----------+-----------+---------+| 1 | admin | 1 || 1 | ba | 0 || 1 | cie | 1 || 1 | cmo | 0 || 1 | developer | 1 || 1 | mde | 0 || 1 | member | 0 || 1 | pd | 0 || 1 | QA | 0 || 1 | sl | 1 |+----------+-----------+---------+

現在需要開發人員做的時，根據這個規則，將所有的group對應的數據都插入t_group_roles 表中。

實現這個需求有多種方法，最容易想到的就是寫Java代碼或者存儲過程，獲取所有群組信息，然後根據角色啟用規則構造SQL語句，insert到t_group_roles中。

但是這個場景如果使用笛卡爾積，一個SQL就能實現。

首先構造查詢語句，select from t_group,t_group_roles 即可使笛卡爾積構造出每個群組及其對應的默認啟用規則，由於存在聯合主鍵，排除掉t_group_roles的群組即可，SQL語句如下：

SELECT a.id, c.role_key, c.is_openFROM t_group a, (SELECT b.role_key, b.is_open FROM t_group_roles b WHERE b.group_id = 1) c -- 獲取角色啟用規則WHERE NOT exists(SELECT 1 FROM t_group_roles d WHERE d.group_id = a.id); -- 不進行重複插入

查詢出的部分數據如下：可以看到，id為1的群組不會參與構建，因為這個群組已經包含在t_group_roles中了。

此時只需要再構建一個insert語句即可：

insert into t_group_roles(group_id, role_key, is_open)SELECT a.id, c.role_key, c.is_openFROM t_group a, (SELECT DISTINCT b.role_key, b.is_open FROM t_group_roles b WHERE b.group_id = 1) c -- 獲取角色啟用規則WHERE NOT exists(SELECT 1 FROM t_group_roles d WHERE d.group_id = a.id); -- 不進行重複插入

執行後的效果：select * from t_group_roles;

並且只要t_group_roles中group_id等於1的數據不被刪除，這個SQL就是正確的，可以在群組中新增了數據之後運行，為新增群組添加默認的角色啟用規則（實際上不會這樣使用，反正規則是寫死的，為group表加個after的triger即可）。

推薦閱讀：