【MySql 实战】以 sql 的方式多表联动更新数据

用通俗的话来说，即一个更新操作，需要使用关联的表中的数据，来更新目标表。

按照使用的习惯，不管是在 navicat 或者是 java 后台，都需要拆分为起码 2 条 sql 进行操作。

因这类需求为偶尔在 DB 中执行操作，不可能每次都去把后台跑起来；也不可能每次手工查询目标数据，再修改 update 语句去执行

表结构

一个分类表 mdm_classification，其中，会使用字段 parent 套娃自己组合成 一级、二级、三级分类 的数据映射，对应分类的 code 的长度分别为 2位、4位、7位，其中的命名风格为：aa, aabb, aabbccc，即可以通过具体的三级 code 截取获得 一、二级 的 code

一个物料表 mdm_basics，会有自己唯一的 code，并且，通过 mdm_classification_id 绑定自己归属的三级分类，并且有冗余的字段 mdm_product_code1, mdm_product_code2, mdm_product_code3 来再次绑定自己对应的 一、二、三级分类 对应的 code 和 name

场景

先有的一个 code=1 的物料，他绑定的 三级分类的 code 为 1301001，需要切换为 2302001，这个时候，此物料所属的 一级、二级 分类数据也需要同步维护。

常规的解决方案

需要一个后台，对 2302001 做截取拿到的 一、二级分类的 code，去 DB 查询获得 code 对应的分类记录数据，拼接 update sql 后去 DB 执行，数据更新入库

sql 解决方案

对分类表自连接补全数据

通过 左连接 的方式，为我们需要查询的三级分类的记录，补全对应的 一二级分类的 code、name

SELECT
        c3.id c3id,
        c1.mdm_code c1code,
        c1.mdm_name c1name,
        c2.mdm_code c2code,
        c2.mdm_name c2name,
        c3.mdm_code c3code,
        c3.mdm_name c3name
FROM
        mdm_classification c3
        LEFT JOIN mdm_classification c2 ON c2.mdm_code = left(c3.mdm_code,4)
        LEFT JOIN mdm_classification c1 ON c1.mdm_code = left(c3.mdm_code,2)
WHERE
        c3.mdm_code = '2302001' 

联表查询后的效果如下

c3id	c1code	c1name	c2code	c2name	c3code	c3name
***750b02	23	标准件	2302	铆钉	2302001	标准件半圆头铆钉

返回的结果记录中，已经具备有我们需要的全部数据了

联表更新

联表更新的模版如下所示：

UPDATE 
    tab1 a,
    tab2 b
SET
    a.xx = b.xx [, ...]
[WHERE conditions...]

核心的思路，使用 表B 的数据，来更新 表A 的记录

注意点

因为 表B 相当于是更新需要的数据源，我们可以使用临时表来查询组合得到需要的数据集；而 表A 是更新的主体，则必须要为数据库中现有的表。 如果 表A 也使用临时表，则 mysql 会因为无法定位到实际需要更新的位置而报错。

如下图示，左侧 为 正确 的操作示例，右侧 为将临时表当成更新主体表的 错误 示例：

成品 sql

UPDATE 
    mdm_basics b,
    (SELECT
            c3.id c3id,
            c1.mdm_code c1code,
            c1.mdm_name c1name,
            c2.mdm_code c2code,
            c2.mdm_name c2name,
            c3.mdm_code c3code,
            c3.mdm_name c3name 
    FROM
            mdm_classification c3
            LEFT JOIN mdm_classification c2 ON c2.mdm_code = LEFT ( c3.mdm_code, 4 )
            LEFT JOIN mdm_classification c1 ON c1.mdm_code = LEFT ( c3.mdm_code, 2 ) 
    WHERE
            c3.mdm_code = '2302001' 
    ) c 
SET 
    b.mdm_classification_id = c.c3id,
    b.mdm_group = c.c3code,
    b.mdm_product_code3 = c.c3code,
    b.mdm_classification_name = c.c3name,
    b.mdm_product_name3 = c.c3name,
    b.mdm_product_code1 = c.c1code,
    b.mdm_product_name1 = c.c1name,
    b.mdm_product_code2 = c.c2code,
    b.mdm_product_name2 = c.c2name,
    b.update_date = now() 
WHERE
    b.mdm_code IN (
        '20031500' 
    )

总结

mysql 的强大，实际上已经超乎我们的想象，而占据我们日常开发中绝大部分工作的 CURD，也不过是 mysql 中的冰山一角。不要让我们的惯性思维，限制了我们对 mysql 的能力的探索

题外话：已解锁的能力

这些都是，在 DB 中，通过 sql 的方式解决问题：

高度聚合的数据项拆分为多行多列，将按特定分隔符特定格式组合而成的数据，用 sql 拆分

复杂触发器案例分享，通过触发器，实现日志记录，且值记录变更的字段的前后值

多行数据转化为同一行多列显示，通过 sql，将 DB 中数据显示的维度，由行，转化为列

批量更新，使用 mysql 的特性，1条 sql 解决千人千面式的数据更新（每条数据变更的点都不同）

---

原创文章，未经允许，禁止转载

create by：安逸的咸鱼