MySQL replace into 语句浅析（一）

主键冲突时，数据库对表做先删除然后插入的操作，也即先删除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)