mysql 值到99999后不增值了_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)模式时,警告会基于行的格式被写进一个二进制日志文件。

如果表是联合主键,则更新时,这些主键都要相同才能认为是主键冲突,如果只有其中部分主键相同,则直接进行插入操作。

使用的算法是:尝试直接将数据插入;

如果因为主键冲突造成插入失败:删除表中含有该主键的记录;

插入新记录;

方案三

有上述两个方案,问题可以由代码逻辑实现,但是,以上操作是保证了数据库的原子操作,如果是自己实现,要保证这一条。

后记

产生这篇文章的起因是:有个新增用户的业务需求,我想将新增和更新封装为一个API,所以使用了ON DUPLICATE KEY UPDATE,开始是使用数据库ID作为主键,但是,后来还需要保证用户的手机号唯一,所以给手机字段添加了UNIQUE属性,在新增用户的时候,并不会触发DuplicateKeyException,所以开始找ON DUPLICATE KEY UPDATE有没有条件只需要确认primary key冲突即可,发现,只能换个思路实现:

1、查询手机号是否存在;

2、存在则返回提示,否则,进行插入;

3、根据是否传入数据库ID作为更新还是新增的条件,进行新增更新操作。

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值