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

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

一、準(zhǔn)備工作

我們首先創(chuàng)建演示用的數(shù)據(jù)庫表，一張是訂單表，里面包含了商品id和商品名稱，一張是商品表，保存了商品的基本信息，兩張表通過商品id進(jìn)行關(guān)聯(lián)，創(chuàng)建完成后，我們向表中插入一些簡(jiǎn)單的測(cè)試數(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 '下單時(shí)間',
  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)建時(shí)間',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

再插入一些簡(jiǎn)單的測(cè)試數(shù)據(jù)：

INSERT INTO `t_order` VALUES ('1', '1', '科幻圖書', '25.00', '2023-08-21 17:16:54');
INSERT INTO `t_order` VALUES ('2', '2', '臺(tái)燈', '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進(jìn)行關(guān)聯(lián)更新

也就是通過INNER JOIN或者LEFT JOIN進(jìn)行關(guān)聯(lián)更新，當(dāng)然，使用RIGHT JOIN也可以，只不過關(guān)聯(lián)的主表變成了右邊的那張，更多是一個(gè)習(xí)慣問題。我們先看看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é)果，之前的商品名稱分別為科幻圖片和臺(tái)燈，執(zhí)行sql后，更新為商品表中對(duì)應(yīng)id的名稱，分別為編程書籍和電飯鍋。執(zhí)行信息顯示，有兩行數(shù)據(jù)受到了影響。

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

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

三、通過子查詢進(jìn)行處理

可以通過子查詢的方式進(jì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ù)，同時(shí)，對(duì)于product_id=128的數(shù)據(jù)，它的商品名稱更新為NULL值，跟LEFT JOIN的效果一樣：

四、直接UPDATE多表

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

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

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

最新評(píng)論