replace into 解析

杨奇龙 yangyidba

一介绍

在支持业务过程中，经常遇到开发咨询replace into 的使用场景以及注意事项，这里做个总结,从功能原理注意事项上做个说明。

二原理

2.1 存在主键但是不存在唯一键

表结构

CREATE TABLE `yy` (
`id` bigint(20) NOT NULL,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
root@test 02:43:58>insert into yy values(1,'abc'),(2,'bbb');
Query OK, 2 row affected (0.00 sec)
root@test 02:55:42>select * from yy;
+----+------+
| id | name |
+----+------+
| 1 | abc |
| 2 | bbb |
+----+------+
2 rows in set (0.00 sec)

如果本来已经存在的主键值，那么MySQL做update操作。

root@test 02:55:56>replace into yy values(1,'ccc');
Query OK, 2 rows affected (0.00 sec)
### UPDATE test.yy
### WHERE
### @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2='abc' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### SET
### @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2='ccc' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */

如果本来相应的主键值没有，那么做insert 操作

replace into yy values(2,'bbb');
### INSERT INTO test.yy
### SET
### @1=2 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2='bbb' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */

2.2 当表中主键和唯一键同时存在时

CREATE TABLE `yy` (
`id` int(11) NOT NULL DEFAULT \'0\',
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL
PRIMARY KEY (`id`),
UNIQUE KEY `uk_bc` (`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

情形1 主键冲突

root@test 04:37:18>replace into yy values(1,2,3);
Query OK, 1 row affected (0.00 sec)
root@test 04:37:37>replace into yy values(2,2,4);
Query OK, 1 row affected (0.00 sec)
root@test 04:38:05>select * from yy;
+----+------+------+
| id | b | c |
+----+------+------+
| 1 | 2 | 3 |
| 2 | 2 | 4 |
+----+------+------+
2 rows in set (0.00 sec)
root@test 04:38:50>replace into yy values(1,2,5);
Query OK, 2 rows affected (0.00 sec)
root@test 04:38:58>select * from yy;
+----+------+------+
| id | b | c |
+----+------+------+
| 2 | 2 | 4 |
| 1 | 2 | 5 |
+----+------+------+
2 rows in set (0.00 sec)

主键冲突时，数据库对表做先删除然后插入的操作，也即先删除id=1的记录，然后插入记录(1,2,5).

### DELETE FROM test.yy
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2=2 /* INT meta=0 nullable=1 is_null=0 */
### @3=3 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO test.yy
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2=2 /* INT meta=0 nullable=1 is_null=0 */
### @3=5 /* INT meta=0 nullable=1 is_null=0 */

情形2 唯一建冲突

root@test 04:48:30>select * from yy;
+----+------+------+
| id | b | c |
+----+------+------+
| 1 | 2 | 4 |
| 2 | 2 | 5 |
| 3 | 3 | 5 |
| 4 | 3 | 6 |
+----+------+------+
4 rows in set (0.00 sec)
root@test 04:53:21>replace into yy values(5,3,6);
Query OK, 2 rows affected (0.00 sec)
root@test 04:53:40>select * from yy;
+----+------+------+
| id | b | c |
+----+------+------+
| 1 | 2 | 4 |
| 2 | 2 | 5 |
| 3 | 3 | 5 |
| 5 | 3 | 6 |
+----+------+------+
4 rows in set (0.00 sec)

主键不冲突，唯一键冲突时，数据库对表唯一键为(3，6)的行做update操作，将主键修改为要插入的值，id=4 改为id=5。

### UPDATE test.yy
### WHERE
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2=3 /* INT meta=0 nullable=1 is_null=0 */
### @3=6 /* INT meta=0 nullable=1 is_null=0 */
### SET
### @1=5 /* INT meta=0 nullable=0 is_null=0 */
### @2=3 /* INT meta=0 nullable=1 is_null=0 */
### @3=6 /* INT meta=0 nullable=1 is_null=0 */

情形3 主键和唯一键同时冲突,如果需要插入的值的主键和唯一和表中已经存在的存在冲突。

root@test 04:53:52>replace into yy values(1,3,6);
Query OK, 3 rows affected (0.00 sec) ---注意此处影响的行数是3
root@test 04:55:35>select * from yy;
+----+------+------+
| id | b | c |
+----+------+------+
| 2 | 2 | 5 |
| 3 | 3 | 5 |
| 1 | 3 | 6 |
+----+------+------+
3 rows in set (0.00 sec)

要插入的值(1,3,6) 主键于表里面的id=1的值冲突，唯一键(3,6)和表中id=5的记录冲突，MySQL 处理的时候，先删除id=1的行，然后更新了id=5的行。

### DELETE FROM test.yy
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2=2 /* INT meta=0 nullable=1 is_null=0 */
### @3=4 /* INT meta=0 nullable=1 is_null=0 */
### UPDATE test.yy
### WHERE
### @1=5 /* INT meta=0 nullable=0 is_null=0 */
### @2=3 /* INT meta=0 nullable=1 is_null=0 */
### @3=6 /* INT meta=0 nullable=1 is_null=0 */
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2=3 /* INT meta=0 nullable=1 is_null=0 */
### @3=6 /* INT meta=0 nullable=1 is_null=0 */

三结论
对表进行replace into操作的时候:
当不存在冲突时,replace into 相当于insert普通的操作。
当存在pk冲突的时候是先delete再insert。如果主键自增,则AUTO_INCREMENT属性不变。

master test 05:39:25> show create table yy \G
*************************** 1. row ***************************
Table: yy
Create Table: CREATE TABLE `yy` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
master test 05:39:35> replace into yy(id,name) values(1,'ccc');
Query OK, 2 rows affected (0.00 sec)
master test 05:39:40> show create table yy \G
*************************** 1. row ***************************
Table: yy
Create Table: CREATE TABLE `yy` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
master test 05:40:41> insert into yy(id) values(3) on duplicate key update name='youzan';
Query OK, 2 rows affected (0.00 sec)
master test 05:41:20> show create table yy \G
*************************** 1. row ***************************
Table: yy
Create Table: CREATE TABLE `yy` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

当存在uk冲突的时候是直接update。如果主键自增的，则AUTO_INCREMENT属性会做 +1 操作。

master test 05:46:31> show create table xx \G
*************************** 1. row ***************************
Table: xx
Create Table: CREATE TABLE `xx` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`b` int(11) NOT NULL DEFAULT '0',
`c` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
master test 06:14:07> replace into xx(b,c) values(2,5);
Query OK, 1 row affected (0.00 sec)
master test 06:14:28> show create table xx \G
*************************** 1. row ***************************
Table: xx
Create Table: CREATE TABLE `xx` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`b` int(11) NOT NULL DEFAULT '0',
`c` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
master test 06:17:10> insert into xx(b,c) values(4,10) on duplicate key update c=10;
Query OK, 2 rows affected (0.00 sec)
master test 06:17:13> show create table xx \G
*************************** 1. row ***************************
Table: xx
Create Table: CREATE TABLE `xx` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`b` int(11) NOT NULL DEFAULT '0',
`c` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

继续滑动看下一个