sql技巧

Mysql语句

很多项目基于需求都需要“若记录存在则更新数据，不存在则插入数据”的操作。有几种方法：

方法①：

​ 先update 表 set 字段1=值1，字段2=值2 where key=‘1’

​ 如果update失败则insert into 表（key，字段1） values （key1，值1）

​ 缺点是需要分成两条语句

方法②：

​ replace into 表（字段1，字段2）values（值1，if（1000>值2,1000，值2））

​ 也可以先设置好唯一约束、联合唯一约束、主键、联合主键，此时replace into 表（字段）values（值）时，出现了冲突，可以是主键冲突、唯一约束冲突等等，就会先删除原有记录，插入新记录以达到更新的目的。若无冲突则直接insert。

​ 添加联合唯一约束语句：alter table 表名 add unique 约束名（字段1，字段2）

​ 缺点是：由于先删再插，对于自增型主键，其原id会变（加1）。而innodb引擎备份表的自增主键id不会跟着变。有主备交换需要的业务会有危险。同时，replace若太频繁，可能导致id数值自增爆掉越界，使表无法再插入新记录。

方法③：

​ insert into 表（字段1，字段2）values（值1，值2）****on duplicate key**** update（字段1=new_value，字段2=new_value）

​ 当遇到了键重复时，更新值，否则插入。

​ 缺点：insert … on duplicate key在执行时innodb引擎会先判断插入的行是否产生重复key错误，如果存在，在对改现有的行加上S（共享锁）锁。返回该行数据给mysql，并且mysql执行完duplicate后设定的update操作后，对该记录加上X排他锁，最后进行update操作。

所以在特定版本mysql中，该语句可能造成死锁，尽量不对存在多个唯一键的table使用该语句；在有可能有并发事务执行的insert的内容一样的情况下不使用该语句。

方法④：

​ if not exists（select * from 表 where 条件）insert into … else update 表 set …

​ 由于此方法实验下来未成功，所以不做细讲。

参考文章：
https://www.cnblogs.com/yjmyzz/p/10053127.html
https://www.cnblogs.com/chenmz1995/p/11053865.html