关于MySQL自增ID的一些小问题总结

下面这几个小问题都是基于 InnoDB 存储引擎的。

1. ID最大的记录删除后，新插入的记录ID是什么

例如当前表中有ID为1,2,3三条记录，把3删除，新插入记录的ID从哪儿开始？

答案： 从4开始。

实验

创建表 tb0，ID自增：

create table tb0(id int unsigned auto_increment primary key);

插入3条记录：

insert into tb0 values(null);

删除ID为3的记录：

delete from tb0 where id=3

查看当前自增值：

show create table tb0;

# 结果
CREATE TABLE `tb0` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

自增ID为4，删除ID最大的记录并不影响自增ID的值。

2. MySQL 重启后自增ID从哪儿开始

例如当前表中有ID为1,2,3三条记录，把3删除，重启MySQL，新插入记录的ID从哪儿开始？

很多人会认为从4开始，实际是从3开始。

因为InnoDB的自增值是记录在内存的，不是记录在数据文件的。

重启后，会把 当前最大ID + 1 作为起始值。

实验

创建表 tb1，ID自增：

create table tb1(id int unsigned auto_increment primary key);

添加3条数据记录：

insert into tb1 values(null);

删除ID为3的记录：

delete from tb1 where id=3

通过上一个问题，我们知道，此时自增ID值为4。

重启MySQL。

查看当前的自增值：

show create table tb1;

# 结果
CREATE TABLE `tb1` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

3. 手动插入ID后，下次插入时自增值是多少

例如当前的自增ID为4，新插入记录时，手动指定ID为10，下次使用自增方式插入时，ID是 11。

ID自增值 = 当前最大ID + 1

在插入新记录时，就已经计算得出了新的ID值

实验

创建表 tb2，ID自增：

create table tb2(id int unsigned auto_increment primary key);

添加记录：

insert into tb2 values(null);

手动指定ID：

insert into tb2 values(4294967000);

查看当前的自增值:

show create table tb2;

# 结果
CREATE TABLE `tb2` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967001 DEFAULT CHARSET=latin1

可以看到自增值变为 4294967001。

4. 自增值用完后怎么办

无符号 int 的最大值为 4294967295，自增值达到此值后，就不变了，新插入记录时就会报错：

Duplicate entry '4294967295' for key 'PRIMARY'

如果表记录经常插入、删除，即使表内记录总量不是很大，ID也可能快速用完，这种情况可以需要使用 bigint。

int 取值范围：

int 是 4 byte，首位用来表示符号

有符号的范围：
从 -  至  - 1

（-2147483648 至 2147483647）

无符号的范围：
0 至  - 1

（0 至 4294967295）

bigint 取值范围：

int 是 8 byte

有符号的范围：
从 -  至  - 1

（-9223372036854775808 至 9223372036854775807）

无符号的范围：
0 至  - 1

（0 至 18446744073709551615）

小结

通过实验可以发现InnoDB中自增ID的一些特性：

插入新记录时，就会计算出新的自增值（最大ID+1），不管是使用自动ID，还是手动指定一个ID。

删除最大ID值对自增ID值没有影响，但MySQL重启之后有影响，不会使用之前的自增ID值，而是使用最大ID+1，因为自增ID值是存在内存中，重启后需

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对我们的支持。

要重新计算。
自增ID用完后就不变了。

赞 (0)

相关推荐

  • Java获取最后插入MySQL记录的自增ID值的3种方法

    方法一: 复制代码 代码如下: String sql = "INSERT INTO users (username,password,email) VALUES (?,?,?);";PreparedStatement pstmt = (PreparedStatement) conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);//传入参数:Statement.RETURN_GENERATED_KEYSpstmt.setSt

  • mysql自增id超大问题的排查与解决

    mysql自增id超大问题的排查与解决

    引言 小A正在balabala写代码呢,DBA小B突然发来了一条消息,"快看看你的用户特定信息表T,里面的主键,也就是自增id,都到16亿了,这才多久,在这样下去过不了多久主键就要超出范围了,插入就会失败,balabala......" 我记得没有这么多,最多1k多万,count了下,果然是1100万.原来运维是通过auto_increment那个值看的,就是说,表中有大量的删除插入操作,但是我大部分情况都是更新的,怎么会这样? 下面话不多说了,来一起看看详细的介绍吧 问题排查 这张表

  • MySQL自增ID耗尽实例讲解

    显示定义ID 表定义的自增值ID达到上限后,在申请下一个ID时,得到的值保持不变 -- (2^32-1) = 4,294,967,295 -- 建议使用 BIGINT UNSIGNED CREATE TABLE t (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY) AUTO_INCREMENT=4294967295; INSERT INTO t VALUES (null); -- AUTO_INCREMENT没有改变 mysql> SHOW CREATE

  • MySQL分表自增ID问题的解决方法

    当我们对MySQL进行分表操作后,将不能依赖MySQL的自动增量来产生唯一ID了,因为数据已经分散到多个表中.  应尽量避免使用自增IP来做为主键,为数据库分表操作带来极大的不便.  在postgreSQL.oracle.db2数据库中有一个特殊的特性---sequence. 任何时候数据库可以根据当前表中的记录数大小和步长来获取到该表下一条记录数.然而,MySQL是没有这种序列对象的.  可以通过下面的方法来实现sequence特性产生唯一ID:  1. 通过MySQL表生成ID  对于插入也

  • 关于Mysql自增id的这些你可能还不知道

    导读: 在使用MySQL建表时,我们通常会创建一个自增字段(AUTO_INCREMENT),并以此字段作为主键.本篇文章将以问答的形式讲述关于自增id的一切. 注: 本文所讲的都是基于Innodb存储引擎. 下面话不多说了,来一起随着小编看看详细的介绍吧 1.MySQL为什么建议将自增列id设为主键? 如果我们定义了主键(PRIMARY KEY),那么InnoDB会选择主键作为聚集索引.如果没有显式定义主键,则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引.如果也没有这样的唯一

  • mysql如何让自增id归0解决方案

    最近老是要为现在这个项目初始化数据,搞的很头疼,而且数据库的Id自增越来越大,要让自增重新从1开始:那么就用下面的方法吧: 方法一: 如果曾经的数据都不需要的话,可以直接清空所有数据,并将自增字段恢复从1开始计数 truncate table 表名 方法二: dbcc checkident ('table_name', reseed, new_reseed_value) 当前值设置为 new_reseed_value.如果自创建表后没有将行插入该表,则在执行 DBCC CHECKIDENT 后插

  • 利用mysql事务特性实现并发安全的自增ID示例

    项目中经常会用到自增id,比如uid,最简单的方法就是用直接用数据库提供的AUTO_INCREMENT,但是如果用户量非常大,几千万,几亿然后需要分表存储的时候呢,这种方案就搞不定了,所以最好有一个全局的自增ID的生成器,不管是否分表,都能从生成器中获取到全局自增的ID. 实现方法应该有很多,不过所有的方案都需要解决一个问题,就是保证在高并发的情景下,数据获取依然正确,每次获取的ID都不会重复. 这里我分享两种利用mysql的innodb的事务特性来实现的方案,一种是实现过了的,另一种没有试验过

  • 两种mysql对自增id重新从1排序的方法

    最近老是要为现在这个项目初始化数据,搞的很头疼,而且数据库的Id自增越来越大,要让自增重新从1开始:那么就用下面的方法吧: 方法一:如果曾经的数据都不需要的话,可以直接清空所有数据,并将自增字段恢复从1开始计数 truncate table 表名 方法二:dbcc checkident ('table_name', reseed, new_reseed_value) 当前值设置为 new_reseed_value.如果自创建表后没有将行插入该表,则在执行 DBCC CHECKIDENT 后插入的

  • mysql自增ID起始值修改方法

    在mysql中很多朋友都认为字段为AUTO_INCREMENT类型自增ID值是无法修改,其实这样理解是错误的,下面介绍mysql自增ID的起始值修改与设置方法.通常的设置自增字段的方法:创建表格时添加: 复制代码 代码如下: create table table1(id int auto_increment primary key,...) 创建表格后添加: 复制代码 代码如下: alter table table1 add id int auto_increment primary key 自

  • 关于MySQL自增ID的一些小问题总结

    下面这几个小问题都是基于 InnoDB 存储引擎的. 1. ID最大的记录删除后,新插入的记录ID是什么 例如当前表中有ID为1,2,3三条记录,把3删除,新插入记录的ID从哪儿开始? 答案: 从4开始. 实验 创建表 tb0,ID自增: create table tb0(id int unsigned auto_increment primary key); 插入3条记录: insert into tb0 values(null); 删除ID为3的记录: delete from tb0 whe

  • MySQL表自增id溢出的故障复盘解决

    问题:MySQL某个表自增id溢出导致某业务block 背景: tokudb引擎的一个大表tb1,存放业务上的机审日志,每天有大量的写入, 并且由于历史原因,这张表是int signed 类型的,最大只能存 2147483647行记录 . 处理过程: 增加DBLE中间件代理,然后做range分区,将新数据写到新加的的一个分片上. 同时业务上修改连接将这个表tb1的连接方式改走DBLE. 但是业务上改完代码后,发现还有残余的部分insert into tb1的写请求被转发到了老的表上,且有些表被错

  • 关于mysql自增id，你需要知道的

    导读:在使用MySQL建表时,我们通常会创建一个自增字段(AUTO_INCREMENT),并以此字段作为主键.本篇文章将以问答的形式讲述关于自增id的一切. 注: 本文所讲的都是基于Innodb存储引擎. 1.MySQL为什么建议将自增列id设为主键? 如果我们定义了主键(PRIMARY KEY),那么InnoDB会选择主键作为聚集索引.如果没有显式定义主键,则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引.如果也没有这样的唯一索引,则InnoDB会选择内置6字节长的ROWID

  • MySQL的自增ID(主键) 用完了的解决方法

    在 MySQL 中用很多类型的自增 ID,每个自增 ID 都设置了初始值.一般情况下初始值都是从 0 开始,然后按照一定的步长增加(一般是自增 1).一般情况下,我们都是用int(11)来作为数据表的自增 ID,在 MySQL 中只要定义了这个数的字节长度,那么就会有上限. MySQL的自增ID(主键) 用完了,怎么办? 如果用 int unsigned (int,4个字节 ), 我们可以算下最大当前声明的自增ID最大是多少,由于这里定义的是 int unsigned,所以最大可以达到2的32幂

  • Mysql数据库自增id、uuid与雪花id详解

    目录 概念介绍 三种主键 聚簇索引与非聚簇索引 自增id uuid 雪花id与应用 总结 概念介绍 三种主键 自增id :1 2 3 4 5…… uuid :UUID是Universally Unique Identifier的缩写,它是在一定的范围内(从特定的名字空间到全球)唯一的机器生成的标识符.通用唯一标识符的意思,可以以业务实际user id为主键 比如QQ号 手机号等 雪花id :相比UUID无序生成的id而言,雪花算法是有序的(有时间参数),而且都是由数字组成.雪花id最大为64位,

  • 线上MySQL的自增id用尽怎么办

    线上MySQL的自增id用尽怎么办

    目录 表定义自增值id InnoDB系统自增row_id Xid Innodb trx_id InnoDB数据可见性的核心思想 thread_id 总结 MySQL的自增id都定义了初始值,然后不断加步长.虽然自然数没有上限,但定义了表示这个数的字节长度,计算机存储就有上限.比如,无符号整型(unsigned int)是4个字节,上限就是2^32 - 1.那自增id用完,会怎么样? 表定义自增值id 表定义的自增值达到上限后的逻辑是:再申请下一个id时,得到的值保持不变. mysql> crea

随机推荐