MySQL replace into行为解析

我们知道replace into和insert into行为有区别，当数据冲突，insert into会直接报错退出，而replace into则不受影响，有诸多场景有用到，性能略低于insert into，充分了解replace into的行为，有利于我们的问题排查。

总结：

1、不冲突，replace执行语句为insert。

2、只存在主键或只存在唯一索引冲突，replace执行update。

3、同时存在主键和唯一索引，唯一索引冲突，replace执行update。

4、同时存在主键和唯一索引，主键冲突，replace先delete再insert。

5、同时存在主键和唯一索引冲突，同一行数据，replace先delete再insert。

6、同时存在主键和唯一索引冲突，不同一行数据，replace先delete再update

下面是详细的验证测试

一、不存在冲突

CREATE TABLE `test` (  `id` int(11) NOT NULL,  `age` int(10) DEFAULT NULL,  `name` varchar(100) NOT NULL DEFAULT '') ENGINE=InnoDB DEFAULT CHARSET=utf8
mysql> insert into test (id,age,name) value (1,1,'t1');Query OK, 1 row affected (0.04 sec)
mysql> insert into test (id,age,name) value (2,2,'t2');Query OK, 1 row affected (0.06 sec)
#执行replacemysql> replace into test (id,age,name) value (2,2,'t2');Query OK, 1 row affected (0.02 sec)

结论：解析观察binlog，执行语句为insert into

#210626 16:20:46 server id 494433  end_log_pos 2674 CRC32 0x21a8c110    Rows_query# replace into test (id,age,name) value (2,2,'t2')# at 2674#210626 16:20:46 server id 494433  end_log_pos 2739 CRC32 0xdb8ed853    Table_map: `test20210626`.`test` mapped to number 157# at 2739#210626 16:20:46 server id 494433  end_log_pos 2787 CRC32 0x37105717    Write_rows: table id 157 flags: STMT_END_F### INSERT INTO `test20210626`.`test`### SET###   @1=2 /* INT meta=0 nullable=0 is_null=0 */###   @2=2 /* INT meta=0 nullable=1 is_null=0 */###   @3='t2' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */# at 2787#210626 16:20:46 server id 494433  end_log_pos 2818 CRC32 0x355696c3    Xid = 6382446COMMIT/*!*/;

二、只存在主键冲突

CREATE TABLE `test` (  `id` int NOT NULL,  `age` int DEFAULT NULL,  `name` varchar(100) NOT NULL DEFAULT '',  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8
mysql> select * from test;+----+------+------+| id | age  | name |+----+------+------+|  1 |    1 | t1   ||  2 |    2 | t2   |+----+------+------+2 rows in set (0.00 sec)
mysql> replace into test (id,age,name) value (2,3,'t3');Query OK, 2 rows affected (0.05 sec)
mysql> select * from test;+----+------+------+| id | age  | name |+----+------+------+|  1 |    1 | t1   ||  2 |    3 | t3   |+----+------+------+2 rows in set (0.00 sec)

结论：解析观察binlog，执行语句为update

#210626 16:31:18 server id 494433  end_log_pos 2300 CRC32 0xc2297969    Rows_query# replace into test (id,age,name) value (2,3,'t3')# at 2300#210626 16:31:18 server id 494433  end_log_pos 2365 CRC32 0x39434e88    Table_map: `test20210626`.`test` mapped to number 158# at 2365#210626 16:31:18 server id 494433  end_log_pos 2427 CRC32 0xa2069bc8    Update_rows: table id 158 flags: STMT_END_F### UPDATE `test20210626`.`test`### WHERE###   @1=2 /* INT meta=0 nullable=0 is_null=0 */###   @2=2 /* INT meta=0 nullable=1 is_null=0 */###   @3='t2' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */### SET###   @1=2 /* INT meta=0 nullable=0 is_null=0 */###   @2=3 /* INT meta=0 nullable=1 is_null=0 */###   @3='t3' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */# at 2427#210626 16:31:18 server id 494433  end_log_pos 2458 CRC32 0xe4b46769    Xid = 6387636COMMIT/*!*/;

注意：就算主键冲突，如果数据一致，是不会产生binlog

mysql> select * from test;+----+------+------+| id | age  | name |+----+------+------+|  1 |    1 | t1   ||  2 |    3 | t3   |+----+------+------+2 rows in set (0.00 sec)
mysql> replace into test (id,age,name) value (2,3,'t3');Query OK, 1 row affected (0.02 sec)
mysql> select * from test;+----+------+------+| id | age  | name |+----+------+------+|  1 |    1 | t1   ||  2 |    3 | t3   |+----+------+------+2 rows in set (0.00 sec)

三、只存在唯一索引冲突

CREATE TABLE `test` (  `id` int NOT NULL,  `age` int DEFAULT NULL,  `name` varchar(100) NOT NULL DEFAULT '',  UNIQUE KEY `uni_age` (`age`)) ENGINE=InnoDB DEFAULT CHARSET=utf8
mysql> select * from test;+----+------+------+| id | age  | name |+----+------+------+|  1 |    1 | t1   ||  2 |    3 | t3   |+----+------+------+2 rows in set (0.00 sec)
mysql> replace into test (id,age,name) value (3,3,'t4');Query OK, 2 rows affected (0.10 sec)
mysql> select * from test;+----+------+------+| id | age  | name |+----+------+------+|  1 |    1 | t1   ||  3 |    3 | t4   |+----+------+------+2 rows in set (0.00 sec)

结论：解析观察binlog，执行语句为update

#210626 16:41:07 server id 494433  end_log_pos 3048 CRC32 0xc827ea74    Rows_query# replace into test (id,age,name) value (3,3,'t4')# at 3048#210626 16:41:07 server id 494433  end_log_pos 3113 CRC32 0x36bc154e    Table_map: `test20210626`.`test` mapped to number 160# at 3113#210626 16:41:07 server id 494433  end_log_pos 3175 CRC32 0x4645ee21    Update_rows: table id 160 flags: STMT_END_F### UPDATE `test20210626`.`test`### WHERE###   @1=2 /* INT meta=0 nullable=0 is_null=0 */###   @2=3 /* INT meta=0 nullable=1 is_null=0 */###   @3='t3' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */### SET###   @1=3 /* INT meta=0 nullable=0 is_null=0 */###   @2=3 /* INT meta=0 nullable=1 is_null=0 */###   @3='t4' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */# at 3175#210626 16:41:07 server id 494433  end_log_pos 3206 CRC32 0x1f29302f    Xid = 6392477COMMIT/*!*/;

四、同时存在主键和唯一索引

1、只有主键冲突

CREATE TABLE `test` (  `id` int NOT NULL,  `age` int DEFAULT NULL,  `name` varchar(100) NOT NULL DEFAULT '',  PRIMARY KEY (`id`),  UNIQUE KEY `uni_age` (`age`)) ENGINE=InnoDB DEFAULT CHARSET=utf8
mysql> select * from test;+----+------+------+| id | age  | name |+----+------+------+|  1 |    1 | t1   ||  3 |    3 | t4   |+----+------+------+2 rows in set (0.00 sec)
mysql> replace into test (id,age,name) value (3,5,'t5');Query OK, 2 rows affected (0.05 sec)
mysql> select * from test;+----+------+------+| id | age  | name |+----+------+------+|  1 |    1 | t1   ||  3 |    5 | t5   |+----+------+------+2 rows in set (0.00 sec)

结论：解析观察binlog，执行语句为先delete再inser

#210626 16:44:53 server id 494433  end_log_pos 31707 CRC32 0x44596272   Rows_query# replace into test (id,age,name) value (3,5,'t5')# at 31707#210626 16:44:53 server id 494433  end_log_pos 31772 CRC32 0xa9536744   Table_map: `test20210626`.`test` mapped to number 161# at 31772#210626 16:44:53 server id 494433  end_log_pos 31820 CRC32 0xa4134b0b   Delete_rows: table id 161# at 31820#210626 16:44:53 server id 494433  end_log_pos 31868 CRC32 0xc239819f   Write_rows: table id 161 flags: STMT_END_F### DELETE FROM `test20210626`.`test`### WHERE###   @1=3 /* INT meta=0 nullable=0 is_null=0 */###   @2=3 /* INT meta=0 nullable=1 is_null=0 */###   @3='t4' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */### INSERT INTO `test20210626`.`test`### SET###   @1=3 /* INT meta=0 nullable=0 is_null=0 */###   @2=5 /* INT meta=0 nullable=1 is_null=0 */###   @3='t5' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */# at 31868#210626 16:44:53 server id 494433  end_log_pos 31899 CRC32 0xd5b9aea6   Xid = 6394330COMMIT/*!*/;

2、只有唯一索引冲突

CREATE TABLE `test` (  `id` int NOT NULL,  `age` int DEFAULT NULL,  `name` varchar(100) NOT NULL DEFAULT '',  PRIMARY KEY (`id`),  UNIQUE KEY `uni_age` (`age`)) ENGINE=InnoDB DEFAULT CHARSET=utf8
mysql> select * from test;+----+------+------+| id | age  | name |+----+------+------+|  1 |    1 | t1   ||  3 |    5 | t5   |+----+------+------+2 rows in set (0.00 sec)
mysql> replace into test (id,age,name) value (6,5,'t6');Query OK, 2 rows affected (0.03 sec)
mysql> select * from test;+----+------+------+| id | age  | name |+----+------+------+|  1 |    1 | t1   ||  6 |    5 | t6   |+----+------+------+2 rows in set (0.00 sec)

结论：解析观察binlog，执行语句为update

#210626 16:49:06 server id 494433  end_log_pos 63923 CRC32 0xcc88f504   Rows_query# replace into test (id,age,name) value (6,5,'t6')# at 63923#210626 16:49:06 server id 494433  end_log_pos 63988 CRC32 0x1a749ecf   Table_map: `test20210626`.`test` mapped to number 161# at 63988#210626 16:49:06 server id 494433  end_log_pos 64050 CRC32 0x2ac7bec8   Update_rows: table id 161 flags: STMT_END_F### UPDATE `test20210626`.`test`### WHERE###   @1=3 /* INT meta=0 nullable=0 is_null=0 */###   @2=5 /* INT meta=0 nullable=1 is_null=0 */###   @3='t5' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */### SET###   @1=6 /* INT meta=0 nullable=0 is_null=0 */###   @2=5 /* INT meta=0 nullable=1 is_null=0 */###   @3='t6' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */# at 64050#210626 16:49:06 server id 494433  end_log_pos 64081 CRC32 0xe37a1135   Xid = 6396411COMMIT/*!*/;

3、同时存在主键和唯一索引冲突，同一行数据

CREATE TABLE `test` (  `id` int NOT NULL,  `age` int DEFAULT NULL,  `name` varchar(100) NOT NULL DEFAULT '',  PRIMARY KEY (`id`),  UNIQUE KEY `uni_age` (`age`)) ENGINE=InnoDB DEFAULT CHARSET=utf8
mysql> select * from test;+----+------+------+| id | age  | name |+----+------+------+|  1 |    1 | t1   ||  6 |    5 | t6   |+----+------+------+2 rows in set (0.01 sec)
mysql> replace into test (id,age,name) value (6,5,'t7');Query OK, 2 rows affected (0.05 sec)
mysql> select * from test;+----+------+------+| id | age  | name |+----+------+------+|  1 |    1 | t1   ||  6 |    5 | t7   |+----+------+------+2 rows in set (0.00 sec)

结论：解析观察binlog，执行语句为先delete再insert

#210626 16:52:02 server id 494433  end_log_pos 86381 CRC32 0x4d74ee34   Rows_query# replace into test (id,age,name) value (6,5,'t7')# at 86381#210626 16:52:02 server id 494433  end_log_pos 86446 CRC32 0xc4f2281d   Table_map: `test20210626`.`test` mapped to number 161# at 86446#210626 16:52:02 server id 494433  end_log_pos 86494 CRC32 0xd587f095   Delete_rows: table id 161# at 86494#210626 16:52:02 server id 494433  end_log_pos 86542 CRC32 0x27c743b9   Write_rows: table id 161 flags: STMT_END_F### DELETE FROM `test20210626`.`test`### WHERE###   @1=6 /* INT meta=0 nullable=0 is_null=0 */###   @2=5 /* INT meta=0 nullable=1 is_null=0 */###   @3='t6' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */### INSERT INTO `test20210626`.`test`### SET###   @1=6 /* INT meta=0 nullable=0 is_null=0 */###   @2=5 /* INT meta=0 nullable=1 is_null=0 */###   @3='t7' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */# at 86542#210626 16:52:02 server id 494433  end_log_pos 86573 CRC32 0x77a5a94b   Xid = 6397866COMMIT/*!*/;

4、同时存在主键和唯一索引冲突，不同一行数据

CREATE TABLE `test` (  `id` int NOT NULL,  `age` int DEFAULT NULL,  `name` varchar(100) NOT NULL DEFAULT '',  PRIMARY KEY (`id`),  UNIQUE KEY `uni_age` (`age`)) ENGINE=InnoDB DEFAULT CHARSET=utf8
mysql> select * from test;+----+------+------+| id | age  | name |+----+------+------+|  1 |    1 | t1   ||  6 |    5 | t7   |+----+------+------+2 rows in set (0.00 sec)
mysql> replace into test (id,age,name) value (1,5,'t8');Query OK, 3 rows affected (0.04 sec)
mysql> select * from test;+----+------+------+| id | age  | name |+----+------+------+|  1 |    5 | t8   |+----+------+------+1 row in set (0.00 sec)

结论：解析观察binlog，执行语句为先delete再update

#210626 16:54:46 server id 494433  end_log_pos 107003 CRC32 0x80f5afb8  Rows_query# replace into test (id,age,name) value (1,5,'t8')# at 107003#210626 16:54:46 server id 494433  end_log_pos 107068 CRC32 0xfb8070b7  Table_map: `test20210626`.`test` mapped to number 161# at 107068#210626 16:54:46 server id 494433  end_log_pos 107116 CRC32 0x43998776  Delete_rows: table id 161# at 107116#210626 16:54:46 server id 494433  end_log_pos 107178 CRC32 0x27a9b8c1  Update_rows: table id 161 flags: STMT_END_F### DELETE FROM `test20210626`.`test`### WHERE###   @1=1 /* INT meta=0 nullable=0 is_null=0 */###   @2=1 /* INT meta=0 nullable=1 is_null=0 */###   @3='t1' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */### UPDATE `test20210626`.`test`### WHERE###   @1=6 /* INT meta=0 nullable=0 is_null=0 */###   @2=5 /* INT meta=0 nullable=1 is_null=0 */###   @3='t7' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */### SET###   @1=1 /* INT meta=0 nullable=0 is_null=0 */###   @2=5 /* INT meta=0 nullable=1 is_null=0 */###   @3='t8' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */# at 107178#210626 16:54:46 server id 494433  end_log_pos 107209 CRC32 0x2d0557fe  Xid = 6399202COMMIT/*!*/;