insert into ... on duplite key update和replace into

港控/mmm° 2022-02-25 08:28 182阅读 0赞

本文先介绍insert into … on duplite key update和replace into各自的作用及用法。

1.insert into … on duplite key update …  
作用：插入一行数据，如果数据重复了(根据主键或者唯一键来判断)，就进行相应的更新。  
需要注意的是，数据是否重复，是根据表上的主键或者唯一键来判断的，如果表上没有主键或者唯一键，那么可以认为任何数据都可以插入成功，而不是根据整行数据来判断是否重复。

例子1，表上有主键：

mysql> desc test;
    +-------+---------+------+-----+---------+-------+
    | Field | Type    | Null | Key | Default | Extra |
    +-------+---------+------+-----+---------+-------+
    | id    | int(11) | NO   | PRI | NULL    |       |
    | col1  | int(11) | YES  |     | NULL    |       |
    +-------+---------+------+-----+---------+-------+
    2 rows in set (0.00 sec)
    
    mysql> select * from test;
    +----+------+
    | id | col1 |
    +----+------+
    |  2 |    3 |
    |  3 |    2 |
    |  4 |    4 |
    +----+------+
    3 rows in set (0.00 sec)
    
    mysql> insert into test values (3,2) on duplicate key update id=1;
    Query OK, 2 rows affected (0.00 sec)
    
    mysql> select * from test;
    +----+------+
    | id | col1 |
    +----+------+
    |  1 |    2 |
    |  2 |    3 |
    |  4 |    4 |
    +----+------+
    3 rows in set (0.00 sec)
    
    mysql> insert into test values (5,5) on duplicate key update col1=5;
    Query OK, 1 row affected (0.01 sec)
    
    mysql> select * from test test;
    +----+------+
    | id | col1 |
    +----+------+
    |  1 |    2 |
    |  2 |    3 |
    |  4 |    4 |
    |  5 |    5 |
    +----+------+
    4 rows in set (0.00 sec)

以上例子可以看到，当主键冲突时，会执行update部分；当主键不冲突时，将数据直接插入。其中注意

mysql> insert into test values (3,2) on duplicate key update id=1;
    Query OK, 2 rows affected (0.00 sec)

显示`2 rows affected`，影响两行，是因为在update时，实际上是将(3,2）标记为删除，然后再新数据(1,2）插入，即实际操作是delete+insert，所以影响的两行数据。而(3,2）这一行数据后台进程在某种机制下进行purge。

例子2，表上无主键：

mysql> desc test;
    +-------+---------+------+-----+---------+-------+
    | Field | Type    | Null | Key | Default | Extra |
    +-------+---------+------+-----+---------+-------+
    | id    | int(11) | NO   |     | NULL    |       |
    | col1  | int(11) | YES  |     | NULL    |       |
    +-------+---------+------+-----+---------+-------+
    2 rows in set (0.00 sec)
    
    mysql> select * from test;
    +----+------+
    | id | col1 |
    +----+------+
    |  1 |    2 |
    |  2 |    3 |
    |  4 |    4 |
    |  5 |    5 |
    +----+------+
    4 rows in set (0.00 sec)
    
    mysql> insert into test values (5,5) on duplicate key update id=6;
    Query OK, 1 row affected (0.01 sec)
    
    mysql> select * from test;
    +----+------+
    | id | col1 |
    +----+------+
    |  1 |    2 |
    |  2 |    3 |
    |  4 |    4 |
    |  5 |    5 |
    |  5 |    5 |
    +----+------+
    5 rows in set (0.00 sec)

以上例子可以看到，即使将要插入的数据与已有数据完全重复，也是可以插入成功。

2.replace into  
作用：插入一行数据，如果存在重复数据（根据表上主键或唯一键来判断），先将原有数据行删除，再插入新数据。  
需要注意的跟insert into … on duplicate key update …类似，判断数据重复的依据是主键或唯一键，如果没有主键和唯一键，并不会使用整行数据来做比较。

3.两者的实现  
两者功能相似，都是如果新数据不与旧数据重复，则插入数据；如果新数据与旧数据重复，则进行覆盖更新；  
但insert into … on dupliate key update …功能更强大，例如上述的例子1，如果主键冲突，update字句可以更新该行的任何列，包括冲突的主键；  
而replace into …冲突的主键或唯一键是不能变化的。

insert into … on duplicate key update，在发生数据冲突时，做的操作是select + update；  
replace into …，在发生数据冲突时，做的操作是delete + insert；

两者的性能，先说一下某个大神的理解：  
insert on duplicate的方式其实只涉及一条DML,而且从索引的维护角度来看，在基于主键的条件下，其实是不需要索引维护的，而replace操作在delete+insert本质是两条DML，从索引的角度维护索引的代价要高一些。

但是我的理解是，update其实是delete+insert，两者性能应该是一样的。但是从大神的测试结果来看，确实insert on duplicate比replace性能要好。那么也许是MySQL在update时，如果主键没有变化，就自动忽略了主键索引的维护？留作以后研究吧。