开发者社区> yangyi_dba> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

【MySQL】replace into 浅析之一

简介: 一 介绍   在笔者支持业务过程中,经常遇到开发咨询replace into 的使用场景以及注意事项,这里做个总结。从功能原理,性能和注意事项上做个说明。 二 原理 2.1 当表中存在主键但是不存在唯一建的时候。
+关注继续查看
一 介绍
  在笔者支持业务过程中,经常遇到开发咨询replace into 的使用场景以及注意事项,这里做个总结。从功能原理,性能和注意事项上做个说明。
二 原理
2.1 当表中存在主键但是不存在唯一建的时候。

表结构
  1. CREATE TABLE `yy` (
  2.   `id` bigint(20) NOT NULL,
  3.   `name` varchar(20) DEFAULT NULL,
  4.   PRIMARY KEY (`id`)
  5. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1. root@test 02:43:58>insert into yy values(1,'abc');
  2. Query OK, 1 row affected (0.00 sec)
  3. root@test 02:44:25>replace into yy values(2,'bbb');
  4. Query OK, 1 row affected (0.00 sec)
  5. root@test 02:55:42>select * from yy;
  6. +----+------+
  7. | id | name |
  8. +----+------+
  9. | 1 | abc |
  10. | 2 | bbb |
  11. +----+------+
  12. 2 rows in set (0.00 sec)
  13. root@test 02:55:56>replace into yy values(1,'ccc');
  14. Query OK, 2 rows affected (0.00 sec)
如果本来已经存在的主键值,那么MySQL做update操作。
  1. ### UPDATE test.yy
  2. ### WHERE
  3. ### @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
  4. ### @2='abc' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
  5. ### SET
  6. ### @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
  7. ### @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 当表中主键和唯一键同时存在时
  1. CREATE TABLE `yy` (
  2.   `id` int(11) NOT NULL DEFAULT \'0\',
  3.   `b` int(11) DEFAULT NULL,
  4.   `c` int(11) DEFAULT NULL
  5.   PRIMARY KEY (`a`),
  6.   UNIQUE KEY `uk_bc` (`b`,`c`)
  7. ) ENGINE=InnoDB DEFAULT CHARSET=utf8
情形1 主键冲突
  1. root@test 04:37:18>replace into yy values(1,2,3);
  2. Query OK, 1 row affected (0.00 sec)
  3. root@test 04:37:37>replace into yy values(2,2,4);
  4. Query OK, 1 row affected (0.00 sec)
  5. root@test 04:38:05>select * from yy;
  6. +----+------+------+
  7. | id | b | c |
  8. +----+------+------+
  9. | 1 | 2 | 3 |
  10. | 2 | 2 | 4 |
  11. +----+------+------+
  12. 2 rows in set (0.00 sec)
  13. root@test 04:38:50>replace into yy values(1,2,5);
  14. Query OK, 2 rows affected (0.00 sec)
  15. root@test 04:38:58>select * from yy;
  16. +----+------+------+
  17. | id | b | c |
  18. +----+------+------+
  19. | 2 | 2 | 4 |
  20. | 1 | 2 | 5 |
  21. +----+------+------+
  22. 2 rows in set (0.00 sec)
主键冲突时,数据库对表做先删除然后插入的操作,也即先删除id=1的记录,然后插入新的id=1 的记录(1,2,5).
  1. BINLOG '
  2. Io5hVROWYHC+KwAAAEICAAAAAMoMAAAAAAEABHRlc3QAAnl5AAMDAwMABg==
  3. Io5hVRmWYHC+KgAAAGwCAAAAAMoMAAAAAAAAA//4AQAAAAIAAAADAAAA
  4. ### DELETE FROM test.yy
  5. ### WHERE
  6. ### @1=1 /* INT meta=0 nullable=0 is_null=0 */
  7. ### @2=2 /* INT meta=0 nullable=1 is_null=0 */
  8. ### @3=3 /* INT meta=0 nullable=1 is_null=0 */
  9. Io5hVReWYHC+KgAAAJYCAAAAAMoMAAAAAAEAA//4AQAAAAIAAAAFAAAA
  10. '/*!*/;
  11. ### INSERT INTO test.yy
  12. ### SET
  13. ### @1=1 /* INT meta=0 nullable=0 is_null=0 */
  14. ### @2=2 /* INT meta=0 nullable=1 is_null=0 */
  15. ### @3=5 /* INT meta=0 nullable=1 is_null=0 */
  16. # at 662
  17. #150524 16:38:58 server id 3195035798 end_log_pos 689 Xid = 22962508
  18. COMMIT/*!*/
情形2 唯一建冲突
  1. root@test 04:48:30>select * from yy;
  2. +----+------+------+
  3. | id | b | c |
  4. +----+------+------+
  5. | 1 | 2 | 4 |
  6. | 2 | 2 | 5 |
  7. | 3 | 3 | 5 |
  8. | 4 | 3 | 6 |
  9. +----+------+------+
  10. 4 rows in set (0.00 sec)
  11. root@test 04:53:21>replace into yy values(5,3,6);
  12. Query OK, 2 rows affected (0.00 sec)
  13. root@test 04:53:40>select * from yy;
  14. +----+------+------+
  15. | id | b | c |
  16. +----+------+------+
  17. | 1 | 2 | 4 |
  18. | 2 | 2 | 5 |
  19. | 3 | 3 | 5 |
  20. | 5 | 3 | 6 |
  21. +----+------+------+
  22. 4 rows in set (0.00 sec)
主键不冲突,唯一键冲突时,数据库对表 唯一键为(3,6)的行做update操作,将主键修改为要插入的值,id=4 改为id=5。
  1. BINLOG \'
  2. lJFhVROWYHC+KwAAANoAAAAAAMoMAAAAAAEABHRlc3QAAnl5AAMDAwMABg==
  3. lJFhVRiWYHC+OAAAABIBAAAAAMoMAAAAAAEAA///+AQAAAADAAAABgAAAPgFAAAAAwAAAAYAAAA=
  4. \'/*!*/;
  5. ### UPDATE test.yy
  6. ### WHERE
  7. ### @1=4 /* INT meta=0 nullable=0 is_null=0 */
  8. ### @2=3 /* INT meta=0 nullable=1 is_null=0 */
  9. ### @3=6 /* INT meta=0 nullable=1 is_null=0 */
  10. ### SET
  11. ### @1=5 /* INT meta=0 nullable=0 is_null=0 */
  12. ### @2=3 /* INT meta=0 nullable=1 is_null=0 */
  13. ### @3=6 /* INT meta=0 nullable=1 is_null=0 */
  14. # at 274
  15. #150524 16:53:40 server id 3195035798 end_log_pos 301 Xid = 22962872
  16. COMMIT/*!*/
情形3 主键和唯一键同时冲突,如果需要插入的值的主键 和唯一和表中已经存在的存在冲突。
  1. root@test 04:53:52>replace into yy values(1,3,6);
  2. Query OK, 3 rows affected (0.00 sec) ---注意此处影响的行数是3
  3. root@test 04:55:35>select * from yy;
  4. +----+------+------+
  5. | id | b | c |
  6. +----+------+------+
  7. | 2 | 2 | 5 |
  8. | 3 | 3 | 5 |
  9. | 1 | 3 | 6 |
  10. +----+------+------+
  11. 3 rows in set (0.00 sec)
 要插入的值(1,3,6) 主键于 表里面的id=1的值冲突,唯一键(3,6)和表中id=5的记录冲突,MySQL 处理的时候 ,先删除id=1的行,然后更新了id=5的行。
  1. BINLOG \'
  2. B5JhVROWYHC+KwAAAJwBAAAAAMoMAAAAAAEABHRlc3QAAnl5AAMDAwMABg==
  3. B5JhVRmWYHC+KgAAAMYBAAAAAMoMAAAAAAAAA//4AQAAAAIAAAAEAAAA
  4. ### DELETE FROM test.yy
  5. ### WHERE
  6. ### @1=1 /* INT meta=0 nullable=0 is_null=0 */
  7. ### @2=2 /* INT meta=0 nullable=1 is_null=0 */
  8. ### @3=4 /* INT meta=0 nullable=1 is_null=0 */
  9. B5JhVRiWYHC+OAAAAP4BAAAAAMoMAAAAAAEAA///+AUAAAADAAAABgAAAPgBAAAAAwAAAAYAAAA=
  10. \'/*!*/;
  11. ### UPDATE test.yy
  12. ### WHERE
  13. ### @1=5 /* INT meta=0 nullable=0 is_null=0 */
  14. ### @2=3 /* INT meta=0 nullable=1 is_null=0 */
  15. ### @3=6 /* INT meta=0 nullable=1 is_null=0 */
  16. ### SET
  17. ### @1=1 /* INT meta=0 nullable=0 is_null=0 */
  18. ### @2=3 /* INT meta=0 nullable=1 is_null=0 */
  19. ### @3=6 /* INT meta=0 nullable=1 is_null=0 */
  20. # at 510
  21. #150524 16:55:35 server id 3195035798 end_log_pos 537 Xid = 22962904
  22. 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原理,讲述生产过程中的注意事项。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
慎用mysql replace语句
结论 如果业务逻辑强依赖自增ID,绝对不要用replace,普通环境也不建议这样用,因为会导致主键的重新组织 当存在pk冲突的时候是先delete再insert 当存在uk冲突的时候是直接update,update操作不会涉及到auto_increment的修改 很大程度上会导致主备中断,存在容灾.
3312 0
SQL中的replace函数
REPLACE ( string_replace1 , string_replace2 , string_replace3 ) 参数解析: string_replace1 待搜索的字符串表达式。
1065 0
第179天:javascript中replace使用总结
ECMAScript提供了replace()方法。这个方法接收两个参数,第一个参数可以是一个RegExp对象或者一个字符串,第二个参数可以是一个字符串或者一个函数。现在我们来详细讲解可能出现的几种情况。
940 0
MySQL replace into (insert into 的增强版)
在使用SQL语句进行数据表插入insert操作时,如果表中定义了主键,插入具有相同主键的记录会报错:    Error Code: 1062. Duplicate entry 'XXXXX' for key 'PRIMARY'(主键冲突)   这样我们只好检查主键是不是存在,若存在则更新,若不存在则插入。
682 0
mysql replace into语句学习
实践过程:id 是主键测试方式一,插入索引值是一样的:REPLACE INTO  fanwe_order(id,sn)  VALUES('33','测试replace into 使用')  结果:受影响的行数:2SELECT * FROM fanwe_order WHERE sn='测试replace into 使用'  结果:查出 一行记录测试方式二,插入主键值是重复的:将插入id指定为34。
743 0
+关注
yangyi_dba
数据库相关技术专家
972
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载