• 欢迎访问VPS岛网站,国外VPS,国内VPS,国外服务器,国内服务器,服务器主机,测评及优惠码,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站 QQ群

MySQL 自增ID 重置问题

mysql技术 浮生凤年 15次浏览 已收录 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)

VPS岛 的文章和资源来自互联网,仅作为参考资料,如果有侵犯版权的资源请尽快联系站长,我们会在24h内删除有争议的资源。丨 转载请注明MySQL 自增ID 重置问题
喜欢 (0)
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址