on duplicate key update 的用法及优缺点

项目中数据的操作有时候会令人头大，遇到一个需求：

需要将数据从A数据库的a数据表同步到B数据库的b数据表中（ab表结构相同，但不是主从关系。。。just同步过去）

第一次同步过去，b表为空，同步很简单。

但是当a表中的某些数据更新且增加了新数据之后，再想让两个表同步就有些麻烦了。（如果把b表清空，重新同步，数据量过大的话耗费的时间太长，不是一个好办法）

想着能不能按照时间段来做更新，这段时间内有新数据了，就插入数据，有数据更新了就更新数据。先说下我的思路：

步骤：

1.首先我从a表取出某一时间段的数据（分段更新）

2.往b表内放数据，根据主键判断b表是否已经有此条记录，没有此数据则插入，有了记录则对比数据是否一样，一样则不做更改，不一样就做更新操作。

第一步很好做，第二步就有点繁琐了，就想着偷懒，网上查了下发现mysql有个语句能满足我的需求。

在MySQL数据库中，如果在insert语句后面带上ON DUPLICATE KEY UPDATE 子句，而要插入的行与表中现有记录的惟一索引或主键中产生重复值，那么就会发生旧行的更新；如果插入的行数据与现有表中记录的唯一索引或者主键不重复，则执行新纪录插入操作。

说通俗点就是数据库中存在某个记录时，执行这个语句会更新，而不存在这条记录时，就会插入。

用法 ： insert into tablename(field1,field2,field3,......) values(value1,value2,value3,....) on duplicate key update field1=value1,field2=value2,field3=value3,......;

注意点：

因为这是个插入语句，所以不能加where条件。

如果是插入操作，受到影响行的值为1；如果更新操作，受到影响行的值为2；如果更新的数据和已有的数据一样（就相当于没变，所有值保持不变），受到影响的行的值为0。

---

（2019.6.20更新）这段时间，同事写接口代码用到了这个，但是遇到了问题：并发量大的时候，接口请求速度大于2s的次数会经常出现（请求频率大概一天有上百万次，高的时候可到千万吧）找了很久，问了几个前辈都没找到问题。最后改为原子操作（SELECT+INSERT or UPDATE 的方案），出现慢请求的次数就少了。。。具体原因他也没找到。

先说下优缺点吧：

优点：

1. 开发简单，对已有表批量插入新数据时尤其方便（特别是做表数据同步脚本，真香！）
2. 可以减少网络连接开销（减少了数据查询、操作次数），在一定量的数据操作时，效率上也提高。

缺点：

1. MySQL私有语法，非SQL92标准语法，当迁移数据时会造成麻烦，需要改写代码。例如MySQL迁移PgSQL。
2. 当环境复杂时，数据量大的情况下，会出现意想不到的问题。（数据量大、产生并发，建议还是用原子操作）
3. 业务逻辑分散在应用逻辑层和数据层，会对项目维护留下隐患。

找了下原因，可能是下面的问题导致的（个人觉的应该是第二种情况），不过由于不是本人负责的，也就没有花太多时间去认真探究这个问题的根源。

1.数据库备份使用的xtrabackup，其备份之后会打成tar包，然后删除源文件；由于备份文件比较大，所以在删除的瞬间导致磁盘IO压力过大！因而导致此问题的产生。

参见：http://www.itpub.net/thread-1941532-1-1.html

2.insert ... on duplicate key 在执行时，innodb引擎会先判断插入的行是否产生重复key错误，如果存在，在对该现有的行加上S（共享锁）锁，如果返回该行数据给mysql,然后mysql执行完duplicate后的update操作，然后对该记录加上X（排他锁），最后进行update写入。如果有两个事务并发的执行同样的语句，那么就会产生死锁。

参见：https://blog.csdn.net/pml18710973036/article/details/78452688  或 https://www.colabug.com/3166910.html

建议：

1、不对存在多个唯一键的数据表使用此语句。

2、在有可能有并发事务执行的insert 语句情况下不使用该语句。

3、使用此语句要考虑以后是否会做数据迁移，数据量是否大，考虑后再使用！！！