Mysql 增加新数据，若存在则更新的问题

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_list

REPLACE [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 = value

assignment_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作为更新还是新增的条件，进行新增更新操作。