mysql批量更新 批量插入之replace语句/insert into... on duplicate key update语句

mysql批量更新/插入数据有以下方法，使用这些方法批量插入数据/更新数据的时候就不用在代码层次增加判断数据是否已存在的逻辑了。

1:replace语句

2:insert into... on duplicate key update语句

所用mysql版本:

使用语法:

replace into 表名l (id,字段1) values (1,'2'),(2,'3'),...(x,'y');insert into 表名l (id,字段1) values (1,'2'),(2,'3'),...(x,'y') on duplicate key update 字段1=values(字段1);

二者结论:

1:唯一性索引冲突时，两种方式都会增加数据表AUTO_INCREMENT的值。

2:replace遇到主键/唯一索引冲突是先删除再插入，无冲突直接进行insert。

结论验证:

结论1：

表初始数据如下：其中id为主键，并且自增，uni为唯一索引。

a:insert into... on duplicate key update验证:

执行sql:insert into test(phone,email,uni) values ('myphon-5', 'myemail@5', 5) on duplicate key update phone='myphon-5',email = 'myemail@5',uni = 5;

再执行插入一条不指定主键的常规插入的sql:INSERT into test (phone,email, uni) values ('myphon-temp', 'myemail@-temp', 6);

从下表数据可以看出新数据id直接从5跳过6，生成7:

b:replace方式验证:

执行sql:REPLACE into test (phone,email,uni) values ('ph-tempins', 'myemail-tempins', 7);

再执行插入一条新不指定主键的常规插入的sql:INSERT into test (phone,email, uni) values ('myphon-temp', 'myemail@-temp', 8);

从下表数据可以看出新数据id直接从9跳过10，生成11:

结论2：

官方原文:/doc/refman/8.0/en/replace.html

二者详细测试:

I: relace语句不带主键索引与唯一索引:

1:表结构

表初始数据:

执行sql:REPLACE INTO test_11(username, pwd) VALUES('1', '1-pwd-new'); 后

II: relace语句带有主键索引:

1：表结构:

2:插入初始数据:

3:执行sql:REPLACE INTO test(ID, phone, email) VALUES(1, 'my phone', 'myemail@'); 后

III: insert into... on duplicate key update语句带有主键索引:

同样对replace语句测试中的test表执行sql:

insert into test (id,phone,email) values (1, 'myphon-new', 'myemail@-new') on duplicate key update phone='myphon-new', email = 'myemail@-new';

结果:

这里显示受影响行为2行，结合结论1中提到的唯一性索引冲突时，两种方式都会增加数据表AUTO_INCREMENT的值以及官方文档的说明，笔者猜想此种方式执行流程应该是先执行插入操作，诺因为索引唯一性冲突导致插入失败则进行更新操作。关于索引唯一性冲入导致的插入失败会导致数据表AUTO_INCREMENT的值增加，这点可自行验证。

官方文档的描述如下：

官方文档地址:/doc/refman/8.0/en/insert-on-duplicate.html

IIII: insert into... on duplicate key update语句无主键索引，唯一索引:

insert into test_11(username,pwd) values ('1-new', '1-pwd-new') on duplicate key update username='1-new', pwd = '1-pwd-new';

最后需要说的是，理论上insert into... on duplicate key update语句能比replace语句有更好的执行效率，此外replace语句 与 insert into... on duplicate key update语句多事务并发会有死锁的风险，参考：/monthly//03/01/