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

SegmentFault博客
我还没有学会写个人说明!
上一篇

《纽约时报》在Instagram上推出AR填字游戏

下一篇

了解太空中目标天体的精确位置有多难?

你也可能喜欢

评论已经被关闭。

插入图片