MySQL進行表之間關(guān)聯(lián)更新的實現(xiàn)方法
在實際編程工作或運維實踐中,對MySQL數(shù)據(jù)庫表進行關(guān)聯(lián)更新是一種比較常見的應(yīng)用場景,比如在電商系統(tǒng)中,訂單表里保存了商品名稱的信息(冗余字段設(shè)計),但如果商品名稱發(fā)生變化,則需要通過關(guān)聯(lián)商品id,把新的商品名稱更新到訂單表中;或者,學(xué)生表中保存了班級信息,但關(guān)聯(lián)的班級表發(fā)生變化,那么學(xué)生表也需要同步更新,等等。
針對這樣的業(yè)務(wù)場景,我們來看看有什么方法可以實現(xiàn)關(guān)聯(lián)更新,當(dāng)然,這樣的知識相對比較基礎(chǔ),資深或者高級專業(yè)人士請繞行,以免留下笑柄,但如果你記得不是很清楚,或者還不是很確定,可以嘗試往下看看。同時,在面試過程中,也經(jīng)常會問起這樣的問題,以考察候選人的基礎(chǔ)知識掌握水平。
一、準(zhǔn)備工作
我們首先創(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)更新,當(dāng)然,使用RIGHT JOIN也可以,只不過關(guān)聯(lián)的主表變成了右邊的那張,更多是一個習(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é)果,之前的商品名稱分別為科幻圖片和臺燈,執(zhí)行sql后,更新為商品表中對應(yīng)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,詳細(xì)區(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è)務(wù)場景,不光是編寫代碼時會碰到這樣的需求,在數(shù)據(jù)庫運維時,也常常會做這樣的操作。完成這樣的需求,有多種不同的實現(xiàn)手段,我們從上面可以看到,這些方法之間也有些細(xì)微的差別:不滿足關(guān)聯(lián)條件的數(shù)據(jù)是否也進行了更新。這個就依需求而定了。
以上就是MySQL進行表之間關(guān)聯(lián)更新的實現(xiàn)方法的詳細(xì)內(nèi)容,更多關(guān)于MySQL表之間關(guān)聯(lián)更新的資料請關(guān)注腳本之家其它相關(guān)文章!
相關(guān)文章
Mysql5.7及以上版本 ONLY_FULL_GROUP_BY報錯的解決方法
這篇文章主要介紹了Mysql5.7及以上版本 ONLY_FULL_GROUP_BY報錯的解決方法,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-03-03mybatis統(tǒng)計每條SQL的執(zhí)行時間的方法示例
這篇文章主要介紹了mybatis統(tǒng)計每條SQL的執(zhí)行時間的方法示例,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-01-01詳解MySQL 重做日志(redo log)與回滾日志(undo logo)
這篇文章主要介紹了MySQL redo與undo日志的相關(guān)資料,幫助大家更好的理解和學(xué)習(xí)MySQL,感興趣的朋友可以了解下2020-08-08MYSQL(電話號碼,身份證)數(shù)據(jù)脫敏的實現(xiàn)
在日常開發(fā)需求中會經(jīng)常遇到數(shù)據(jù)脫敏處理,比如身份證號、手機號,需要使用*進行部分替換顯示。這樣能使敏感隱私信息在一定程度上得到保護。本文就來介紹一下2021-05-05