P.S. 基于mysql 5.6,数据库引擎是 InnoDB
解决方案
1 、使用INSERT ... ON DUPLICATE KEY UPDATE Statement语法;
官网手册
2、 使用REPLACE statement
官网手册
3、逻辑层处理,先判断是否存在记录,有则修改数据然后提交(删除然后插入),否则直接插入
方案一详解
1、语法
INSERT ... ON DUPLICATE KEY UPDATE ...
2、例子
INSERT INTO t1 (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;
3、说明
- 如果数据库表t1的字段a 为主键(Primary key)或者唯一索引(Unique Index),则上面的操作在遇到数据库已经存在a=1的情况下,则会进行c自增1的效果,否则就是插入一条记录。
- 如果是插入一条新记录,则影响的行记录数量是1;如果是更新,则返回的是2;没有变化则返回0。
- 如果a的属性是AUTO INCREMENT,则LAST_INSERT_ID()方法获取到的是自增值(AUTO_INCREMENT VALUE),而不是影响的行数。
- UPDATE后面可更新多个字段,他们用英文逗号分割。
- UPDATE后面的赋值表达式,可以使用values(col_name)函数获取到INSERT字段插入的值:
INSERT INTO t1 (a,b,c) VALUES (1,6,3) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
如果,数据库中已经存在a=1的记录,那么c=1+6,用values(col_name)获取col_name字段欲被分配的值,从而避免duplicate-key conflict。
- DELAYED选项在这个语句中无效。
方案二详解
1、语法
REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [PARTITION (partition_name [, partition_name] ...)] [(col_name [, col_name] ...)] {VALUES | VALUE} (value_list) [, (value_list)] ...REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [PARTITION (partition_name [, partition_name] ...)] SET assignment_listREPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [PARTITION (partition_name [, partition_name] ...)] [(col_name [, col_name] ...)] SELECT ...value: {expr | DEFAULT}value_list: value [, value] ...assignment: col_name = valueassignment_list: assignment [, assignment] ...
2、例子
REPLACE INTO test VALUES (1, 'Old', '2014-08-20 18:47:00');
3、说明
- 在没有主键`Primary key`或者`Unique index`冲突时,和`INSERT`功能一样;冲突时,它先删除旧纪录,然后插入新记录来完成更新,这也意味着执行sql语句的用户有`插入`和`删除`的权限。
- `REPLACE`时没有指定的字段,其值会被设置为`Default value`,且没有办法获取到旧纪录该字段的值然后把他应用到新记录中(即没办法像方案一中使用`VALUES()`函数)。
- 返回影响的行数值是删除和增加的总和,如果返回1,说明只进行了增加。
- 由于`REPLACE INTO`的结果依赖`SELECT`的结果集记录顺序,而且这个顺序无法保证,这有可能造成日志记录数据不一致。因此,这个操作被标记为在声明式备份时"不安全",且在声明模式下,会在错误日志文件中记录一个警告,然而在使用混合(MIXED)模式时,警告会基于行的格式被写进一个二进制日志文件。
- 如果表是联合主键,则更新时,这些主键都要相同才能认为是主键冲突,如果只有其中部分主键相同,则直接进行插入操作。
- 使用的算法是:
1. 尝试直接将数据插入;2. 如果因为主键冲突造成插入失败: 1. 删除表中含有该主键的记录; 2. 插入新记录;
方案三
有上述两个方案,问题可以由代码逻辑实现,但是,以上操作是保证了数据库的`原子操作`,如果是自己实现,要保证这一条。
后记
产生这篇文章的起因是:有个新增用户的业务需求,我想将新增和更新封装为一个API,所以使用了`ON DUPLICATE KEY UPDATE`,开始是使用数据库ID作为主键,但是,后来还需要保证用户的手机号唯一,所以给手机字段添加了`UNIQUE`属性,在新增用户的时候,并不会触发`DuplicateKeyException`,所以开始找`ON DUPLICATE KEY UPDATE`有没有条件只需要确认`primary key`冲突即可,发现,只能换个思路实现:
1、查询手机号是否存在;
2、存在则返回提示,否则,进行插入;
3、根据是否传入数据库ID作为更新还是新增的条件,进行新增更新操作。