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