MySQL replace into 语句浅析（一） |
|
一 介绍 在笔者支持业务过程中，经常遇到开发咨询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 的时候，相信各位读者可以知道如何选择，由于篇幅限制，后续文章会基于replace into原理，讲述生产过程中的注意事项 。 |
