一 介绍
在笔者支持业务过程中，经常遇到开发咨询replace into 的使用场景以及注意事项，这里做个总结。从功能原理，性能和注意事项上做个说明。
二 原理
2.1 当表中存在主键但是不存在唯一建的时候。
表结构
代码如下:
CREATE TABLE `yy` (
`id` bigint(20) NOT NULL,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
root@test 02:43:58>insert into yy values(1,'abc');
Query OK, 1 row affected (0.00 sec)
root@test 02:44:25>replace into yy values(2,'bbb');
Query OK, 1 row affected (0.00 sec)
root@test 02:55:42>select * from yy;
+----+------+
| id | name |
+----+------+
| 1 | abc |
| 2 | bbb |
+----+------+
2 rows in set (0.00 sec)
root@test 02:55:56>replace into yy values(1,'ccc');
Query OK, 2 rows affected (0.00 sec)
如果本来已经存在的主键值，那么MySQL做update操作。
代码如下:
### UPDATE test.yy
### WHERE
### @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2='abc' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### SET
### @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2='ccc' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
如果本来相应的主键值没有，那么做insert 操作 replace into yy values(2,'bbb');
代码如下:
### INSERT INTO test.yy
### SET
### @1=2 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2='bbb' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
# at 623
#140314 2:55:42 server id 136403306 end_log_pos 650 Xid = 6090885569
2.2 当表中主键和唯一键同时存在时
代码如下:
CREATE TABLE `yy` (
`id` int(11) NOT NULL DEFAULT \'0\',
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL
PRIMARY KEY (`a`),
UNIQUE KEY `uk_bc` (`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
情形1 主键冲突
代码如下:
root@test 04:37:18>replace into yy values(1,2,3);
Query OK, 1 row affected (0.00 sec)
root@test 04:37:37>replace into yy values(2,2,4);
Query OK, 1 row affected (0.00 sec)
root@test 04:38:05>select * from yy;
+----+------+------+
| id | b | c |
+----+------+------+
| 1 | 2 | 3 |
| 2 | 2 | 4 |
+----+------+------+
2 rows in set (0.00 sec)
root@test 04:38:50>replace into yy values(1,2,5);
Query OK, 2 rows affected (0.00 sec)
root@test 04:38:58>select * from yy;
+----+------+------+
| id | b | c |
+----+------+------+
| 2 | 2 | 4 |
| 1 | 2 | 5 |
+----+------+------+
2 rows in set (0.00 sec)
主键冲突时，数据库对表做先删除然后插入的操作，也即先删除id=1的记录，然后插入新的id=1 的记录(1,2,5).
代码如下:
BINLOG '
Io5hVROWYHC+KwAAAEICAAAAAMoMAAAAAAEABHRlc3QAAnl5AAMDAwMABg==
Io5hVRmWYHC+KgAAAGwCAAAAAMoMAAAAAAAAA//4AQAAAAIAAAADAAAA
### DELETE FROM test.yy
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2=2 /* INT meta=0 nullable=1 is_null=0 */
### @3=3 /* INT meta=0 nullable=1 is_null=0 */
Io5hVReWYHC+KgAAAJYCAAAAAMoMAAAAAAEAA//4AQAAAAIAAAAFAAAA
'/*!*/;
### INSERT INTO test.yy
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2=2 /* INT meta=0 nullable=1 is_null=0 */
### @3=5 /* INT meta=0 nullable=1 is_null=0 */
# at 662
#150524 16:38:58 server id 3195035798 end_log_pos 689 Xid = 22962508
COMMIT/*!*/
情形2 唯一建冲突
代码如下:
root@test 04:48:30>select * from yy;
+----+------+------+
| id | b | c |
+----+------+------+
| 1 | 2 | 4 |
| 2 | 2 | 5 |
| 3 | 3 | 5 |
| 4 | 3 | 6 |
+----+------+------+
4 rows in set (0.00 sec)
root@test 04:53:21>replace into yy values(5,3,6);
Query OK, 2 rows affected (0.00 sec)
root@test 04:53:40>select * from yy;
+----+------+------+
| id | b | c |
+----+------+------+
| 1 | 2 | 4 |
| 2 | 2 | 5 |
| 3 | 3 | 5 |
| 5 | 3 | 6 |
+----+------+------+
4 rows in set (0.00 sec)
主键不冲突，唯一键冲突时，数据库对表 唯一键为(3，6)的行做update操作，将主键修改为要插入的值，id=4 改为id=5。
代码如下:
BINLOG \'
lJFhVROWYHC+KwAAANoAAAAAAMoMAAAAAAEABHRlc3QAAnl5AAMDAwMABg==
lJFhVRiWYHC+OAAAABIBAAAAAMoMAAAAAAEAA///+AQAAAADAAAABgAAAPgFAAAAAwAAAAYAAAA=
\'/*!*/;
### UPDATE test.yy
### WHERE
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2=3 /* INT meta=0 nullable=1 is_null=0 */
### @3=6 /* INT meta=0 nullable=1 is_null=0 */
### SET
### @1=5 /* INT meta=0 nullable=0 is_null=0 */
### @2=3 /* INT meta=0 nullable=1 is_null=0 */
### @3=6 /* INT meta=0 nullable=1 is_null=0 */
# at 274
#150524 16:53:40 server id 3195035798 end_log_pos 301 Xid = 22962872
COMMIT/*!*/
情形3 主键和唯一键同时冲突,如果需要插入的值的主键 和唯一和表中已经存在的存在冲突。
代码如下:
root@test 04:53:52>replace into yy values(1,3,6);
Query OK, 3 rows affected (0.00 sec) ---注意此处影响的行数是3
root@test 04:55:35>select * from yy;
+----+------+------+
| id | b | c |
+----+------+------+
| 2 | 2 | 5 |
| 3 | 3 | 5 |
| 1 | 3 | 6 |
+----+------+------+
3 rows in set (0.00 sec)
要插入的值(1,3,6) 主键于 表里面的id=1的值冲突，唯一键(3,6)和表中id=5的记录冲突，MySQL 处理的时候 ，先删除id=1的行，然后更新了id=5的行。
代码如下:
BINLOG \'
B5JhVROWYHC+KwAAAJwBAAAAAMoMAAAAAAEABHRlc3QAAnl5AAMDAwMABg==
B5JhVRmWYHC+KgAAAMYBAAAAAMoMAAAAAAAAA//4AQAAAAIAAAAEAAAA
### DELETE FROM test.yy
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2=2 /* INT meta=0 nullable=1 is_null=0 */
### @3=4 /* INT meta=0 nullable=1 is_null=0 */
B5JhVRiWYHC+OAAAAP4BAAAAAMoMAAAAAAEAA///+AUAAAADAAAABgAAAPgBAAAAAwAAAAYAAAA=
\'/*!*/;
### UPDATE test.yy
### WHERE
### @1=5 /* INT meta=0 nullable=0 is_null=0 */
### @2=3 /* INT meta=0 nullable=1 is_null=0 */
### @3=6 /* INT meta=0 nullable=1 is_null=0 */
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2=3 /* INT meta=0 nullable=1 is_null=0 */
### @3=6 /* INT meta=0 nullable=1 is_null=0 */
# at 510
#150524 16:55:35 server id 3195035798 end_log_pos 537 Xid = 22962904
COMMIT/*!*/
三 结论
对表进行replace into操作的时候，
当不存在冲突时,replace into 相当于insert操作。
当存在pk冲突的时候是先delete再insert,如果主键是自增的，则自增主键会做 +1 操作。【5.5,5.6版本均做过测试】
当存在uk冲突的时候是直接update。,如果主键是自增的，则自增主键会做 +1 操作。 【5.5,5.6版本均做过测试】
了解上述原理和结论之后，以后再遇到replace into 的时候，相信各位读者可以知道如何选择，由于篇幅限制，后续文章会基于replace into原理，讲述生产过程中的注意事项。
您什么时候应该使用复合索引与多个单列索引？Apr 11, 2025 am 12:06 AM在数据库优化中，应根据查询需求选择索引策略：1.当查询涉及多个列且条件顺序固定时，使用复合索引；2.当查询涉及多个列但条件顺序不固定时，使用多个单列索引。复合索引适用于优化多列查询，单列索引则适合单列查询。
如何识别和优化MySQL中的慢速查询？ （慢查询日志，performance_schema）Apr 10, 2025 am 09:36 AM要优化MySQL慢查询，需使用slowquerylog和performance_schema：1.启用slowquerylog并设置阈值，记录慢查询；2.利用performance_schema分析查询执行细节，找出性能瓶颈并优化。
MySQL和SQL：开发人员的基本技能Apr 10, 2025 am 09:30 AMMySQL和SQL是开发者必备技能。1.MySQL是开源的关系型数据库管理系统，SQL是用于管理和操作数据库的标准语言。2.MySQL通过高效的数据存储和检索功能支持多种存储引擎，SQL通过简单语句完成复杂数据操作。3.使用示例包括基本查询和高级查询，如按条件过滤和排序。4.常见错误包括语法错误和性能问题，可通过检查SQL语句和使用EXPLAIN命令优化。5.性能优化技巧包括使用索引、避免全表扫描、优化JOIN操作和提升代码可读性。
描述MySQL异步主奴隶复制过程。Apr 10, 2025 am 09:30 AMMySQL异步主从复制通过binlog实现数据同步，提升读性能和高可用性。1)主服务器记录变更到binlog；2)从服务器通过I/O线程读取binlog；3)从服务器的SQL线程应用binlog同步数据。
mysql：简单的概念，用于轻松学习Apr 10, 2025 am 09:29 AMMySQL是一个开源的关系型数据库管理系统。1）创建数据库和表：使用CREATEDATABASE和CREATETABLE命令。2）基本操作：INSERT、UPDATE、DELETE和SELECT。3）高级操作：JOIN、子查询和事务处理。4）调试技巧：检查语法、数据类型和权限。5）优化建议：使用索引、避免SELECT*和使用事务。
MySQL：数据库的用户友好介绍Apr 10, 2025 am 09:27 AMMySQL的安装和基本操作包括：1.下载并安装MySQL，设置根用户密码；2.使用SQL命令创建数据库和表，如CREATEDATABASE和CREATETABLE；3.执行CRUD操作，使用INSERT,SELECT,UPDATE,DELETE命令；4.创建索引和存储过程以优化性能和实现复杂逻辑。通过这些步骤，你可以从零开始构建和管理MySQL数据库。
InnoDB缓冲池如何工作，为什么对性能至关重要？Apr 09, 2025 am 12:12 AMInnoDBBufferPool通过将数据和索引页加载到内存中来提升MySQL数据库的性能。1)数据页加载到BufferPool中，减少磁盘I/O。2)脏页被标记并定期刷新到磁盘。3)LRU算法管理数据页淘汰。4)预读机制提前加载可能需要的数据页。
MySQL：初学者的数据管理易用性Apr 09, 2025 am 12:07 AMMySQL适合初学者使用，因为它安装简单、功能强大且易于管理数据。1.安装和配置简单，适用于多种操作系统。2.支持基本操作如创建数据库和表、插入、查询、更新和删除数据。3.提供高级功能如JOIN操作和子查询。4.可以通过索引、查询优化和分表分区来提升性能。5.支持备份、恢复和安全措施，确保数据的安全和一致性。
热AI工具
Undresser.AI Undress
人工智能驱动的应用程序，用于创建逼真的裸体照片
AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。
Undress AI Tool
免费脱衣服图片
Clothoff.io
AI脱衣机
AI Hentai Generator
免费生成ai无尽的。
热门文章
热工具
安全考试浏览器
Safe Exam Browser是一个安全的浏览器环境，用于安全地进行在线考试。该软件将任何计算机变成一个安全的工作站。它控制对任何实用工具的访问，并防止学生使用未经授权的资源。
记事本++7.3.1
好用且免费的代码编辑器
Dreamweaver CS6
视觉化网页开发工具
MinGW - 适用于 Windows 的极简 GNU
这个项目正在迁移到osdn.net/projects/mingw的过程中，你可以继续在那里关注我们。MinGW：GNU编译器集合（GCC）的本地Windows移植版本，可自由分发的导入库和用于构建本地Windows应用程序的头文件；包括对MSVC运行时的扩展，以支持C99功能。MinGW的所有软件都可以在64位Windows平台上运行。
PhpStorm Mac 版本
最新（2018.2.1 ）专业的PHP集成开发工具
