mysql连表更新慢 mysql联表更新问题

转载

blueice 2023-08-02 08:23:31

文章标签 mysql连表更新慢数据库面试 mysql 后端 文章分类 MySQL 数据库

mysql连表更新慢 mysql联表更新问题_数据库

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

按照使用的习惯，不管是在 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 会因为无法定位到实际需要更新的位置而报错。

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

mysql连表更新慢 mysql联表更新问题_面试_02

成品 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' 
    )