MySQL 自增ID 重置问题

时间：2019-03-24 21:08:52 阅读：164 评论：0 收藏：0 [点我收藏+]

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)

MySQL 自增ID 重置问题

原文：https://blog.51cto.com/liuzhanbin/2368117

踩

(0)

评论一句话评论（0）

分享档案

更多>

2021年09月23日 (328)
2021年09月24日 (313)
2021年09月17日 (191)
2021年09月15日 (369)
2021年09月16日 (411)
2021年09月13日 (439)
2021年09月11日 (398)
2021年09月12日 (393)
2021年09月10日 (160)
2021年09月08日 (222)