MySQL的replace使用分析

原创薛晓刚 4天前

这个我之前没有研究过，看名字就是替换。因为我实在想不出我现在业务场景中会用到它。

没想到还有开发人员居然去使用。
从名字解析可能类似Oracle的merge into update的做法

先实验看看实际效果

为了观察binlog，新刷新一个。

mysql>  truncate table t1;
Query OK, 0 rows affected (0.02 sec)

mysql>  truncate table t2;
Query OK, 0 rows affected (0.03 sec)

mysql>  flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values (1,1,1, CURRENT_TIMESTAMP);
Query OK, 1 row affected (0.01 sec)

mysql> replace into t2 select * from t1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0


mysqlbinlog binlog.000021  --base64-output=DECODE-ROWS -v   |grep '`z`.'
#260107 14:45:44 server id 1  end_log_pos 366 CRC32 0x716ebe5f  Table_map: `z`.`t1` mapped to number 594
### INSERT INTO `z`.`t1`
#260107 14:45:45 server id 1  end_log_pos 658 CRC32 0x74681c7f  Table_map: `z`.`t2` mapped to number 595
### INSERT INTO `z`.`t2`

从这里可以看出来，我现在t1表写了一条数据，然后执行replace into。效果是把t1表的数据复制过去了。
类似insert into select。
日志的end_log_pos 366和658记录了这两行。
接下来看一下不同的。在t1表继续写入一条数据。
再次执行replace into

mysql> insert into t1 values (2,2,2, CURRENT_TIMESTAMP);
Query OK, 1 row affected (0.01 sec)

mysql> replace into t2 select * from t1;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> 
mysql> select * from t1;
+----+------+------+---------------------+
| id | m    | n    | t                   |
+----+------+------+---------------------+
|  1 |    1 |    1 | 2026-01-07 14:45:44 |
|  2 |    2 |    2 | 2026-01-07 14:47:00 |
+----+------+------+---------------------+
2 rows in set (0.00 sec)

mysql> 
mysql> select * from t2;
+----+------+------+---------------------+
| id | m    | n    | time                |
+----+------+------+---------------------+
|  1 |    1 |    1 | 2026-01-07 14:45:44 |
|  2 |    2 |    2 | 2026-01-07 14:47:00 |
+----+------+------+---------------------+
2 rows in set (0.00 sec)

 mysqlbinlog binlog.000021  --base64-output=DECODE-ROWS -v   |grep '`z`.'
#260107 14:45:44 server id 1  end_log_pos 366 CRC32 0x716ebe5f  Table_map: `z`.`t1` mapped to number 594
### INSERT INTO `z`.`t1`
#260107 14:45:45 server id 1  end_log_pos 658 CRC32 0x74681c7f  Table_map: `z`.`t2` mapped to number 595
### INSERT INTO `z`.`t2`
#260107 14:47:00 server id 1  end_log_pos 953 CRC32 0x04894dd3  Table_map: `z`.`t1` mapped to number 594
### INSERT INTO `z`.`t1`
#260107 14:47:00 server id 1  end_log_pos 1245 CRC32 0x8d00490e         Table_map: `z`.`t2` mapped to number 595
### INSERT INTO `z`.`t2`

从效果看，在刚才的t1和t2的end_log_pos 366和658后面新多了2行953和1245的两个insert。
其中一个是insert t1的一个是insert t2的。

#260107 14:47:00 server id 1  end_log_pos 1245 CRC32 0x8d00490e         Table_map: `z`.`t2` mapped to number 595
# has_generated_invisible_primary_key=0
# at 1245
#260107 14:47:00 server id 1  end_log_pos 1301 CRC32 0x103947fd         Write_rows: table id 595 flags: STMT_END_F
### Extra row info for partitioning: partition: 1
### INSERT INTO `z`.`t2`
### SET
###   @1=2
###   @2=2
###   @3=2
###   @4='2026-01-07 14:47:00'
# at 1301
#260107 14:47:00 server id 1  end_log_pos 1332 CRC32 0xc183a77b         Xid = 649006
COMMIT/*!*/;

具体位置的信息是这样的，看上去通过对比把第二行写入了。第一行没动。
这里并没有删除，更加不是全部删除，全部新增的粗暴做法。
下面试试改一行以后，会如何?

mysql> update t1 set m=11 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t1;
+----+------+------+---------------------+
| id | m    | n    | t                   |
+----+------+------+---------------------+
|  1 |   11 |    1 | 2026-01-07 14:45:44 |
|  2 |    2 |    2 | 2026-01-07 14:47:00 |
+----+------+------+---------------------+
2 rows in set (0.00 sec)

mysql> 
mysql> replace into t2 select * from t1;
Query OK, 3 rows affected (0.00 sec)
Records: 2  Duplicates: 1  Warnings: 0

mysql> select * from t2;
+----+------+------+---------------------+
| id | m    | n    | time                |
+----+------+------+---------------------+
|  1 |   11 |    1 | 2026-01-07 14:45:44 |
|  2 |    2 |    2 | 2026-01-07 14:47:00 |
+----+------+------+---------------------+
2 rows in set (0.00 sec)

mysqlbinlog binlog.000021  --base64-output=DECODE-ROWS -v   |grep '`z`.'
#260107 14:45:44 server id 1  end_log_pos 366 CRC32 0x716ebe5f  Table_map: `z`.`t1` mapped to number 594
### INSERT INTO `z`.`t1`
#260107 14:45:45 server id 1  end_log_pos 658 CRC32 0x74681c7f  Table_map: `z`.`t2` mapped to number 595
### INSERT INTO `z`.`t2`
#260107 14:47:00 server id 1  end_log_pos 953 CRC32 0x04894dd3  Table_map: `z`.`t1` mapped to number 594
### INSERT INTO `z`.`t1`
#260107 14:47:00 server id 1  end_log_pos 1245 CRC32 0x8d00490e         Table_map: `z`.`t2` mapped to number 595
### INSERT INTO `z`.`t2`
#260107 14:48:11 server id 1  end_log_pos 1541 CRC32 0x5c8ff983         Table_map: `z`.`t1` mapped to number 594
### UPDATE `z`.`t1`
#260107 14:48:11 server id 1  end_log_pos 1852 CRC32 0xf82e7121         Table_map: `z`.`t2` mapped to number 595
### UPDATE `z`.`t2`

通过日志发现end_log_pos的1541和1852行各有一处更新。最终把更新的数据覆盖过来了。

接下来就是问题的点了。现实中t1和t2表，没有这么理想化。

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> create table d1 (id int primary key ,m int,n int ,t datetime);

Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> create table d2 (id int ,m int ,n int,time datetime default CURRENT_TIMESTAMP, PRIMARY KEY (id,time))
    -> PARTITION BY RANGE(TO_DAYS (time))
    -> (
    -> PARTITION p1 VALUES LESS THAN (TO_DAYS('2026-01-01')),
    -> PARTITION p2 VALUES LESS THAN (TO_DAYS('2026-02-01')),
    -> PARTITION p3 VALUES LESS THAN (TO_DAYS('2026-03-01')));

Query OK, 0 rows affected (0.03 sec)

看到这里想必都明白了吧。d2表的主键是(id,time) d1表的主键是id。这又是会怎么样的火花？
顺便说一下这就是一个非分区表改成分区表的一个迁移，只迁移最近的就行的这种。

mysql> insert into d1 (id,m,n) values (1,1,1);
Query OK, 1 row affected (0.01 sec)

mysql> replace into d2 (id ,m,n) select id ,m,n from d1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from d1;
+----+------+------+------+
| id | m    | n    | t    |
+----+------+------+------+
|  1 |    1 |    1 | NULL |
+----+------+------+------+
1 row in set (0.00 sec)

mysql> replace into d2 (id ,m,n) select id ,m,n from d1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from d2;
+----+------+------+---------------------+
| id | m    | n    | time                |
+----+------+------+---------------------+
|  1 |    1 |    1 | 2026-01-07 16:15:16 |
|  1 |    1 |    1 | 2026-01-07 16:15:38 |
+----+------+------+---------------------+
2 rows in set (0.00 sec)

然后再次执行replace into 发现逻辑和之前的不一样了。
这就是出问题的点。
这里由于d2表的time列是有默认值的，所以这样就会产生重复数据了。
其实在这种前提下，d2表是 id+time的联合主键，那么d1表时间只要改变，那么送过来的数据都会重复的落在d2表中。
具体看下面的实操。

mysql> select * from d1;
±—±-----±-----±-----+
| id | m | n | t |
±—±-----±-----±-----+
| 1 | 1 | 1 | NULL |
±—±-----±-----±-----+
1 row in set (0.00 sec)

mysql> update d1 set t=‘2026-01-07 00:00:00’ ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from d1;
±—±-----±-----±--------------------+
| id | m | n | t |
±—±-----±-----±--------------------+
| 1 | 1 | 1 | 2026-01-07 00:00:00 |
±—±-----±-----±--------------------+
1 row in set (0.00 sec)

mysql> replace into d2 (id ,m,n,time) select id ,m,n,t from d1;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from d2;
±—±-----±-----±--------------------+
| id | m | n | time |
±—±-----±-----±--------------------+
| 1 | 1 | 1 | 2026-01-07 00:00:00 |
| 1 | 1 | 1 | 2026-01-07 16:15:16 |
| 1 | 1 | 1 | 2026-01-07 16:15:38 |
| 1 | 1 | 1 | 2026-01-07 16:30:34 |
±—±-----±-----±--------------------+
4 rows in set (0.00 sec)

mysql> update d1 set t=‘2026-01-08 00:00:00’ ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from d1;
±—±-----±-----±--------------------+
| id | m | n | t |
±—±-----±-----±--------------------+
| 1 | 1 | 1 | 2026-01-08 00:00:00 |
±—±-----±-----±--------------------+
1 row in set (0.01 sec)

mysql> replace into d2 (id ,m,n,time) select id ,m,n,t from d1;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from d2;
±—±-----±-----±--------------------+
| id | m | n | time |
±—±-----±-----±--------------------+
| 1 | 1 | 1 | 2026-01-07 00:00:00 |
| 1 | 1 | 1 | 2026-01-07 16:15:16 |
| 1 | 1 | 1 | 2026-01-07 16:15:38 |
| 1 | 1 | 1 | 2026-01-07 16:30:34 |
| 1 | 1 | 1 | 2026-01-08 00:00:00 |
±—±-----±-----±--------------------+
5 rows in set (0.01 sec)


# 主要是实在没想过会有人去这样用replace into。

墨力计划 mysql

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

MySQL的replace使用分析

这个我之前没有研究过，看名字就是替换。 因为我实在想不出我现在业务场景中会用到它。

先实验看看实际效果

接下来就是问题的点了。现实中t1和t2表，没有这么理想化。

评论

这个我之前没有研究过，看名字就是替换。因为我实在想不出我现在业务场景中会用到它。