数据插入失败引发的主键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 条评论
登录 后参与评论

相关文章

来自专栏Netkiller

CentOS7 下 MySQL 5.7 重置root密码

本文节选自《Netkiller MySQL 手札》 CentOS 7.x 添加 skip-grant-tables=1 选项,然后重启mysql # cat /...

3374
来自专栏数据之美

详解 MySQL 5.7 新的权限与安全问题

1、新版 MySQL 权限问题:  问题:SQL Error (1130): Host '192.168.1.100' is not allowed to co...

92710
来自专栏c#开发者

Oracle常用数据字典表

Oracle常用数据字典表      查看当前用户的缺省表空间   SQL>select username,default_tablespace ...

2716
来自专栏听雨堂

Mysql高效插入/更新数据

从tushare抓取到的财务数据,最开始只是想存下来,用的办法想简单点,是:插入--报错—update 但发现这个方法太蠢,异常会导致大量无效连接,改为: ...

1807
来自专栏应用案例

索引,视图,存储过程和触发器文档

实验案例一:验证索引的作用 1、首先创建一个数据量大的表,名称为“学生表”,分别有三列,学号,姓名和班级,如下图所示,学号为自动编号,班级为默认值“一班”。 ?...

1958
来自专栏用户2442861的专栏

【Linux】Ubuntu下C语言访问MySQL数据库入门

首先以用户rick登录MySQL数据库(用户rick已经被root权限用户赋予了创建数据库等等的权限):

683
来自专栏PHP技术

MYSQL数据库设计的一些小技巧[转载]

常用数据操作语言DML笔记(select insert update delete) select 语句 高级的查询功能,见下面的详细内容 s...

3835
来自专栏Aloys的开发之路

Oracle系统表整理+常用SQL语句收集

-- DBA/ALL/USER/V_$/GV_$/SESSION/INDEX开头的绝大部分都是视图 -- DBA_TABLES意为DBA拥有的或可以访问的所有...

18710
来自专栏安恒信息

请尽快升级!OpenSSH远程代码执行漏洞(CVE-2016-10009)

12月19日,国外漏洞平台 SecurityFocus 上发布了最新的 OpenSSH(CVE-2016-10009) 远程代码执行漏洞。 漏洞描述 OpenS...

2694
来自专栏Jed的技术阶梯

HBase Shell常用Shell命令

scan的用法很多,参数,过滤条件可以很多,各种组合, 在此不列举过多的例子,参考 help 'scan'

1793

扫码关注云+社区