数据插入失败引发的主键auto_increment问题

昨天在调试一个业务代码中,无意间发现了一个问题。数据入库后的主键不是连续自增的,主键键值没过几秒就从两千多直接跳到了五千上下。这是为什么?瞬间引起我的注意。

先简单说明下环境。Mysql版本:5.6.23。为了防止某些数据重复,数据库中对某些字段设置了唯一索引,即unique key。经确认此表也只有一个业务程序在操作。那么,问题就定位到主键的auto_increment属性上了。

问题重现

下面来还原问题,以便能准确查找出原因。 表结构如下:

CREATE TABLE `test_innodb` (
   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
   `username` varchar(100) NOT NULL COMMENT '用户名',
   PRIMARY KEY (`id`),
   UNIQUE KEY `UNIQUE_USERNAME` (`username`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

username字段设置了唯一索引(unique key)。先插入一条数据:

INSERT INTO test_innodb (`username`) VALUES('admin');

执行成功,主键ID为“1”。再次执行此SQL,因username重复,数据入库失败,提示:

Duplicate entry 'admin' for key 'UNIQUE_USERNAME'

然后再成功插入一条username不重复的数据,可以看到主键ID为“3”,已经略过了“2”。问题重现。

问题原因

在mysql官网的文档中,对“AUTO_INCREMENT Handling in InnoDB”,有这样一段说明: InnoDB uses the in-memory auto-increment counter as long as the server runs. When the server is stopped and restarted, InnoDB reinitializes the counter for each table for the first INSERT to the table, as described earlier. A server restart also cancels the effect of the AUTO_INCREMENT = N table option in CREATE TABLE and ALTER TABLE statements, which you can use with InnoDB tables as of MySQL 5.0.3 to set the initial counter value or alter the current counter value. You may see gaps in the sequence of values assigned to the AUTO_INCREMENT column if you roll back transactions that have generated numbers using the counter.

大概意思是,Innodb存储引擎的auto_increment计数器是随着mysql-server启动分配,并永久缓存在内存中。当插入数据失败或者回滚事务时,内存中的auto_increment计算器的值却不会回滚。

举一反三

Innodb存储引擎会引起此问题,那MyISAM存储引擎呢?经过测试之后,以MyISAM作为存储引擎的数据表,不会出现上述的问题。

参考资料:http://dev.mysql.com/doc/refman/5.0/en/innodb-auto-increment-handling.html

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏吴伟祥

MySQL 存储引擎 原

使用MEMORY存储引擎的表,其数据存储在内存中,且行的长度固定,这两个特点使得MEMORY存储引擎非常快。

691
来自专栏码神联盟

碎片化 | 第四阶段-35-Struts2-Spring结合jdbc实现查询列表-视频

如清晰度低,可转PC网页观看高清版本: http://v.qq.com/x/page/q0566q6xkdu.html 结合jdbc实现查询功能 步骤: 1...

3469
来自专栏Laoqi's Linux运维专列

Mysql中MyISAM引擎和InnoDB引擎的比较

结论 如果不清楚自己应该用什么引擎,那么请选择InnoDB,Mysql5.5+的版本默认引擎都是InnoDB,早期的Mysql版本默认的引擎是MyISAM --...

3566
来自专栏PHP在线

MySQL 数据库锁定机制

1. MySQL 锁定机制简介 各存储引擎使用三种类型锁定机制 行级锁定(row-level) 表级锁定(table-level) 页级锁定(page...

40916
来自专栏java一日一条

理解MySQL——架构与概念

写在前面:最早接触的MySQL是在三年前,那时候MySQL还是4.x版本,很多功能都不支持,比如,存储过程,视图,触发器,更别说分布式事务等复杂特性了。但从5....

632
来自专栏散尽浮华

MySQL存储引擎之Myisam和Innodb总结性梳理

Mysql有两种存储引擎:InnoDB与Myisam,下表是两种引擎的简单对比 MyISAM InnoDB 构成上的区别: 每个MyISAM...

1955
来自专栏后端之路

坑爹的MySql update in subquery

背景 ? 最近收到邮件如上,在做allot时发生db超时【前一段时间由于系统负载过大忽略了相关慢sql】 收到如上邮件考虑可能是allot详情太多导致发生超时 ...

2429
来自专栏蓝天

MYSQL不能从远程连接的一个解决方法

ERROR 1130: Host '192.168.1.3' is not allowed to connect to this MySQL server

802
来自专栏数据和云

MySQL SQL优化之覆盖索引

内容概要 利用主索引提升SQL的查询效率是我们经常使用的一个技巧,但是有些时候MySQL给出的执行计划却完全出乎我们的意料,我们预想MySQL会通过索引扫描完成...

4066
来自专栏杨建荣的学习笔记

关于interval partitioning(r3笔记65天)

今天一个开发的同事问我一个问题,说在系统中目前需要一个临时的解决方案,定义了一张表,目前想设定为分区表,因为会和外围系统交互产生大量的数据,所以需要按照小时来做...

2808

扫码关注云+社区