mysql自增id原理_《MySQL自增ID》告诉你不为人知的“秘密”......

原标题：《MySQL自增ID》告诉你不为人知的“秘密”......

作者：Sunshine Koo

1.概述

“

MySQL数据库是最常使用的数据库之一，我们经常需要用到它的自增ID来标识记录。在MySQL中，可通过数据列的auto_increment属性来自动生成。也可以在建表时可用“auto_increment=n”选项来指定一个自增的初始值。可用“alter table table_name auto_increment=n”命令来重设自增的起始值，当然在设置的时候MySQL会取数据表中auto_increment列的最大值+1与n中的较大者作为新的auto_increment值。

”

Mysql的auto_increment属性具有以下特性：

(1) 它是一个正数序列，如果把该数据列声明为UNSIGNED，编号个数可增加一倍。假如设置的是tinyint类型最大编号是127，如果加上UNSIGNED，那么最大编号变为255

(2) auto_increment数据列必须有唯一索引，以避免序号重复；必须具备NOT NULL属性。

那么，MySQL自增ID用过的都说好用，但是没用对，可能发生灾难...... 为什么这样说？因为我们最近在一个老项目上踩到了MySQL自增ID的大坑，事情是这样的...

神级爆款项目(暂且这样叫)在设计未来合服逻辑时，为了考虑不让ID发生冲突，聪明地在部署每个新服时强制带上服标识号作为自增ID的起始号，例如：

S1101服，部署脚本会强制指定 auto_increment=110100000000 作为该服的自增ID号。

S1102服，部署脚本会强制指定 auto_increment=110200000000 作为该服的自增ID号。

这样一来，在执行合服的过程中是绝对不会发生ID冲突的问题，每个服的玩家ID号是完全唯一的。相信很多项目也是这么设计。。。(我猜的)

但是问题来了，这么好的设计为什么说有坑呢？原因是我们使用了InnoDB作为存储引擎，Innodb引擎的表指定了一个auto_increment列，那么这张表会有一个auto_increment计数器，专门记录当前auto_increment的相关值，用来在insert时为auto_increment列赋值。非常重要的一点是，这个计数值是保存在内存中的，而非磁盘上。

当MySQL运行时，这个计数值随着insert增长。假设表中有100条记录，auto_increment计数值是101，当delete所有的记录后，再insert一条语句，这条语句的id便是101。但是如果把表清空了，再启动MySQL，auto_increment的值就会变为1，因为这个值是InnoDB存储在内存中，如果表中没有任何记录，auto_increment的值是会被重置的。

我们先回到刚才那个神级爆款项目(暂且这样叫)，看看这个项目涉及自增ID的表：

由于是一次半夜里机柜意外的断电，导致机器关机自动重启，MySQL和所服游戏服都被重启，重启之后游戏服并没有发生什么异常，也能正常玩。但过两周进行合服的时候，突然报ID冲突了。

百思不得其解啊！明明设计之初就已经规避了的问题。因此对所有自增表进行了扫描，发现以下情况，真的无语了！

机器被重启之前，这个服还没有对外开放，表里还是空的，正常来说部署的时候已经给ID赋值了106800000000，起始号应当是106800000000。当这个服开了之后，ID号就会106800000000+n。正因为机器被重启过，而且表也是空的，导致了ID自增号恢复为1，所以看到上面ID编号不对了，合服时也就发生了ID冲突。

2.揭晓真相

为了一究到底，我们做了一个实验来验证它。当然也就选用了MySQL两大优秀主流引擎 InnoDB和MyISAM。看看真实如此？

(1)首先测试的是InnoDB引擎，采用方法是建一个表，然后插入一些数据，再把所有数据清空，然后重启MySQL服务。

CREATE TABLE `table1` ( `id` tinyint( 3) NOT NULL auto_increment, `name` varchar( 50), `time` datetime DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

建了一个table1的表，然后插入了9条数据，目前自增ID号为10。

然后把所有记录清空，再查看自增ID号仍为10。

然而，重启MySQL服务后，再查看自增ID号已变为1了。

(2)接下来看看MyISAM引擎，用样的方式先建一个表，然后插入一些数据，再把所有数据清空，然后重启MySQL服务。

CREATE TABLE `table1` ( `id` tinyint( 3) NOT NULL auto_increment, `name` varchar( 50), `time` datetime DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;

建了一个table1的表，然后插入了9条数据，目前自增ID号为10。

然后把所有记录清空，再查看自增ID号仍为10。

重启MySQL服务后，再查看自增ID号依然是10 ！！！

结论：

(1) InnoDB引擎的表，执行清空操作之后，表的auto_increment值不会受到影响；一旦重启MySQL，auto_increment值将变成1。

(2) MyISAM引擎的表，执行清空操作之后，表的auto_increment值不会受到影响；重启MySQL，auto_increment值也不会受到影响。

(3) 以上两个引擎在表中还有记录的情况下，若auto_increment指定了起始号，即使重启MySQL后，表的auto_increment值不会被重置。但InnoDB的auto_increment值会被设置为当前最后一条记录的ID+1。

(4) 证明InnoDB引擎不会在本地记录auto_increment值，它会在启动后以表中最后一条记录ID+1，作为auto_increment值。类似执行：

select max(id) maxId from table;

alter table auto_increment = maxId + 1;

3.延伸

关于InnoDB引擎对AUTO_INCREMENT计数器初始化的解析，可阅读https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html，这篇官方文档说明。

在官网上明确说明MySQL5.7以下版本的InnoDB引擎不会记录在磁盘上，而MySQL8.0版本以将会改变这个方式，让AUTO_INCREMENT计数值能够保持最后一次的ID值。

那么你的业务有涉及到类似的用法，你又不想有后续踩到坑，在业务允许的情况下，要么更新MySQL版本。要么就乖乖地做好修复方案SOP。

修复方案也很简单，例如要修复以下表：

AUTO_INCREMEN起始值原本是106800000000，现在需要把表的AUTO_INCREMEN自增ID为了10680000011，同时里面记录也需要106800000000+n。

alter table table1 auto_increment = 106800000000;

update table1 set id=106800000001+id where id < 106800000000;

每次重启MySQL后跑以上两条SQL。当然这不是最好的解决方法，只是万不得已才使用！

END

公众号

ywjtshare

专注运维技术与传承，分享丰富原创干货返回搜狐，查看更多

责任编辑：