mysql自增长id用完了该怎么办

mysql自增长id用完了，怎么办？

作为一名程序员，在求职面试时，不知你有没有遇到类似这样的问题。

张工是一名java程序员，最近到一家互联网公司面试，面试官就问了他这样的一个问题。

面试官:"用过mysql吧，你们数据表主键id是用自增主键还是UUID？"

张工:"用的是自增主键"

面试官:"为什么是自增主键？"

张工:"因为采用自增主键，数据在物理结构上是顺序存储，性能好"

面试官:"那自增主键达到最大值了，用完了怎么办？"

张工：“用完了就用完了，再申请呗”

面试官：“你可以回去等通知了”

今天我们就来谈一谈，这个自增主键用完了该怎么办？

在mysql，int整型的范围如下int的取值范围为：-2^31——2^31-1，即-2147483648—2147483647

如图：

以无符号整型为例，存储范围为0～4294967295，约43亿。当自增id达到最大值时，这是继续插入会出现什么异常呢，

我们来动手实践下。

首先，创建一张表tb_user，这张表只包含一个自增id

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

然后向这张表插入一条数据：

insert into tb_user values(null);

通过show命令show create table tb_user;查看表情况：

CREATE TABLE `tb_user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

细心的你会发现 AUTO_INCREMENT 已经变成2，不过这离最大值4294967295远着呢，要想让它变成4294967295得插入非常多的记录，其实不用这么麻烦，我们可以在创建表的时候，直接声明AUTO_INCREMENT的初始值。

把我们刚才的创建表语句调整下，先把刚才的表删除掉，然后在创建表时加上auto_increment = 4294967295

create table tb_user(id int unsigned auto_increment primary key) auto_increment = 4294967295;

然后同样往表插入一条记录

insert into tb_user values(null);

同样，我们通过show命令，查看表tb_user的表结构：

CREATE TABLE `tb_user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=utf8

通过

select * from tb_user

我们查询到id 为4294967295，已经是最大值，这时候如果再

当想往表在尝试插入一条数据时，报一个主键冲突异常如下所示。

[SQL]insert into tb_user values(null);
[Err] 1062 - Duplicate entry '4294967295' for key 'PRIMARY'

这可以说明，当再次插入时，使用的自增ID还是4294967295，就会报主键冲突的异常了。

4294967295，这个数字已经可以应付大部分的场景了，如果你的服务会经常性的插入和删除数据的话，还是存在用完的风险。

建议采用bigint unsigned，这个数字就大了。

那有什么办法解决，答案是肯定的，解决方法也是很简单的，将Int类型改为BigInt类型，BigInt的范围如下

-2^63-1到2^63-1

-9223372036854775808  9223372036854775807

就算每秒往数据表插入10000条数据，运行100年，来看看数据量有多少

10000*24*3600*365*100=31536000000000

这数字距离BigInt的上限还差的远，因此你将自增ID设为BigInt类型，就可以解决问题了。

如果你在面试中是这样回答面试官的。

你:"这还不简单，把自增主键的类型改为BigInt类型就可以解决了！"

面试官:"你在线上怎么修改列的数据类型的？"

你:"alter table tb_user change id  id bigint;"

面试官：“你有实际操作经验吗？”

你：“…………没有实际操作过”

需要注意的是，这种方式在myl5.6+才开始支持，mysql支持在线修改数据库表，在修改表的过程中，对绝大部分操作，原表可读，也可以写。

对于修改数据类型这种操作，是不支持并发的DML操作！也就是说，如果你直接使用alter这样的语句在线修改表数据结构，会导致这张表无法进行更新类操作(delete、update、insert)。所以，想在生产线上执行修改表结构这样的方案是不可行的。

那有没有更好的方式，对于这个问题，我们以后再做讨论。

不知你有没有留意到这样一种情况，虽然主键自增ID是从0开始的，也就是说，现在可以用的范围为0～2147483647，但实际数据中有些id的值并不是连续的。

要是实际生产表出现单表超过上亿的数据量了，这时候想再往数据表写数据，性能肯定是受影响了，得赶紧考虑分库分表了。

一旦分库分表了，我们就不能依赖于每个表的自增id来全局唯一标识这些数据了。此时，我们就需要提供一 个全局唯一的id号生成策略来支持分库分表的环境。

所以在实际中，根本等不到自增主键用完的情况。

较友好的回答不妨参考这样的

面试官:"那自增主键达到最大值了，用完了怎么办？"

你:这问题没遇到过，因为自增主键我们用int类型，一般达不到最大值，就要考虑分表分库了。

要是面试官穷追不舍，继续问你有关分库分表的要点，你也就可以针对性地回答，说明你完全有这方面的开发经验，相信能为这次面试加分。

总结：

mysql数据库表的自增 ID 达到上限之后，这时候再申请它的值就不会在改变了，如果继续插入数据就会导致报主键冲突异常。

因此在做数据字典设计时，要根据业务的需求来选择合适的字段类型。

到此这篇关于mysql自增长id用完了该怎么办的文章就介绍到这了,更多相关mysql自增长id内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

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

  • 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自增id超大问题的排查与解决

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

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

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

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

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

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

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

  • 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用完了该怎么办

    mysql自增长id用完了该怎么办

    mysql自增长id用完了,怎么办? 作为一名程序员,在求职面试时,不知你有没有遇到类似这样的问题. 张工是一名java程序员,最近到一家互联网公司面试,面试官就问了他这样的一个问题. 面试官:"用过mysql吧,你们数据表主键id是用自增主键还是UUID?" 张工:"用的是自增主键" 面试官:"为什么是自增主键?" 张工:"因为采用自增主键,数据在物理结构上是顺序存储,性能好" 面试官:"那自增主键达到最大值了,用

  • 设置MySQL自动增长从某个指定的数开始方法

    自增字段,一定要设置为primary key. 以指定从1000开始为例. 1 创建表的时候就设置: CREATE TABLE `Test` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `NAME` varchar(50) NOT NULL, `SEX` varchar(2) NOT NULL, PRIMARY KEY (`ID`) ) ENGINE=MEMORY AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8 CHECKSU

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

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

  • mysql主键id的生成方式(自增、唯一不规则)

    mysql主键id的生成方式(自增、唯一不规则)

    目录 1.利用uuid函数生成唯一且不规则的主键id 2.id实现自动增长 1.利用uuid函数生成唯一且不规则的主键id sql: CREATE TABLE `test` ( `id` varchar(100) COLLATE utf8_estonian_ci NOT NULL COMMENT '唯一不重复', `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `update_time` timestamp NOT NULL

  • php插入mysql数据返回id的方法

    如下所示: <?php $dbh = mysql_connect('localhost','root','root'); mysql_select_db('pkbk'); $query = "insert intov9_admin_role_priv(roleid, m, c, a, `data`, siteid) values (12, 'huiben','huiben', 'hbprop_add', '', 1)"; $res = mysql_query($query, $d

  • mysql执行计划id为空（UNION关键字）详解

    mysql执行计划id为空（UNION关键字）详解

    简介 在工作过程中,有时候会对慢查询进行调优.对于MySQL的SQL语句调优,MySQL本身提供了强大的explain关键字用于查询分析执行计划.本文主要给大家介绍了关于mysql执行计划id为空(UNION关键字)的相关内容,分享出来供大家参考学习,下面话不多说了,来一起看看详细的 介绍吧 UNION 操作符用于合并两个或多个 SELECT 语句的结果集.例如,我有两个表,表1记录的是公司男员工的数据,包括年龄.姓名.职位.表2记录的是公司女员工的数据,包括姓名.家庭住址.手机号等字段.这时,

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

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

随机推荐