MySQL多个表的关联字段实现同步更新的解决方案

逃跑的面包

1. 背景

在对数据库表结构进行设计时，为了提高查询效率，会进行一些反规范化设计，如：设计一些冗余字段。但这样可能会存在数据同步问题，当源表字段值更新时，冗余字段值也需要同步更新。
现有产品表

1. product

复制代码

，移动端版本

1. mobile_version

复制代码

，授权绑定产品表

1. auth_server_product

复制代码

三个表，这三个表有一个共同字段 productName，期望当 product 表中的 productName 字段的值改变时，将该字段修改后的值同步更新到 mobile_version，auth_server_product 两个表中。

1.1 表结构定义


1.1.1 product 表

1. CREATE TABLE `product`  (
   
2.   `id` int(0) NOT NULL AUTO_INCREMENT,
   
3.   `productName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
   
4.   PRIMARY KEY (`id`) USING BTREE
   
5. ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
   
6. 
   
7. INSERT INTO `product` VALUES (1, '产品A');
   
8. INSERT INTO `product` VALUES (2, '产品B');
   
9. INSERT INTO `product` VALUES (3, '产品C');

复制代码

1.1.2 auth_server_product 表

1. CREATE TABLE `auth_server_product`  (
   
2.   `id` int(0) NOT NULL,
   
3.   `authId` int(0) NULL DEFAULT NULL,
   
4.   `productId` int(0) NULL DEFAULT NULL,
   
5.   `productName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
   
6.   PRIMARY KEY (`id`) USING BTREE
   
7. ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
   
8. 
   
9. INSERT INTO `auth_server_product` VALUES (1, 1, 1, '产品A');
   
10. INSERT INTO `auth_server_product` VALUES (2, 1, 2, '产品B');
    
11. INSERT INTO `auth_server_product` VALUES (3, 1, 3, '产品C');

复制代码

1.1.3 mobile_version 表

1. CREATE TABLE `mobile_version`  (
   
2.   `id` int(0) NOT NULL,
   
3.   `mobile_version` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
   
4.   `productId` int(0) NULL DEFAULT NULL,
   
5.   `productName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
   
6.   PRIMARY KEY (`id`) USING BTREE
   
7. ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
   
8. 
   
9. 
   
10. INSERT INTO `mobile_version` VALUES (1, 'V1.0.0', 1, '产品A');
    
11. INSERT INTO `mobile_version` VALUES (2, 'V1.0.1', 1, '产品A');

复制代码

2. 解决方案


2.1 应用层代码中更新

在代码层实现数据同步逻辑，在更新 product 表中的 productName 字段时，同步更新auth_server_product 与 mobile_version 两表中的字段值。大致逻辑如下：

1. @Transactional
   
2. public void updateProductName(String oldProductName, String newProductName) {
   
3.     // 更新 product 表中的 productName
   
4.     productRepository.updateProductName(oldProductName, newProductName);
   
5.    
   
6.     // 同步更新 mobile_version 表
   
7.     mobileVersionRepository.updateProductName(oldProductName, newProductName);
   
8. 
   
9.     // 同步更新 auth_server_product 表
   
10.     authServerProductRepository.updateProductName(oldProductName, newProductName);
    
11. }

复制代码

1. 该种方案的<strong>劣势</strong>：
   
2. <ul><li>代码复杂，耦合性强；</li><li>数据<strong>不一致</strong>：如果同步逻辑没有被正确实现或处理，可能会导致数据不一致。</li></ul>

复制代码

2.2 利用MySQL提供的触发器功能

MySQL 提供了触发器功能，在随某个表进行记录的新增（

1. INSERT

复制代码

）、修改（

1. UPDATE

复制代码

） 或是删除（

1. DELETE

复制代码

） 操作时，会自动触发相应的操作。
针对上述场景中的这个问题，可以在

1. product

复制代码

表设置触发器，在

1. UPDATE

复制代码

操作时，通过触发器来同步更新

1. mobile_version

复制代码

和

1. auth_server_product

复制代码

表中的

1. productName

复制代码

字段。

2.2.1 具体实现

1. 以 <strong>Navicat</strong> 为例进行演示

复制代码

Step 1 : 创建触发器
设计 procut 表，点击触发器，定义触发器的名称，类型选择

1. AFTER UPDATE

复制代码

，表示在product 表中的 productName 更新后 同步更新其他表。
Step 2 : SQL 定义触发器行为
编写具体的SQL语句定义触发器行为，在这个例子中，期望在

1. productName

复制代码

更新时，自动更新

1. mobile_version

复制代码

和

1. auth_server_product

复制代码

表中的

1. productName

复制代码

。SQL 下：

1. BEGIN
   
2.     -- 如果 productName 发生变化
   
3.     IF OLD.productName <> NEW.productName THEN
   
4.         -- 更新 mobile_version 表中的 productName
   
5.         UPDATE mobile_version
   
6.         SET productName = NEW.productName
   
7.         WHERE productName = OLD.productName;
   
8.         
   
9.         -- 更新 auth_server_product 表中的 productName
   
10.         UPDATE auth_server_product
    
11.         SET productName = NEW.productName
    
12.         WHERE productName = OLD.productName;
    
13.     END IF;
    
14. END

复制代码

Step 3 : 验证

• 1. show triggers

  复制代码
  ，可查看当前数据库中的触发器，验证触发器是否创建成功。
  

• 执行更新语句，验证触发器的定义能否达到期望的效果。
  

1. update product set productName = '产品AA' where id = '1'

复制代码

1. 可以看到，当 product 表中的 productName 值更新后，mobile_version 与 auth_server_product 对应的 productName 值同步更新。

复制代码

2.2.2 关于触发器

• 触发事件：触发器会在某个特定事件发生时被触发。常见的触发事件包括：
  
  
  
  • 1. INSERT

    复制代码
    ：在插入数据时触发。
    
  • 1. UPDATE

    复制代码
    ：在更新数据时触发。
    
  • 1. DELETE

    复制代码
    ：在删除数据时触发。
    
  
  
• 触发时机：触发器可以定义在数据事件发生的 前（
  1. BEFORE

  复制代码
  ）或 后（
  1. AFTER

  复制代码
  ）执行。
  
  
  
  • 1. BEFORE

    复制代码
    ：触发器在数据变更之前执行。
    
  • 1. AFTER

    复制代码
    ：触发器在数据变更之后执行。
    
  
  
• 触发器的作用域：触发器通常绑定到某个表上，并且只能在该表的数据操作时触发。它不能跨表执行，也不能直接返回数据。
  

以上就是MySQL多个表的关联字段实现同步更新的解决方案的详细内容，更多关于MySQL关联字段同步更新的资料请关注脚本之家其它相关文章！

来源:https://www.jb51.net/database/330985zsc.htm
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！