Auto_increment in mysql

The auto_increment problem in mysql was found to be a very simple problem during a visit to the Forum, but caused widespread attention: This is a very early interview question:
A table contains an ID auto-incrementing primary key. After 17 records are inserted, the records 15, 16, and 17 are deleted, and Mysql is restarted,
Insert a record. The ID of this record is 18 or 15. Www.2cto.com answer: If the table type is MyISAM, it is 18. Because the MyISAM table records the maximum ID of the auto-incrementing primary key to the data file, the maximum ID of the restart MySQL auto-incrementing primary key will not be lost. If the table type is InnoDB, it is 15. The InnoDB table only records the maximum ID of the auto-increment primary key to the memory. Therefore, you can restart the database or OPTIMIZE the table,
The maximum ID is lost. I also did an experiment and the results confirmed the above statement. I am ashamed to say that I have made a mistake in the case of a simple problem. [Html] mysql> select * from test1; + ---- + ----------- + | id | name | + ---- + ----------- + | 1 | chen binhui | 2 | chen | 3 | chen | 4 | chen | 5 | chen | 6 | chen | 7 | chen | 8 | chen | 9 | chen | 10 | chen | 11 | chen | + ---- + ----------- + 11 rows in set (0.00 sec) www.2cto.com mysql> delete from test1 where id in (0.03, 9); Query OK, 3 rows affected (sec) mysql> show create table tes T1; create table 'test1' ('id' int (11) not null auto_increment, 'name' varchar (10) 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) 2000,201 1, Oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of Oracle Corporation and/or its affiliates. other names may be 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 this feature to get a quicker startup with-A Database changed mysql> show create table test1; | create table 'test1' ('id' int (11) not null auto_increment, 'name' varchar (10) default NULL, primary key ('id ')) ENGINE = InnoDB AUTO_INCREMENT = <span style = "color: # ff0000;"> 9 </span> default charset = utf8 | www.2cto.com
2. You can also obtain the number of auto-increment fields in the current database table. [Html] mysql> select last_insert_id (); + ------------------ + | last_insert_id () | + -------------------- + | 0 | + ------------------ + 1 row in set (0.00 sec) Author chen86120