<span>mysql replace into 的使用情况</span>

replace into的存在的几种情况

当表存在主键并且存在唯一键的时候
- 如果只是主键冲突

mysql> select * from auto; +----+---+------+---------+ | id | k | v | extra | +----+---+------+---------+ | 2 | 2 | 2 | extra 2 | | 3 | 3 | 3 | extra 3 | | 4 | 1 | 1-1 | NULL | +----+---+------+---------+ 3 rows in set (0.00 sec) mysql> mysql> show create table auto\G *************************** 1. row *************************** Table: auto Create Table: CREATE TABLE `auto` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL, `v` varchar(100) DEFAULT NULL, `extra` varchar(200) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_k` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 1 row in set (0.00 sec)

这里我们插入一条主键已经存在的4的数据

mysql> replace into auto(id,k)values(4,5); Query OK, 2 rows affected (0.01 sec) mysql> select * from auto; +----+---+------+---------+ | id | k | v | extra | +----+---+------+---------+ | 2 | 2 | 2 | extra 2 | | 3 | 3 | 3 | extra 3 | | 4 | 5 | NULL | NULL | +----+---+------+---------+ 3 rows in set (0.00 sec) mysql> show create table auto \G *************************** 1. row *************************** Table: auto Create Table: CREATE TABLE `auto` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL, `v` varchar(100) DEFAULT NULL, `extra` varchar(200) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_k` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 1 row in set (0.00 sec)

发现，auto_increment并没有+1，而是针对原来的那一条id=4的记录进行了update，因为没有指定其他列(v,extra)的值，所以，update的时候都使用了默认值.

如果主键跟唯一键都冲突并且在同一行里

mysql> select * from auto; +----+---+------+---------+ | id | k | v | extra | +----+---+------+---------+ | 2 | 2 | 2 | extra 2 | | 3 | 3 | 3 | extra 3 | | 4 | 5 | NULL | NULL | | 5 | 6 | 6 | NULL | +----+---+------+---------+ 4 rows in set (0.00 sec) mysql> show create table auto \G *************************** 1. row *************************** Table: auto Create Table: CREATE TABLE `auto` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL, `v` varchar(100) DEFAULT NULL, `extra` varchar(200) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_k` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> mysql> mysql> replace into auto(id,k,extra)values(5,6,77); Query OK, 2 rows affected (0.01 sec) mysql> select * from auto; +----+---+------+---------+ | id | k | v | extra | +----+---+------+---------+ | 2 | 2 | 2 | extra 2 | | 3 | 3 | 3 | extra 3 | | 4 | 5 | NULL | NULL | | 5 | 6 | NULL | 77 | +----+---+------+---------+ 4 rows in set (0.00 sec) mysql> show create table auto \G *************************** 1. row *************************** Table: auto Create Table: CREATE TABLE `auto` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL, `v` varchar(100) DEFAULT NULL, `extra` varchar(200) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_k` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 1 row in set (0.00 sec)

我们发现，auto_increment也并没有+1，而是针对原来的那一条id=6的记录进行了update，因为没有指定其他列(v)的值，所以，update的时候都v使用了默认值变成了null

如果主键跟唯一键都冲突不在同一行，对应2条记录呢

我们来看下：

mysql> show create table auto \G
*************************** 1. row *************************** Table: auto Create Table: CREATE TABLE `auto` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL, `v` varchar(100) DEFAULT NULL, `extra` varchar(200) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_k` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> select * from auto; +----+---+------+---------+ | id | k | v | extra | +----+---+------+---------+ | 2 | 2 | 2 | extra 2 | | 3 | 3 | 3 | extra 3 | | 4 | 5 | NULL | NULL | | 6 | 6 | 66 | NULL | +----+---+------+---------+ 4 rows in set (0.00 sec) mysql> replace into auto(id,k,v)values(6,2,88); Query OK, 3 rows affected (0.03 sec)

像上面的，主键id=6对应一条记录，唯一索引k=2对应id=2的另外一条记录，所以我们当前插入的记录就会跟2行数据有冲突，我们replace into 看看会有什么结果

mysql> replace into auto(id,k,v)values(6,2,88); Query OK, 3 rows affected (0.03 sec) mysql> select * from auto; +----+---+------+---------+ | id | k | v | extra | +----+---+------+---------+ | 3 | 3 | 3 | extra 3 | | 4 | 5 | NULL | NULL | | 6 | 2 | 88 | NULL | +----+---+------+---------+ 3 rows in set (0.00 sec) mysql> show create table auto \G *************************** 1. row *************************** Table: auto Create Table: CREATE TABLE `auto` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL, `v` varchar(100) DEFAULT NULL, `extra` varchar(200) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_k` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 1 row in set (0.00 sec)

我们发现auto_increment并没有+1，MySQL把原来的id=6的这条记录上进行uppdate，但是发现唯一索引k出现了冲突，所以就把对应冲突的那条数据删除，再进行更新，由于没有指定更新字段extra的数据，所以就把extra更新为默认数据

如果仅仅是唯一键冲突呢？

mysql> select * from auto; +----+---+------+---------+ | id | k | v | extra | +----+---+------+---------+ | 2 | 2 | 2 | extra 2 | | 3 | 3 | 3 | extra 3 | | 4 | 5 | NULL | NULL | | 5 | 6 | NULL | 77 | +----+---+------+---------+ 4 rows in set (0.00 sec) mysql> show create table auto \G *************************** 1. row *************************** Table: auto Create Table: CREATE TABLE `auto` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL, `v` varchar(100) DEFAULT NULL, `extra` varchar(200) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_k` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> mysql> replace into auto(k,v)values(6,66); Query OK, 2 rows affected (0.04 sec) mysql> select * from auto; +----+---+------+---------+ | id | k | v | extra | +----+---+------+---------+ | 2 | 2 | 2 | extra 2 | | 3 | 3 | 3 | extra 3 | | 4 | 5 | NULL | NULL | | 6 | 6 | 66 | NULL | +----+---+------+---------+ 4 rows in set (0.00 sec) mysql> show create table auto \G *************************** 1. row *************************** Table: auto Create Table: CREATE TABLE `auto` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL, `v` varchar(100) DEFAULT NULL, `extra` varchar(200) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_k` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 1 row in set (0.00 sec)

这时候，我们发现，，auto_increment已经+1了。MySQL这时候的执行步骤是，首先往表里面插入一条数据，这时候auto_increment+1,但是在插入的时候发现唯一索引的k冲突了，然后把冲突的这条数据删除，然后重新插入，对于没有指定其他列(extra)的值，如extra都使用了默认值变成了null

现在我们可以下结论了：

当replace into 记录只与主键冲突的时候，auto_increment不会增加，它会对与主键冲突的那一条记录进行更新，没有指定的列将会被更新为默认值
当replace into 记录与主键跟唯一索引同时冲突的时候，auto_increment不会增加
1. 如果冲突的主键和索引在同一行记录，则replace into只做更新，对于没有指定值的其他列，将会被更新为默认值，
2. 如果冲突的主键和索引分别对应2行数据，则MySQL将会删除唯一索引的那一行记录，更新对应主键的那一行记录。
当replace into 记录只与唯一索引进行冲突的时候，auto_increment + 1，再对数据进行更新。

最后我们可以对总结分析下，MySQL对replace into的操作是首先是insert操作，如果insert失败，则对insert失败的这条记录进行update，如果update还是失败，则会进行delete操作之后再update。
具体流程是这样的：insert记录，发现主键冲突，则update这一行，update的时候发现存在唯一键冲突，则delete对应的唯一键的行后再进行update。如果insert成功，auto_increment自然+1了，然后对这条记录进行update，update的时候发现存在唯一键冲突，则delete对应的唯一键的行后再进行update。

发布者：全栈程序员-站长，转载请注明出处：https://javaforall.net/119672.html原文链接：https://javaforall.net