mysql中的insert ON DUPLICATE KEY UPDATE，replace语句

最新推荐文章于 2023-11-08 15:07:56 发布

aoerqileng

最新推荐文章于 2023-11-08 15:07:56 发布

阅读量479

点赞数

分类专栏： MYSQL

本文链接：https://blog.csdn.net/aoerqileng/article/details/89450871

版权

本文探讨了MySQL中`INSERT ON DUPLICATE KEY UPDATE`语句的执行细节，指出在主键冲突时执行的是UPDATE而非DELETE后再INSERT，并且自增主键值会增加。同样，`REPLACE`语句在冲突时也采用UPDATE操作。虽然在某些情况下可能导致ID间隙问题，但在特定版本中已得到修复。此外，文章提到了主从同步时可能出现的主键冲突问题及其解决方案。

摘要由CSDN通过智能技术生成

之前没有关注过的一个点，在insert ON DUPLICATE KEY UPDATE执行后，表上的自增主键值维护是加了1的，主键一样，执行的就是update，不是删除在插入,如果id相同进行的是update操作，下面提示影响的行数是2

mysql> select * from bai;
+------+------+------+----+
| a    | b    | c    | id |
+------+------+------+----+
|    1 |    2 |    4 |  1 |
+------+------+------+----+
1 row in set (0.00 sec)

mysql> insert into bai (a,b,c,id) VALUES (1,2,3,1) ON DUPLICATE KEY UPDATE c=c+1;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from bai;
+------+------+------+----+
| a    | b    | c    | id |
+------+------+------+----+
|    1 |    2 |    5 |  1 |
+------+------+------+----+
1 row in set (0.00 sec)

mysql> insert into bai (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;     
Query OK, 1 row affected (0.00 sec)

mysql> select * from bai;
+------+------+------+----+
| a    | b    | c    | id |
+------+------+------+----+
|    1 |    2 |    5 |  1 |
|    1 |    2 |    3 |  5 |
+------+------+------+----+
2 rows in set (0.00 sec)

至于在update的情况下产生的id gap，我在5.7版本上没有测试出来，在udpate 一条记录后，再次的insert,id也是连续的。https://www.jianshu.com/p/1df268630038

replace into的语句，在id冲突后，也是用的update，不是delete 在insert

mysql> replace into bai (a,b,c) values(1,2,3);
Query OK, 1 row affected (0.00 sec)

mysql> select * from bai;
+------+------+------+----+
| a    | b    | c    | id |
+------+------+------+----+
|    1 |    2 |    5 |  1 |
|    1 |    2 |    3 |  5 |
|    1 |    2 |    3 |  6 |
+------+------+------+----+
3 rows in set (0.00 sec)

mysql> replace into bai (a,b,c,id) values(1,2,3,1);
Query OK, 2 rows affected (0.00 sec)

在这里插入图片描述

replace在执行的过程中，如果没有指定列的信息，那么该列会被默认的值覆盖，这个坑要注意避免

mysql> select * from bai;
+------+------+------+----+
| a    | b    | c    | id |
+------+------+------+----+
|    1 |    2 |    3 |  1 |
|    1 |    2 |    3 |  5 |
|    1 |    2 |    3 |  6 |
|    2 |    2 |    2 |  7 |
+------+------+------+----+
4 rows in set (0.00 sec)

mysql> replace into bai(a,b,id) value