MySQL自增列ID的面試題

lhrbest發表於2020-04-29

在一張表中，裡面有ID自增主鍵，當insert了17條記錄之後，刪除了第15,16,17條記錄，再把MySQL重啟，再Insert一條記錄，這條記錄的ID是18還是15？
在MySQL 8.0之前，對於MyISAM和InnoDB的表，因為儲存引擎對於自增列的實現機制不同，ID值也會有所不同，對於InnoDB儲存引擎的表，ID是按照max(id)+1的演算法來計算的。在MySQL 8.0之後，InnoDB的自增列資訊寫入了共享表空間中，所以服務重啟之後，還是可以繼續追溯這個自增列的ID變化情況的。
所以，對於MyISAM表來說，若資料庫重啟後，則ID值為18；對於InnoDB表來說，若資料庫重啟後，則對於MySQL 8.0來說，ID值為18，對於MySQL 8.0之前的資料庫來說，則資料庫重啟後，ID值為15。
MySQL [(none)]> use lhrdb;
Database changed
MySQL [lhrdb]> select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.29    |
+-----------+
1 row in set (0.00 sec)
MySQL [lhrdb]>
MySQL [lhrdb]> create table test_innodb(id int primary key auto_increment,name varchar(30)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)
MySQL [lhrdb]> create table test_myisam(id int primary key auto_increment,name varchar(30)) engine=myisam;
Query OK, 0 rows affected (0.00 sec)
MySQL [lhrdb]>
MySQL [lhrdb]> insert into test_innodb(name) values('aa'),('bb'),('cc');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
MySQL [lhrdb]> insert into test_myisam(name) values('aa'),('bb'),('cc');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
MySQL [lhrdb]>
MySQL [lhrdb]> insert into test_innodb(id,name) values(5,'ee');
Query OK, 1 row affected (0.00 sec)
MySQL [lhrdb]> insert into test_myisam(id,name) values(5,'ee');
Query OK, 1 row affected (0.00 sec)
MySQL [lhrdb]>
MySQL [lhrdb]> show create table test_innodb \G
*************************** 1. row ***************************
       Table: test_innodb
Create Table: CREATE TABLE `test_innodb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
MySQL [lhrdb]> show create table test_myisam \G
*************************** 1. row ***************************
       Table: test_myisam
Create Table: CREATE TABLE `test_myisam` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
MySQL [lhrdb]>
MySQL [lhrdb]> delete from test_innodb where id=5;
Query OK, 1 row affected (0.00 sec)
MySQL [lhrdb]> delete from test_myisam where id=5;
Query OK, 1 row affected (0.00 sec)
MySQL [lhrdb]>
MySQL [lhrdb]> show create table test_innodb \G
*************************** 1. row ***************************
       Table: test_innodb
Create Table: CREATE TABLE `test_innodb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
MySQL [lhrdb]> show create table test_myisam \G
*************************** 1. row ***************************
       Table: test_myisam
Create Table: CREATE TABLE `test_myisam` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
MySQL [lhrdb]> select @@version;
ERROR 2013 (HY000): Lost connection to MySQL server during query
MySQL [lhrdb]> select @@version;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: lhrdb
+-----------+
| @@version |
+-----------+
| 5.7.29    |
+-----------+
1 row in set (0.40 sec)
MySQL [lhrdb]>
MySQL [lhrdb]> show create table test_innodb \G
*************************** 1. row ***************************
       Table: test_innodb
Create Table: CREATE TABLE `test_innodb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.01 sec)
MySQL [lhrdb]> show create table test_myisam \G
*************************** 1. row ***************************
       Table: test_myisam
Create Table: CREATE TABLE `test_myisam` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
MySQL [lhrdb]>
MySQL [lhrdb]>
MySQL [lhrdb]> insert into test_innodb(name) values('ee');
Query OK, 1 row affected (0.01 sec)
MySQL [lhrdb]> insert into test_myisam(name) values('ee');
Query OK, 1 row affected (0.00 sec)
MySQL [lhrdb]>
MySQL [lhrdb]> select * from test_innodb;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
|  4 | ee   |
+----+------+
4 rows in set (0.00 sec)
MySQL [lhrdb]> select * from test_myisam;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
|  6 | ee   |
+----+------+
4 rows in set (0.00 sec)
MySQL [lhrdb]> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.19    |
+-----------+
1 row in set (0.00 sec)
MySQL [(none)]> use lhrdb;
Database changed
MySQL [lhrdb]> create table test_innodb(id int primary key auto_increment,name varchar(30)) engine=innodb;
Query OK, 0 rows affected (0.03 sec)
MySQL [lhrdb]> create table test_myisam(id int primary key auto_increment,name varchar(30)) engine=myisam;
Query OK, 0 rows affected (0.01 sec)
MySQL [lhrdb]>
MySQL [lhrdb]> insert into test_innodb(name) values('aa'),('bb'),('cc');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
MySQL [lhrdb]> insert into test_myisam(name) values('aa'),('bb'),('cc');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
MySQL [lhrdb]>
MySQL [lhrdb]> insert into test_innodb(id,name) values(5,'ee');
Query OK, 1 row affected (0.01 sec)
MySQL [lhrdb]> insert into test_myisam(id,name) values(5,'ee');
Query OK, 1 row affected (0.00 sec)
MySQL [lhrdb]>
MySQL [lhrdb]> show create table test_innodb \G
*************************** 1. row ***************************
       Table: test_innodb
Create Table: CREATE TABLE `test_innodb` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
MySQL [lhrdb]> show create table test_myisam \G
*************************** 1. row ***************************
       Table: test_myisam
Create Table: CREATE TABLE `test_myisam` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
MySQL [lhrdb]>
MySQL [lhrdb]> delete from test_innodb where id=5;
Query OK, 1 row affected (0.01 sec)
MySQL [lhrdb]> delete from test_myisam where id=5;
Query OK, 1 row affected (0.00 sec)
MySQL [lhrdb]>
MySQL [lhrdb]> show create table test_innodb \G
*************************** 1. row ***************************
       Table: test_innodb
Create Table: CREATE TABLE `test_innodb` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
MySQL [lhrdb]> show create table test_myisam \G
*************************** 1. row ***************************
       Table: test_myisam
Create Table: CREATE TABLE `test_myisam` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
MySQL [lhrdb]> select @@version;
ERROR 2013 (HY000): Lost connection to MySQL server during query
MySQL [lhrdb]> select @@version;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    8
Current database: lhrdb
+-----------+
| @@version |
+-----------+
| 8.0.19    |
+-----------+
1 row in set (0.05 sec)
MySQL [lhrdb]>
MySQL [lhrdb]> show create table test_innodb \G
*************************** 1. row ***************************
       Table: test_innodb
Create Table: CREATE TABLE `test_innodb` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.02 sec)
MySQL [lhrdb]> show create table test_myisam \G
*************************** 1. row ***************************
       Table: test_myisam
Create Table: CREATE TABLE `test_myisam` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
MySQL [lhrdb]>
MySQL [lhrdb]>
MySQL [lhrdb]> insert into test_innodb(name) values('ee');
Query OK, 1 row affected (0.01 sec)
MySQL [lhrdb]> insert into test_myisam(name) values('ee');
Query OK, 1 row affected (0.00 sec)
MySQL [lhrdb]>
MySQL [lhrdb]>
MySQL [lhrdb]> select * from test_innodb;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
|  6 | ee   |
+----+------+
4 rows in set (0.00 sec)
MySQL [lhrdb]> select * from test_myisam;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
|  6 | ee   |
+----+------+
4 rows in set (0.00 sec)

https://mp.weixin.qq.com/s?__biz=MjM5ODEzNDA4OA==&mid=2650315359&idx=1&sn=61801f8b806f6ec7ff1a3a433ccf2f70&chksm=bec3527889b4db6ec3cc1e2d970c58d787cc34dca6d7d7dd2b1bbb3f914382e7f86a01a366a7&mpshare=1&scene=1&srcid=&sharer_sharetime=1581380653038&sharer_shareid=056c2fdf5dd5650130a6a3cea2b1cc1b&key=541e2b75854761481f047acde19e89d4db35200f528a7c21a58c7f265662e6f515306bdc9cdc4978a2a1c86d2997355e0372fdeed0f35dc4701da38219d92088c5b7aec026687f311367cfcd77363fa9&ascene=1&uin=MTk5MDM4ODY5&devicetype=Windows+10+x64&version=62090072&lang=zh_CN&exportkey=A1Mzq6smdQcOiGaHXYmuATE%3D&pass_ticket=ughIvtE%2BmsBr9uYKXlb%2B%2FwQ0SieoXj2IPkRlWrcyuiI%3D

在MySQL 8.0之前：

1）如果是MyISAM表，則資料庫重啟後，ID值為18

2）如果是InnoDB表，則資料庫重啟後，ID值為15

在MySQL 8.0開始，

1）如果是MyISAM表，則資料庫重啟後，ID值為18

2）如果是InnoDB表，則資料庫重啟後，ID值為18

此處需要補充的是，對於ID自增列，在MySQL 5.7中可以使用sys schema來進行有效監控了,可以檢視檢視 schema_auto_increment_columns 來進行列值溢位的有效判斷。

更難能可貴的是，如果是MySQL 5.7版本以下，雖然沒有sys schema特性，但是可以複用MySQL 5.7中的 schema_auto_increment_columns 的檢視語句，也是可以對列值溢位進行有效判斷的。

About Me

........................................................................................................................

● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除

● 本文在itpub、部落格園、CSDN和個人微 信公眾號（
DB寶）上有同步更新

● 本文itpub地址：
http://blog.itpub.net/26736162 

● 本文部落格園地址：
http://www.cnblogs.com/lhrbest 

● 本文CSDN地址：
https://blog.csdn.net/lihuarongaini 

● 本文pdf版、個人簡介及小麥苗雲盤地址：
http://blog.itpub.net/26736162/viewspace-1624453/ 

● 資料庫筆試面試題庫及解答：
http://blog.itpub.net/26736162/viewspace-2134706/ 

● DBA寶典今日頭條號地址：
http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826 

........................................................................................................................

● QQ群號：
230161599
、618766405

● 微 信群：可加我微 信，我拉大家進群，非誠勿擾

● 聯絡我請加QQ好友
（
646634621
），註明新增緣由

● 於 2020-04-01 06:00 ~ 2020-04-30 24:00 在西安完成

● 最新修改時間：2020-04-01 06:00 ~ 2020-04-30 24:00

● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

........................................................................................................................

● 
小麥苗的微店：
https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

● 
小麥苗出版的資料庫類叢書：
http://blog.itpub.net/26736162/viewspace-2142121/

● 
小麥苗OCP、OCM、高可用網路班：
http://blog.itpub.net/26736162/viewspace-2148098/

● 
小麥苗騰訊課堂主頁：
https://lhr.ke.qq.com/

........................................................................................................................
使用

微 信客戶端掃描下面的二維碼來關注小麥苗的微 信公眾號（
DB寶）及QQ群（DBA寶典）、新增小麥苗微 信，
學習最實用的資料庫技術。 


........................................................................................................................

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2689290/，如需轉載，請註明出處，否則將追究法律責任。

mysql自增列
2016-03-07
MySql
MySQL中的自增列
2018-05-07
MySql
MySQL·8.0.0新特性·持久化自增列值
2016-09-22
MySql持久化
如何解決自增列賦值的問題
2019-10-22
賦值
MySQL自增列的重複值問題(r12筆記第25天)
2017-04-05
MySql筆記
MySQL死鎖案例二（自增列導致死鎖）
2020-08-21
MySql
DMSQL DM自增列的使用
2020-03-02
SQL
通過shell指令碼模擬MySQL自增列的不一致問題
2017-07-13
指令碼MySql
【mysql】table中新增列
2019-01-14
MySql
mysql的自增id的一個問題
2010-04-26
MySql
SQLServer插入資料到有自增列的表
2019-12-02
SQLServer
面試題：MySQL事務的ACID如何實現？
2023-10-28
面試題MySql
MySQL 的面試題集
2020-02-03
MySql面試題
面試題-mysql
2019-03-07
面試題MySql
SQL Server自增列跳號總結
2024-03-08
SQLServer
java面試一日一題：mysql中的自增主鍵
2021-04-28
Java面試MySql
MySql給表新增列和註釋
2019-03-16
MySql
MySQL alter 新增列對dml影響
2022-08-24
MySql
樂視mysql面試題
2016-05-04
MySql面試題
MySQL自增列鎖模式 innodb_autoinc_lock_mode不同引數下效能測試
2018-09-13
MySql模式
SQL在自增列插入指定資料的操作方法
2019-01-01
SQL
Android 面試之某些公司的面試題
2016-12-10
Android面試題
mysql面試練習題
2018-07-31
MySql面試
MySQL高頻面試題
2023-05-11
MySql面試題
Mysql 常見面試題
2021-11-18
MySql面試題
mysql 面試題彙總
2020-10-11
MySql面試題
MySQL-新增列，新增或修改欄位的註釋
2017-11-15
MySql
DB2 和SQL Server自增列比較
2018-09-21
DB2SQLServer
Android面試題
2012-06-27
Android面試題
MySQL自增列主從不一致的測試(r12筆記第37天)
2017-04-17
MySql筆記
iOS面試題·自整理·Two
2016-06-26
iOS面試題
MySQL 事務常見面試題總結 | JavaGuide 稽核中
2022-05-23
MySql面試題JavaGUIIDE
mysql面試常見題目
2018-05-20
MySql面試
mysql一道面試題
2013-01-02
MySql面試題
Java高頻面試題---MySQL
2024-03-14
Java面試題MySql
SQLite新增列的限制
2017-04-19
SQLite
Android面試題收集
2017-02-17
Android面試題
Android面試題(四)
2017-12-13
Android面試題

MySQL自增列ID的面試題

相關文章