MySQL之"数据库中没有就创建，有就修改"ON DUPLICATE KEY UPDATE

一.场景

当你想存入一条数据到扩展表中（主表下附表），但这个扩展表并非一定会创建，就会让其工程逻辑复杂化

（也就是说：有可能创建主表数据的同时不会创建扩展表数据，这样就会照成你想修改的时候，扩展表本身是没有数据的，导致你可能要先创建数据，但这样就需要在调用一次创建方法，并且要做逻辑上的判断）

而本次介绍的SQL功能就是：解决如果数据库中该表没有数据就创建，有数据就修改的需求

优点：

方便、优雅

缺点：

高并发的场景下，容易锁死，所以还是不太建议使用的

msyql的innodb5.0以上版本有很多的陷阱，即有可能导致death lock死锁也有可能导致主从模式下的replication产生数据不一致

产生death lock原理

insert ... on duplicate key 在执行时，innodb引擎会先判断插入的行是否产生重复key错误，如果存在，在对该现有的行加上S（共享锁）锁，如果返回该行数据给mysql,然后mysql执行完duplicate后的update操作，然后对该记录加上X（排他锁），最后进行update写入。

可参考文章：https://www.2cto.com/database/201711/695662.html

MySQL文章：https://bugs.mysql.com/bug.php?id=52020

注意：

①一个唯一键，或者主键（insert into列中包含该键）

②不能跟where条件

③表中有多个唯一键时可能造成死锁，使用时注意

强调一点：

on duplicate key update后面跟全部更新的字段=值，也就是说insert into填写values()中的值，全部以key=value的形式填写在update后面,否则会出现不更新，或者更新某些字段的情况！！！

二.格式

 

INSERT INTO 表名(添加的字段1, 添加的字段2) VALUES ("字段1的值", 字段2的值) ON DUPLICATE KEY UPDATE 修改的字段= VALUES(修改的字段)

三.案例

根据SKU调整下载视频次数

• product_sku   SKU
• download_video_number  下载视频次数

# 这样如果product_extend表中没有数据就直接执行INSERT INTO 创建，有值就执行UPDATE修改download_video_number字段
INSERT INTO product_extend (product_sku, download_video_number)
        VALUES ("wzw", 2)
        ON DUPLICATE KEY UPDATE download_video_number = VALUES(download_video_number)

 

四.扩展

直接SQL执行

在查资料的时候发现了另一种新的写法可用于参考：这里的区别是直接用SQL查询直接替代传值，只需要传入条件product_sku就可以了

INSERT INTO order_burst_sku (order_id, sku, quantity, create_time)
        (
            SELECT t2.order_id order_id, t2.product_sku sku, SUM(t2.sale_quantity) quantity, NOW()
            FROM order t1
            LEFT JOIN order_item t2 ON t1.platform_order_id = t2.order_id
            WHERE 1 = 1t2.product_sku = 'wzw'
            GROUP BY t2.order_id, t2.product_sku
        ) ON DUPLICATE KEY UPDATE quantity = VALUES(quantity)

 

亦可以参考以下格式