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 t2G
*************************** 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 t2G
*************************** 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 t2G
*************************** 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 t2G
*************************** 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 t2G
*************************** 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 t2G
*************************** 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 t2G
*************************** 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 t2G
*************************** 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 t2G
*************************** 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 t2G
*************************** 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)