在實際編程工作或運維實踐中，對MySQL數(shù)據(jù)庫表進行關(guān)聯(lián)更新是一種比較常見的應用場景，比如在電商系統(tǒng)中，訂單表里保存了商品名稱的信息（冗余字段設計），但如果商品名稱發(fā)生變化，則需要通過關(guān)聯(lián)商品id，把新的商品名稱更新到訂單表中；或者，學生表中保存了班級信息，但關(guān)聯(lián)的班級表發(fā)生變化，那么學生表也需要同步更新，等等。

針對這樣的業(yè)務場景，我們來看看有什么方法可以實現(xiàn)關(guān)聯(lián)更新，當然，這樣的知識相對比較基礎，資深或者高級專業(yè)人士請繞行，以免留下笑柄，但如果你記得不是很清楚，或者還不是很確定，可以嘗試往下看看。同時，在面試過程中，也經(jīng)常會問起這樣的問題，以考察候選人的基礎知識掌握水平。

一、準備工作

我們首先創(chuàng)建演示用的數(shù)據(jù)庫表，一張是訂單表，里面包含了商品id和商品名稱，一張是商品表，保存了商品的基本信息，兩張表通過商品id進行關(guān)聯(lián)，創(chuàng)建完成后，我們向表中插入一些簡單的測試數(shù)據(jù)。

首先創(chuàng)建兩張表：

CREATE TABLE `t_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `product_id` int(11) DEFAULT NULL COMMENT '商品id',
  `product_name` varchar(32) DEFAULT NULL COMMENT '商品名稱',
  `amout` decimal(10,2) DEFAULT NULL COMMENT '訂單金額',
  `order_time` timestamp NULL DEFAULT NULL COMMENT '下單時間',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `t_product` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `product_name` varchar(32) DEFAULT NULL COMMENT '商品名稱',
  `create_time` timestamp NULL DEFAULT NULL COMMENT '創(chuàng)建時間',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

再插入一些簡單的測試數(shù)據(jù)：

INSERT INTO `t_order` VALUES ('1', '1', '科幻圖書', '25.00', '2023-08-21 17:16:54');
INSERT INTO `t_order` VALUES ('2', '2', '臺燈', '12.00', '2023-08-21 17:17:22');
INSERT INTO `t_order` VALUES ('3', '128', '籃球', '82.00', '2023-08-21 17:18:18');

INSERT INTO `t_product` VALUES ('1', '編程書籍', '2023-08-21 17:15:24');
INSERT INTO `t_product` VALUES ('2', '電飯鍋', '2023-08-21 17:15:27');
INSERT INTO `t_product` VALUES ('3', '加薪神器', '2023-08-21 17:16:00');

執(zhí)行上面的sql后，表中的數(shù)據(jù)是這樣的：

二、通過JOIN進行關(guān)聯(lián)更新

也就是通過INNER JOIN或者LEFT JOIN進行關(guān)聯(lián)更新，當然，使用RIGHT JOIN也可以，只不過關(guān)聯(lián)的主表變成了右邊的那張，更多是一個習慣問題。我們先看看INNER JOIN的情況：

UPDATE t_order o
INNER JOIN t_product p ON o.product_id=p.id
SET o.product_name=p.product_name

SQL如上所示，它把訂單表中的商品名稱字段值更新為商品表中的商品名稱，更新后訂單表的結(jié)果如下：

可以看到，product_id等于1和2的商品名稱，已經(jīng)更新為商品表中的最新結(jié)果，之前的商品名稱分別為科幻圖片和臺燈，執(zhí)行sql后，更新為商品表中對應id的名稱，分別為編程書籍和電飯鍋。執(zhí)行信息顯示，有兩行數(shù)據(jù)受到了影響。

上面是用INNER JOIN進行更新，如果使用LEFT JOIN（sql不用任何其它修改，只把INNER換成LEFT即可），結(jié)果稍有不同：

可以看到，product_id=128的記錄，它的商品名稱被更新為NULL值了，而且執(zhí)行信息也顯示，有三行數(shù)據(jù)受到了影響。這主要是因為內(nèi)聯(lián)接和左聯(lián)接的處理邏輯不同，INNER JOIN是強關(guān)聯(lián)，而對LEFT JOIN來說，即使副表沒有滿足條件的數(shù)據(jù)，也會處理成NULL，詳細區(qū)別可參考相關(guān)資料。

三、通過子查詢進行處理

可以通過子查詢的方式進行關(guān)聯(lián)更新：

UPDATE t_order t
SET t.product_name =
 (SELECT product_name FROM t_product p WHERE t.product_id = p.id)

更新操作也是影響了三行數(shù)據(jù)，同時，對于product_id=128的數(shù)據(jù)，它的商品名稱更新為NULL值，跟LEFT JOIN的效果一樣：

四、直接UPDATE多表

根據(jù)UPDATE語法規(guī)則，它后面可以直接跟隨多個表，表之間使用逗號分隔：

UPDATE t_order o, t_product p 
SET o.product_name=p.product_name
WHERE o.product_id=p.id

執(zhí)行信息提示影響了2行數(shù)據(jù)，它的效果跟INNER JOIN是一樣的，product_id=128的數(shù)據(jù)沒有被更新，還保持原狀：

五、結(jié)尾

多表關(guān)聯(lián)更新也是非常常見的業(yè)務場景，不光是編寫代碼時會碰到這樣的需求，在數(shù)據(jù)庫運維時，也常常會做這樣的操作。完成這樣的需求，有多種不同的實現(xiàn)手段，我們從上面可以看到，這些方法之間也有些細微的差別：不滿足關(guān)聯(lián)條件的數(shù)據(jù)是否也進行了更新。這個就依需求而定了。

以上就是MySQL進行表之間關(guān)聯(lián)更新的實現(xiàn)方法的詳細內(nèi)容，更多關(guān)于MySQL表之間關(guān)聯(lián)更新的資料請關(guān)注腳本之家其它相關(guān)文章！

最新評論