A feature of the identity in MySQL

If the table contains a column auto_increment,

If the engine is of the MyISAM type, then the most recent data is deleted, regardless of whether or not MySQL is restarted, the last deleted maximum id+1 will still be used after the next insert.

mysql> CREATE TABLE Test_myisam (ID int not NULL auto_increment primary key, name C Har (5)) Engine=myisam; Query OK, 0 rows affected (0.04 sec) mysql> insert into Test_myisam (name) select ' A '; Query OK, 1 row Affected (0.00 sec) records:1 duplicates:0 warnings:0mysql> insert into Test_myisam (name) SELECT ' B '; Query OK, 1 row Affected (0.00 sec) records:1 duplicates:0 warnings:0mysql> insert into Test_myisam (name) SELECT ' C '; Query OK, 1 row Affected (0.00 sec) records:1 duplicates:0 warnings:0mysql> insert into Test_myisam (name) Select n Ame from Test_myisam; Query OK, 3 Rows Affected (0.00 sec) Records:3 duplicates:0 warnings:0mysql> SELECT * FROM test_myisam;+----+------ +| ID |  Name |+----+------+| 1 |  A | | 2 |  B | | 3 |  C | | 4 |  A | | 5 |  B | | 6 | C |+----+------+6 rows in Set (0.00 sec) mysql> Delete from Test_myisam where id=6; Query OK, 1 row Affected (0.00 sec) 

mysql> INSERT into Test_myisam (name) select ' d '; Query OK, 1 row Affected (0.00 sec) records:1  duplicates:0  warnings:0mysql> select * FROM test_myisam;+----+- -----+| ID | Name |+----+------+|  1 | A    | |  2 | b    | |  3 | C    | |  4 | A    | |  5 | b    | |  7 | D    |+----+------+6 rows in Set (0.00 sec)

The following is a test of the InnoDB table.

Mysql> CREATE TABLE Test_innodb (ID int not NULL auto_increment primary key, name char (5)) Engine=innodb; Query OK, 0 rows affected (0.26 sec) mysql> insert into TEST_INNODB (name) select ' A '; Query OK, 1 row affected (0.06 sec) records:1 duplicates:0 warnings:0mysql> insert into TEST_INNODB (name) SELECT ' B ‘; Query OK, 1 row affected (0.06 sec) records:1 duplicates:0 warnings:0mysql> insert into TEST_INNODB (name) SELECT ' C ‘; Query OK, 1 row affected (0.07 sec) records:1 duplicates:0 warnings:0mysql> SELECT * FROM test_innodb;+----+------+ | ID |  Name |+----+------+| 1 |  A | | 2 |  B | | 3 | C |+----+------+3 rows in Set (0.00 sec) mysql> Delete from Test_innodb where id=3; Query OK, 1 row affected (0.05 sec) mysql> insert into TEST_INNODB (name) select ' d '; Query OK, 1 row affected (0.20 sec) records:1 duplicates:0 warnings:0mysql> SELECT * FROM test_innodb;+----+------+ | ID |  Name |+----+------+| 1 |  A | | 2 |  B | | 4 | D |+----+------+3 roWS in Set (0.00 sec) mysql> exitbye[[email protected] data]$ mysql-uroot-pwsdadwelcome to the MySQL monitor. Commands End With; or \g.your MySQL connection ID is 5Server version:5.5.37-log Source distributioncopyright (c)., Oracle and/or I TS affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> use wisondatabase changedmysql> Delete from Test_innodb where id=4;  Query OK, 1 row affected (0.07 sec) mysql> exitbye[[email protected] data]$ sudo service MySQL restartshutting down Mysql... success! Starting MySQL. success!  [[email protected] data]$ mysql-uroot-pwisonwelcome to the MySQL monitor. Commands End With; or \g.your MySQL connection ID is 1Server version:5.5.37-log Source distributioncopyright (c)., Oracle and/or I TS affiliates. All RighTS reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help.  Type ' \c ' to clear the current input statement.mysql> with wisondatabase changedmysql> insert into TEST_INNODB (name) Select ' Z '; Query OK, 1 row affected (0.07 sec) records:1 duplicates:0 warnings:0mysql> SELECT * FROM test_innodb;+----+------+ | ID |  Name |+----+------+| 1 |  A | | 2 |  B | | 3 | Z |+----+------+3 rows in Set (0.00 sec)

You can see that when the MySQL database is not restarted, the newly inserted data for the InnoDB table will be the previously deleted data plus 1.

However, when the MySQL service is restarted and the data is inserted into the InnoDB table, the largest self-increment column in the current InnoDB table is added with 1.

Reason:

The MyISAM Type Storage engine table will record the maximum ID value in the data file, regardless of whether the maximum ID value is not lost or not restarted. However, the maximum ID value for the InnoDB table is in memory, and if the MySQL service is not restarted, the new join data will use the largest amount of data in memory +1. However, after a reboot, the largest value in the current table is used and +1

A feature of the identity in MySQL