MySQL replace into行为解析

小灯数据 2021-07-01

339

我们知道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)


#执行replace
mysql> 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 = 6382446
COMMIT/*!*/;

二、只存在主键冲突

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 = 6387636
COMMIT/*!*/;

注意：就算主键冲突，如果数据一致，是不会产生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 = 6392477
COMMIT/*!*/;

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

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再insert

#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 = 6394330
COMMIT/*!*/;

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 = 6396411
COMMIT/*!*/;

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 = 6397866
COMMIT/*!*/;

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 = 6399202
COMMIT/*!*/;

End !

mysql

文章转载自小灯数据，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL replace into行为解析

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

总结：

一、不存在冲突

二、只存在主键冲突

一、不存在冲突

二、只存在主键冲突

评论