replace into语法浅析

AsiaYe

发布于 2019-11-06 17:20:13

3.3K0

发布于 2019-11-06 17:20:13

文章被收录于专栏：DBA随笔DBA随笔

replace into语法浅析

今天有个开发问我replace into和insert into哪个效率高，就我了解，replace是会首先判断这个值在不在，如果在的话，则进行更新操作，否则进行插入操作。拍脑门一想，当然是insert into的效率高，不过replace into确实可以避免一些问题出现，比如duplicate key的问题。

于是我对这个语法做了个试验，大概分为下面几种情况，介绍如下：

当表中存在主键但是不存在唯一建的时候：

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

我们看看binlog当中记录的内容，首先是：

### INSERT INTO test.yyz
### SET
### @1=2 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2='bbb' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
# at 623
#190617 9:55:42 server id 136403306 end_log_pos 650 Xid = 6090885569

可以看到，针对不存在的id=2，replace做的是insert的操作，而对于已经存在的id=1的记录，replace一个(1，'ccc')的记录是，二进制日志中的结果是：

### UPDATE test.yyz
### 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 */

可以看到，直接是一个update的操作。

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

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

root@test >replace into yyz values(1,2,3);
Query OK, 1 row affected (0.00 sec)
root@test >replace into yyz values(2,2,4);
Query OK, 1 row affected (0.00 sec)
root@test >select * from yyz;
+----+------+------+
| id |   b  |   c  |
+----+------+------+
| 1  |   2  |   3  |
| 2  |   2  |   4  |
+----+------+------+
2 rows in set (0.00 sec)
root@test >replace into yyz values(1,2,5);
Query OK, 2 rows affected (0.00 sec)
root@test >select * from yyz;
+----+------+------+
| id |   b  |   c  |
+----+------+------+
|  2 |   2  |   4  |
|  1 |   2  |   5  |
+----+------+------+
2 rows in set (0.00 sec)

上面的情况，发生了主键冲突，我们看看binlog，结果如下：

BINLOG '
Io5hVROWYHC+KwAAAEICAAAAAMoMAAAAAAEABHRlc3QAAnl5AAMDAwMABg==
Io5hVRmWYHC+KgAAAGwCAAAAAMoMAAAAAAAAA//4AQAAAAIAAAADAAAA
### DELETE FROM test.yyz
### 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 */
Io5hVReWYHC+KgAAAJYCAAAAAMoMAAAAAAEAA//4AQAAAAIAAAAFAAAA
'/*!*/;
### INSERT INTO test.yyz
### 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 */
# at 662
#190617 19:38:58 server id 3195035798 end_log_pos 689 Xid = 22962508
COMMIT/*!*/

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

如果在这种情况下，唯一键发生冲突呢？再来看：

root@test >select * from yyz;
+----+------+------+
| id |   b  |   c  |
+----+------+------+
|  1 |   2  |   4  |
|  2 |   2  |   5  |
|  3 |   3  |   5  |
|  4 |   3  |   6  |
+----+------+------+
4 rows in set (0.00 sec)
root@test >replace into yyz values(5,3,6);
Query OK, 2 rows affected (0.00 sec)
root@test >select * from yyz;
+----+------+------+
| id |   b  |   c  |
+----+------+------+
|  1 |   2  |   4  |
|  2 |   2  |   5  |
|  3 |   3  |   5  |
|  5 |   3  |   6  |
+----+------+------+
4 rows in set (0.00 sec)

再来看看binlog中的内容：

BINLOG \'
lJFhVROWYHC+KwAAANoAAAAAAMoMAAAAAAEABHRlc3QAAnl5AAMDAwMABg==
lJFhVRiWYHC+OAAAABIBAAAAAMoMAAAAAAEAA///+AQAAAADAAAABgAAAPgFAAAAAwAAAAYAAAA=
\'/*!*/;
### UPDATE test.yyz
### 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 */
# at 274
#190617 19:53:40 server id 3195035798 end_log_pos 301 Xid = 22962872
COMMIT/*!*/

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

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

4 rows in set (0.00 sec)root@test >replace into yyz values(1,3,6);

可以看到，replace中id=1和第一条记录冲突，而b=3，c=6和最后一天记录冲突，我们可以看看binlog中的结果：

BINLOG \'
B5JhVROWYHC+KwAAAJwBAAAAAMoMAAAAAAEABHRlc3QAAnl5AAMDAwMABg==
B5JhVRmWYHC+KgAAAMYBAAAAAMoMAAAAAAAAA//4AQAAAAIAAAAEAAAA
### DELETE FROM test.yyz
### 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 */
B5JhVRiWYHC+OAAAAP4BAAAAAMoMAAAAAAEAA///+AUAAAADAAAABgAAAPgBAAAAAwAAAAYAAAA=
\'/*!*/;
### UPDATE test.yyz
### 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 */
# at 510
#190617 19:55:35 server id 3195035798 end_log_pos 537 Xid = 22962904
COMMIT/*!*/

我们可以看到，相当于删除了id=1的记录，然后将最后一条记录的id进行了一个update的操作，改为了1。

最终结论总结如下：

 对表进行replace into操作的时候，
如果表只包含主键：
   当不存在冲突时,replace into 相当于insert操作。
   当存在冲突时,replace into 相当于update操作。
如果表包含主键和唯一性索引：
   当不存在冲突时,replace into 相当于insert操作。 
   当存在主键冲突的时候是先delete再insert,如果主键是自增的，则自增主键会做 +1 操作。
   当存在唯一性索引冲突的时候是直接update。,如果主键是自增的，则自增主键会做 +1 操作。

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2019-06-17，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库

sql

本文分享自 DBA随笔微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

数据库

sql

登录后参与评论

0 条评论

热度

replace into语法浅析

replace into语法浅析

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐