MySQL 自增ID 重置问题

innodb引擎：Innodb表把自增主键的最大ID记录到内存中，重启数据库后，都会导致最大自增ID重置。 （据说8.0 会保存自增的最大ID到表里，待验证） 第一种情况： 删除的空洞数据后面没有数据，重启后，AUTO_INCREMENT会查询最大ID并进行重置，重置后和重启前的值不同。

mysql> select * from t2;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | a    |
|  3 | a    |
|  4 | a    |
|  5 | a    |
|  6 | a    |
|  7 | a    |
|  8 | b    |
|  9 | b    |
| 10 | b    |
| 11 | b    |
| 12 | b    |
| 13 | b    |
+----+------+
13 rows in set (0.00 sec)
mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> delete from t2 where id >10;
Query OK, 3 rows affected (0.03 sec)

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> select * from t2;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | a    |
|  3 | a    |
|  4 | a    |
|  5 | a    |
|  6 | a    |
|  7 | a    |
|  8 | b    |
|  9 | b    |
| 10 | b    |
+----+------+
10 rows in set (0.00 sec)
重启：
mysqladmin -uroot -h127.0.0.1 -p -P3306 shutdown
mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> 
mysql> 
mysql> select * from t2;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | a    |
|  3 | a    |
|  4 | a    |
|  5 | a    |
|  6 | a    |
|  7 | a    |
|  8 | b    |
|  9 | b    |
| 10 | b    |
+----+------+
10 rows in set (0.00 sec)
mysql> insert into t2 (name) values('b');
Query OK, 1 row affected (0.00 sec)
mysql> 
mysql> insert into t2 (name) values('b');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t2;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | a    |
|  3 | a    |
|  4 | a    |
|  5 | a    |
|  6 | a    |
|  7 | a    |
|  8 | b    |
|  9 | b    |
| 10 | b    |
| 11 | b    |
| 12 | b    |
+----+------+
12 rows in set (0.00 sec)

第二种情况： 删除的空洞数据后面有数据，重启后，AUTO_INCREMENT会查询最大ID并进行重置，重置后和重启前的值相同。

mysql> select * from t2;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | a    |
|  3 | a    |
|  4 | a    |
|  5 | a    |
|  9 | b    |
| 10 | b    |
| 11 | b    |
| 12 | b    |
+----+------+
9 rows in set (0.00 sec)
mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> insert into t2 (name) values('b');
Query OK, 1 row affected (0.00 sec)
mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> select * from t2;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | a    |
|  3 | a    |
|  4 | a    |
|  5 | a    |
|  9 | b    |
| 10 | b    |
| 11 | b    |
| 12 | b    |
| 13 | b    |
+----+------+
10 rows in set (0.00 sec)
重启后：
mysqladmin -uroot -h127.0.0.1 -p -P3306 shutdown
mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> select * from t2;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | a    |
|  3 | a    |
|  4 | a    |
|  5 | a    |
|  9 | b    |
| 10 | b    |
| 11 | b    |
| 12 | b    |
| 13 | b    |
+----+------+
10 rows in set (0.00 sec)

**MyISam引擎：MyISAM表会把自增主键的最大ID记录到数据文件里，重启MySQL自增主键的最大ID也不会丢失。 **

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> 
mysql> 
mysql> select * from t2;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | a    |
|  3 | a    |
|  4 | a    |
|  5 | a    |
|  9 | b    |
| 10 | b    |
| 11 | b    |
| 12 | b    |
| 13 | b    |
+----+------+
10 rows in set (0.00 sec)
mysql> delete from t2 where id>10;
Query OK, 3 rows affected (0.01 sec)
mysql> select * from t2;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | a    |
|  3 | a    |
|  4 | a    |
|  5 | a    |
|  9 | b    |
| 10 | b    |
+----+------+
7 rows in set (0.00 sec)
mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

重启DB：
mysqladmin -uroot -h127.0.0.1 -p -P3306 shutdown
Database changed
mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> select * from t2;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | a    |
|  3 | a    |
|  4 | a    |
|  5 | a    |
|  9 | b    |
| 10 | b    |
+----+------+
7 rows in set (0.00 sec)
mysql> insert into t2 (name) values('b');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t2;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | a    |
|  3 | a    |
|  4 | a    |
|  5 | a    |
|  9 | b    |
| 10 | b    |
| 14 | b    |
+----+------+
8 rows in set (0.00 sec)
mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=15 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)