MySQL replace into行为解析

已于 2023-05-25 10:29:42 修改
阅读量1.5k 收藏 2
点赞数 1
分类专栏： Mysql 文章标签： mysql 数据库
于 2023-05-25 10:27:45 首次发布
原文链接：https://www.modb.pro/db/77899
版权

目录

一、不存在冲突

二、只存在主键冲突

三、只存在唯一索引冲突

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

1、只有主键冲突

2、只有唯一索引冲突

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

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

      我们知道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/*!*/;

MartinYangHJ
关注
  • 1
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值