MySQL（12）---紀錄一次left join一對多關係而引起的BUG

雨點的名字發表於2019-05-20
MySql

MySQL（11）---紀錄一次left join一對多關係而引起的bug

BUG背景 我們有一個訂單表 和 一個 物流表 它們通過 訂單ID 進行一對一的關係繫結。但是由於物流表在儲存訂單資訊的時候沒有做判斷該訂單是否已經有物流資訊，
這就變成同一個訂單id在物流表中存在多條資料，也就變成了本來訂單表只有100條紀錄，而left join 物流表後，所查詢的訂單資料遠遠大於100條。
總結 趁著上面這個問題，自己來複習下join語句 和 distinct關鍵字，同時說明如何解決就算關聯是一對多，但我還是想只顯示100條訂單資料的方法。

一、理論

先再講下關聯表查詢的幾種表示式，網上找了一張圖，通過這張圖就能理解所有關聯查詢的含義。

MySQL（12）---紀錄一次left join一對多關係而引起的BUG

left join(左聯接) 返回包括左表中的所有記錄和右表中聯結欄位相等的記錄 。
right join(右聯接) 返回包括右表中的所有記錄和左表中聯結欄位相等的記錄。
inner join(等值連線) 只返回兩個表中聯結欄位相等的行。


二、left join一對一和一對多

1、一對一關聯表查詢

業務邏輯1 有兩張表，一張商品表、一張商品訂單表回顯訂單列表的時候需要訂單表關聯商品表，如下

1）商品表

DROP TABLE IF EXISTS `t_product`;
CREATE TABLE `t_product` (
  `product_id` char(32) NOT NULL DEFAULT '' COMMENT '主鍵ID',
  `pro_name` varchar(64) DEFAULT NULL COMMENT '商品名稱',
  `cash` double(10,2) DEFAULT '0.00' COMMENT '商品價格',
  `pro_code` varchar(32) DEFAULT NULL COMMENT '商品編號',
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='商品表';

INSERT INTO `t_product` (`product_id`, `pro_name`, `cash`, `pro_code`)
VALUES
    ('1','小米',888.00,'001'),
    ('2','華為',1888.00,'002');

2) 訂單表

DROP TABLE IF EXISTS `t_order`;
CREATE TABLE `t_order` (
  `order_id` char(32) NOT NULL DEFAULT '' COMMENT '主鍵ID',
  `product_id` char(32) DEFAULT NULL COMMENT '商品ID',
  `sale_amount` double(16,2) DEFAULT '0.00' COMMENT '訂單金額',
  `order_number` varchar(40) DEFAULT NULL COMMENT '訂單編碼',
  `status` int(2) DEFAULT '1' COMMENT '訂單狀態 0訂單無效1兌換功成2、已發貨',
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='訂單表';

INSERT INTO `t_order` (`order_id`, `product_id`, `sale_amount`, `order_number`, `status`)
VALUES
    ('1','1',888.00,'001001',1),
    ('2','2',1888.00,'001002',1);

3) 關聯查詢

這裡需要展示訂單列表，訂單列表中當然需要展示商品資訊。

select o.`order_id`,o.`sale_amount`,p.`pro_name` from t_order o left join t_product p on o.`product_id`=p.`product_id`;

執行結果

MySQL（12）---紀錄一次left join一對多關係而引起的BUG

這兩張表不可能是一對多的關係，因為左表關聯右表的主鍵ID,所有右表不可能出現多條紀錄。

2、left join有一對多關聯查詢

業務邏輯2 這裡是邏輯也是有兩張表，一張訂單表、一張物流表。訂單表和上面一樣，資料也一致。

物流表

DROP TABLE IF EXISTS `t_logistics`;
CREATE TABLE `t_logistics` (
  `logistics_id` char(32) NOT NULL DEFAULT '' COMMENT '主鍵ID',
  `order_id` char(32) DEFAULT NULL COMMENT '訂單ID',
  `logistics_company_name` varchar(32) DEFAULT NULL COMMENT '物流公司名稱',
  `courier_number` varchar(32) DEFAULT NULL COMMENT '快遞單號',
  PRIMARY KEY (`logistics_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='物流資訊表';

INSERT INTO `t_logistics` (`logistics_id`, `order_id`, `logistics_company_name`, `courier_number`)
VALUES
    ('1','1','順豐','001'),
    ('2','1','順豐','002');
    ('3','2','中通','003');

注意 這張表資料是有問題的，因為不可能一個訂單同時有兩條物流資訊，但是你不能完全排除這條表裡存在兩條相同訂單編號，因為左表繫結的不是右表的主鍵ID,這可能就是保留物流資訊的時候沒有判斷該訂單已經儲存物流資訊，而引起的資料重複問題。

那麼這個時候問題來了。

select o.`order_id`,o.`sale_amount`,l.`logistics_company_name` from t_order o left join t_logistics l on o.`order_id`=l.`order_id`;

執行結果

MySQL（12）---紀錄一次left join一對多關係而引起的BUG

我們發現，訂單列表已經有三條紀錄，但按照常理應該展示兩條。

注意 所以從這裡我們可以得知，如果你在left join 時，需要顯示的資料的左表資料不能重複時，那麼就需要 on 後面的表它們的對應關係是一對一的關係。顯然這裡對於order_id為1所對應的物流表資訊是一對多的關係。


三、如何解決一對多的問題

一對多並不一定是問題，主要還是看錶與表之間的關係。比如：
A表是使用者表，B表是訂單表。自然也就想到了一個使用者可能多次下單。我們假設B表中的使用者id在A表中匹配到50個使用者id，但是這50個使用者id總訂單數是500個。這就是合理的一對多關係。

那麼如果你業務邏輯肯定顯示一對一的關係，而表關係確實一對多的關係，就像上面的訂單表和物流表一樣。怎麼解決，這裡有兩種解決方案。

1、group by

關鍵點 把一對多的問題轉化成聚合查詢

select o.`order_id`,o.`sale_amount`,l.`logistics_company_name` from t_order o left join t_logistics l on o.`order_id`=l.`order_id` group by o.`order_id`;

MySQL（12）---紀錄一次left join一對多關係而引起的BUG

2、distinct

select distinct o.`order_id`,o.`sale_amount`,l.`logistics_company_name` from t_order o left join t_logistics l on o.`order_id`=l.`order_id`;

MySQL（12）---紀錄一次left join一對多關係而引起的BUG

它所得的的結果和上面是一樣的。

3、group by 和 distinct 比較

1）、不同

  • distinct需要將col列中的全部內容都儲存在一個記憶體中，可以理解為一個hash結構，key為col的值，最後計算hash結構中有多少個key即可得到結果。很明顯，需要將所有不同的值都存起來。記憶體消耗可能較大。
  • 而group by的方式是先將col排序。而資料庫中的group一般使用sort的方法，即資料庫會先對col進行排序。而排序的基本理論是，時間複雜為nlogn，空間為1。然後只要單純的計數就可以了。優點是空間複雜度小，缺點是要進行一次排序，執行時間會較長。

2）、使用場景

資料分佈 去重方式 原因
離散 group distinct空間佔用較大，在時間複雜度允許的情況下，group 可以發揮空間複雜度優勢
集中 distinct distinct空間佔用較小，可以發揮時間複雜度優勢

3）、兩個極端

  • 資料列的所有資料都一樣，即去重計數的結果為1時，用distinct最佳。
  • 如果資料列唯一，沒有相同數值，用group 最好。

四、distinct

1、作用於單列

select distinct name from A   #name去重

2、作用於多列

select distinct name, age from A  #根據name和age兩個欄位來去重的

3、COUNT統計

select count(distinct name) from A;   #表中name去重後的數目

注意： count是不能統計多個欄位的，下面的SQL在SQL Server和Access中都無法執行。

若想使用多個欄位，請使用巢狀查詢，如下：

select count(*) from (select distinct name, age from A) AS B;

4、distinct必須放在開頭

select age, distinct name from A;   #會提示錯誤，因為distinct必須放在開頭

補充

1、能用inner join 儘量用inner join。
2、重複資料可能是表結構一對多造成的，這種情況往往是有意義的，比如訂單和訂單商品明細，算總價的時候，是需要sum多個明細的。
3、如果一對多的多確實沒有意義，那就可以考慮用group by 或者 distinct。
4、具體結構問題具體分析。


參考

1、left join百度百科

2、left join的用法

3、SQL中distinct的用法




只要自己變優秀了，其他的事情才會跟著好起來（少將15）

相關文章