Analysis of the auto_increment problem in Mysql _mysql

Today in the forum, found a seemingly very simple problem, but it has aroused widespread concern:
This is a very early interview question:
A table, which has ID from the primary key, when the insert 17 records, delete the first 15,16,17 records, and then restart the MySQL, and then insert a record, this record ID is 18 or 15.
Answer:

If the type of the table is MyISAM, then it is 18.
Because the MyISAM table will record the maximum ID of the self-added primary key to the data file, the maximum ID for restarting the MySQL self-added primary key will not be lost.
If the type of the table is InnoDB, then it is 15.
The InnoDB table simply records the maximum ID of the self-added primary key to memory, so restarting the database or optimize the table will cause the maximum ID to be lost.

I did an experiment, and the results confirmed the above statement. What a shame, the seemingly simple problem is wrong.

Copy Code code as follows:

Mysql> select * from Test1;
+----+-----------+
| ID | name |
+----+-----------+
| 1 | Chen Binghui |
| 2 | Chen |
| 3 | Chen |
| 4 | Chen |
| 5 | Chen |
| 6 | Chen |
| 9 2 Chen |
| 8 | Chen |
| 9 | Chen |
| 10 | Chen |
| 11 | Chen |
+----+-----------+
Rows in Set (0.00 sec)

Mysql> Delete from test1 where ID in (10,11,9);
Query OK, 3 rows affected (0.03 sec)

Mysql> Show CREATE TABLE test1;
CREATE TABLE ' test1 ' (
' id ' int (one) not NULL auto_increment,
' Name ' varchar (a) default NULL,
PRIMARY KEY (' id ')
) Engine=innodb Auto_increment=<span style= "COLOR: #ff0000" >12</SPAN> DEFAULT Charset=utf8 |
Mysql> exit;
Bye
[root@fsailing1 ~]# Service mysqld Restart
Stop MySQL: [OK]
Start MySQL: [OK]
[Root@fsailing1 ~]# Mysql-uroot-p
Enter Password:
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 2
Server version:5.0.95 Source Distribution
Copyright (c), the Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark the Oracle Corporation and/or its
Affiliates. Names may trademarks of their respective
Owners.

Type ' help, ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

mysql> use test;
Reading table information for completion of table and column names
You can turn off the feature to get a quicker startup with-a

Database changed
Mysql> Show CREATE TABLE test1;
| CREATE TABLE ' test1 ' (
' id ' int (one) not NULL auto_increment,
' Name ' varchar (a) default NULL,
PRIMARY KEY (' id ')
) Engine=innodb Auto_increment=<span style= "COLOR: #ff0000" >9</SPAN> DEFAULT Charset=utf8 |

2, and another is to get the number of self increasing fields in the current database table.

Copy Code code as follows:

Mysql> select last_insert_id ();
+------------------+
| last_insert_id () |
+------------------+
| 0 |
+------------------+
1 row in Set (0.00 sec)