replace into语法浅析

  • 2019 年 11 月 6 日
  • 筆記

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 操作。

VirMach 便宜 VPS

QNews

QNews