在插入大量数据时可能同时遇到两个问题:
-
如果每条更新执行一次sql性能很低,也容易造成阻塞;
-
批量插入时又有可能遇到主键 或者 唯一索引重复的问题,导致整个批量插入失败
使用 ON DUPLICATE KEY UPDATE 一条sql解决批量插入和主键或者唯一索引重复问题
举例如下:
表结构如下:
create table mytable(
id int not null,
code char(6) not null,
description char(50) null,
number int not null,
name char(50) not null,
primary key id,
unique key uk_mytable_code (code));
批量插入如下:
INSERT INTO mytable(id, code, description, number, name)
VALUES (?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?)
ON DUPLICATE KEY UPDATE description = VALUES(description), number = VALUES(number)
//description=
VALUES
(description),number = VALUES
(number)
表示出现在
values
中某列的id字段值与表中已有id字段值重复
(或者code的值与表中已有code字段值重复)时,会更新对应已存在记录的
这两个字段
//还可以指定其它值或进行运算:number=number+ 1,number=number- 1
//因为这里未指定列name, 所以遇到重复id(或者code)列,表中该列的name字段不会更新
//如果某列作为新记录被插入,则受影响行的值为1;如果表中原有的记录被更新,则受影响行的值为2