MySQL自增列ID的面试题--AUTO_INCREMENT
Tags： MySQL
👉 本文共约305个字，系统预计阅读时间或需2分钟。
在一张表中，里面有ID自增主键，当insert了17条记录之后，删除了第15,16,17条记录，再把MySQL重启，再Insert一条记录，
这条记录的ID是18还是15？
在MySQL 8.0之前，对于MyISAM和InnoDB的表，因为存储引擎对于自增列的实现机制不同，ID值也会有所不同，
对于InnoDB存储引擎的表，ID是按照max(id
所以服务重启之后，还是可以继续追溯这个自增列的ID变化情况的。
所以，对于MyISAM表来说，若数据库重启后，则ID值为18；
对于InnoDB表来说，若数据库重启后，则对于MySQL 8.0来说，ID值为18，
对于MySQL 8.0之前的数据库来说，则数据库重启后，ID值为15。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 | 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) |
