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 相当于insert操作。
   当存在pk冲突的时候是先delete再insert,如果主键是自增的，则自增主键会做 +1 操作。【5.5,5.6版本均做过测试】
   当存在uk冲突的时候是直接update。,如果主键是自增的，则自增主键会做 +1 操作。   【5.5,5.6版本均做过测试】

   了解上述原理和结论之后，以后再遇到replace into 的时候，相信各位读者可以知道如何选择，由于篇幅限制，后续文章会基于replace into原理，讲述生产过程中的注意事项。