MySQL中的ON DUPLICATE KEY UPDATE(插入更新)用法

业务场景

在设计数据库表时，通常会为某些列加PRIMARY KEY(主键约束)和UNIQUE KEY(唯一约束)，

再插入数据的时候，如果表中存在相同的唯一约束列，就更新该条记录，否则就插入新的记录。

示例

1.创建数据库,插入测试数据

CREATE TABLE `t_product_amount` (
  `id` INT(10) NOT NULL,
  `amount` INT(10) NOT NULL COMMENT '库存',
  `product_id` INT(10) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_product_amount_product_id` (`product_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

INSERT INTO t_product_amount VALUES(1,5,1);

INSERT INTO t_product_amount VALUES(2,5,2);

INSERT INTO t_product_amount VALUES(3,5,3);

   

2.测试ON DUPLICATE KEY UPDATE的使用

   执行脚本：

INSERT INTO t_product_amount VALUES(4,5,3) ON DUPLICATE KEY UPDATE amount = amount + 5;

   结果：

   

  测试发现数据并没有插入，而是根据唯一约束的列将以有的数据更新

  使用注意事项：

  1.如果行作为新记录被插入，则受影响行的值为1；如果原有的记录被更新，则受影响行的值为2。

  2.如果id，product_id与多个行向匹配，则只有一行被更新。应该尽量避免对带有多个唯一约束的表

    使用ON DUPLICATE KEY子句，执行如下脚本

INSERT INTO t_product_amount VALUES(2,5,3) ON DUPLICATE KEY UPDATE amount = amount + 5;

   执行结果

   

  3.ON DUPLICATE KEY UPDATE 特别适用于多行插入,可以在UPDATE子句中使用VALUES(col_name)函数

  从INSERT...UPDATE 语句的INSERT部分引用列值。

  如果没有发生重复关键字冲突，则UPDATE子句中的VALUES(col_name)可以引用被插入的col_name的值。

  本函数特别适用于多行插入。VALUES()函数只在INSERT...UPDATE语句中有意义，其它时候会返回NULL。

  示例：

INSERT INTO t_product_amount (id,amount,product_id) VALUES (4,2,5),(5,5,6) 
ON DUPLICATE KEY UPDATE amount=amount+VALUES(amount);