快捷導(dǎo)航

MySQL多個表的關(guān)聯(lián)字段實現(xiàn)同步更新的解決方案

更新時間：2024年11月19日 11:50:42 作者：離開地球表面_99

在對數(shù)據(jù)庫表結(jié)構(gòu)進行設(shè)計時,為了提高查詢效率,會進行一些反規(guī)范化設(shè)計,如：設(shè)計一些冗余字段,但這樣可能會存在數(shù)據(jù)同步問題,故本文給大家介紹了MySQL多個表的關(guān)聯(lián)字段實現(xiàn)同步更新的解決方案,需要的朋友可以參考下

1. 背景

在對數(shù)據(jù)庫表結(jié)構(gòu)進行設(shè)計時，為了提高查詢效率，會進行一些反規(guī)范化設(shè)計，如：設(shè)計一些冗余字段。但這樣可能會存在數(shù)據(jù)同步問題，當源表字段值更新時，冗余字段值也需要同步更新。

現(xiàn)有產(chǎn)品表 product ，移動端版本 mobile_version ，授權(quán)綁定產(chǎn)品表auth_server_product 三個表，這三個表有一個共同字段 productName，期望當 product 表中的 productName 字段的值改變時，將該字段修改后的值同步更新到 mobile_version，auth_server_product 兩個表中。

1.1 表結(jié)構(gòu)定義

1.1.1 product 表

CREATE TABLE `product`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `productName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

INSERT INTO `product` VALUES (1, '產(chǎn)品A');
INSERT INTO `product` VALUES (2, '產(chǎn)品B');
INSERT INTO `product` VALUES (3, '產(chǎn)品C');

1.1.2 auth_server_product 表

CREATE TABLE `auth_server_product`  (
  `id` int(0) NOT NULL,
  `authId` int(0) NULL DEFAULT NULL,
  `productId` int(0) NULL DEFAULT NULL,
  `productName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

INSERT INTO `auth_server_product` VALUES (1, 1, 1, '產(chǎn)品A');
INSERT INTO `auth_server_product` VALUES (2, 1, 2, '產(chǎn)品B');
INSERT INTO `auth_server_product` VALUES (3, 1, 3, '產(chǎn)品C');

1.1.3 mobile_version 表

CREATE TABLE `mobile_version`  (
  `id` int(0) NOT NULL,
  `mobile_version` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `productId` int(0) NULL DEFAULT NULL,
  `productName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;


INSERT INTO `mobile_version` VALUES (1, 'V1.0.0', 1, '產(chǎn)品A');
INSERT INTO `mobile_version` VALUES (2, 'V1.0.1', 1, '產(chǎn)品A');

2. 解決方案

2.1 應(yīng)用層代碼中更新

在代碼層實現(xiàn)數(shù)據(jù)同步邏輯，在更新 product 表中的 productName 字段時，同步更新auth_server_product 與 mobile_version 兩表中的字段值。大致邏輯如下：

@Transactional
public void updateProductName(String oldProductName, String newProductName) {
    // 更新 product 表中的 productName
    productRepository.updateProductName(oldProductName, newProductName);
    
    // 同步更新 mobile_version 表
    mobileVersionRepository.updateProductName(oldProductName, newProductName);

    // 同步更新 auth_server_product 表
    authServerProductRepository.updateProductName(oldProductName, newProductName);
}

該種方案的劣勢：

代碼復(fù)雜，耦合性強；
數(shù)據(jù)不一致：如果同步邏輯沒有被正確實現(xiàn)或處理，可能會導(dǎo)致數(shù)據(jù)不一致。

2.2 利用MySQL提供的觸發(fā)器功能

MySQL 提供了觸發(fā)器功能，在隨某個表進行記錄的新增（INSERT）、修改（UPDATE）或是刪除（ DELETE）操作時，會自動觸發(fā)相應(yīng)的操作。

針對上述場景中的這個問題，可以在 product 表設(shè)置觸發(fā)器，在 UPDATE 操作時，通過觸發(fā)器來同步更新 mobile_version 和 auth_server_product 表中的 productName 字段。

2.2.1 具體實現(xiàn)

以 Navicat 為例進行演示

Step 1 : 創(chuàng)建觸發(fā)器

設(shè)計 procut 表，點擊觸發(fā)器，定義觸發(fā)器的名稱，類型選擇AFTER UPDATE，表示在product 表中的 productName 更新后 同步更新其他表。

Step 2 : SQL 定義觸發(fā)器行為

編寫具體的SQL語句定義觸發(fā)器行為，在這個例子中，期望在 productName 更新時，自動更新 mobile_version 和 auth_server_product 表中的 productName。SQL 下：

BEGIN
    -- 如果 productName 發(fā)生變化
    IF OLD.productName <> NEW.productName THEN
        -- 更新 mobile_version 表中的 productName
        UPDATE mobile_version
        SET productName = NEW.productName
        WHERE productName = OLD.productName;
        
        -- 更新 auth_server_product 表中的 productName
        UPDATE auth_server_product
        SET productName = NEW.productName
        WHERE productName = OLD.productName;
    END IF;
END

Step 3 : 驗證

show triggers，可查看當前數(shù)據(jù)庫中的觸發(fā)器，驗證觸發(fā)器是否創(chuàng)建成功。

執(zhí)行更新語句，驗證觸發(fā)器的定義能否達到期望的效果。

update product set productName = '產(chǎn)品AA' where id = '1'

可以看到，當 product 表中的 productName 值更新后，mobile_version 與 auth_server_product 對應(yīng)的 productName 值同步更新。

2.2.2 關(guān)于觸發(fā)器

觸發(fā)事件：觸發(fā)器會在某個特定事件發(fā)生時被觸發(fā)。常見的觸發(fā)事件包括：
- INSERT：在插入數(shù)據(jù)時觸發(fā)。
- UPDATE：在更新數(shù)據(jù)時觸發(fā)。
- DELETE：在刪除數(shù)據(jù)時觸發(fā)。
觸發(fā)時機：觸發(fā)器可以定義在數(shù)據(jù)事件發(fā)生的前（BEFORE）或后（AFTER）執(zhí)行。
- BEFORE：觸發(fā)器在數(shù)據(jù)變更之前執(zhí)行。
- AFTER：觸發(fā)器在數(shù)據(jù)變更之后執(zhí)行。
觸發(fā)器的作用域：觸發(fā)器通常綁定到某個表上，并且只能在該表的數(shù)據(jù)操作時觸發(fā)。它不能跨表執(zhí)行，也不能直接返回數(shù)據(jù)。