彻底弄懂MySQL的自增机制

背景
MYSQL的自增列在实际生产中应用的非常广泛，相信各位所在的公司or团队，MYSQL开发规范中一定会有要求尽量使用自增列去充当表的主键，为什么DBA会有这样的要求，各位在使用MYSQL自增列时遇到过哪些问题？这些问题是由什么原因造成的呢？本文由浅入深，带领大家彻底弄懂MYSQL的自增机制。
基础扫盲

1. 通过auto_increment关键字来指定自增的列，并指定自增列的初始值为1。
   [root@localhost][test1]> Create table t(id int auto_increment,name varchar(10),primary key(id))auto_increment=1;
   Query OK, 0 rows affected (0.63 sec)
2. 自增列上必须有索引，将t表的主键索引删除掉，会报错
   [root@localhost][test1]> alter table t drop primary key;
   ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
3. 设定auto_increment_increment参数，可以调整自增步长，该参数有session级跟global级，在分库分表以及双主or多主的模式下比较有用。
4. 一个表上只能有一个自增列
5. Mysql5.7及以下版本，innodb表的自增值保存在内存中，重启后表的自增值会设为max(id)+1，而myisam引擎的自增值是保存在文件中，重启不会丢失。Mysql8.0开始，innodb的自增id能持久化了，重启mysql，自增ID不会丢。
   自增列的上限
   首先：表中自增列的上限是根据自增列的字段类型来定的。
   若设定了自增id充当主键，当达到了自增id的上限值时，会发生什么样的事情呢？还是以上面创建的 t表为例， 先回顾它的表结构：
   CREATE TABLE t (
   id int(11) NOT NULL AUTO_INCREMENT,
   name varchar(10) COLLATE utf8mb4_bin DEFAULT NULL,
   PRIMARY KEY (id)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
   无符号的int类型，上限是2147483647。这里我们将表的自增值设为2147483647，再插入两行数据：
   [root@localhost][test1]> alter table t auto_increment=2147483647;
   Query OK, 0 rows affected (0.01 sec)
   Records: 0 Duplicates: 0 Warnings: 0

[root@localhost][test1]> insert into t(name) values (‘test’);
Query OK, 1 row affected (0.01 sec)

[root@localhost][test1]> insert into t(name) values (‘test’);
ERROR 1062 (23000): Duplicate entry ‘2147483647’ for key ‘PRIMARY’
可以看到，第一个插入没问题，因为自增列的值为2147483647，这是达到了上限，还没有超过，第二行数据插入时，则报出主键