Mysql in the identity detailed introduction _mysql

If the table contains a column named Auto_increment,

If it is a MyISAM type of engine, then after the latest data is deleted, whether or not to restart MySQL, 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 Char
(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:0 mysql> insert into Test_myisam (name) Select
' B '; Query OK, 1 row Affected (0.00 sec) records:1 duplicates:0 warnings:0 mysql> insert into Test_myisam (name) Select
' C '; Query OK, 1 row Affected (0.00 sec) records:1 duplicates:0 warnings:0 mysql> insert into Test_myisam (name) Select
Name from Test_myisam;
Query OK, 3 Rows Affected (0.00 sec) records:3 duplicates:0 warnings:0 mysql> 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> deletes 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:0 mysql>

select * from Test_myisam;
+----+------+
| id | name |
+----+------+
| 1 | a  |
| 2 | b  |
| 3 | c  |
| 4 | a  |
| 5 |
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:0 mysql> insert into TEST_INNODB (name) SELECT '
B '; Query OK, 1 row affected (0.06 sec) records:1 duplicates:0 warnings:0 mysql> insert into TEST_INNODB (name) SELECT '
C ';
Query OK, 1 row affected (0.07 sec) records:1 duplicates:0 warnings:0 mysql> select * from Test_innodb; +----+------+
| ID |
name | +----+------+
| 1 | A | | 2 | B | | 3 |
C |
+----+------+ 3 rows in Set (0.00 sec) mysql> deletes 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:0 mysql> select * from Test_innodb; +----+------+
| ID |
name | +----+------+
| 1 | A | | 2 | B | | 4 |
D | +----+------+ 3 rows in Set (0.00 sec) mysql> exit Bye [2@a data]$ Mysql-uroot-pwsdad to the MySQL monitor. Commands End With;
or \g. Your MySQL Connection ID is 5 Server version:5.5.37-log Source distribution Copyright (c), 2014, Oracle and/or its Affiliates.

All rights reserved. Oracle is a registered trademark to Oracle Corporation and/or its affiliates.

The other names may is trademarks of their respective owners. Type ' help, ' or ' \h ' for help.

Type ' \c ' to clear the current input statement.
mysql> use Wison The Database changed mysql> delete from Test_innodb where id=4; Query OK, 1 row affected (0.07 sec) mysql> exit Bye [2@a data]$ sudo service mysql restart shutting down MySQL ...
success! Starting MySQL.
success! [2@a data]$ Mysql-uroot-pwison Welcome to the MySQL monitor. Commands End With;
or \g. Your MySQL Connection ID is 1 Server version:5.5.37-log Source distribution Copyright (c), 2014, Oracle and/or its Affiliates. All rights reserved.

Oracle is a registered trademark to Oracle Corporation and/or its affiliates.

The other names may is trademarks of their respective owners. Type ' help, ' or ' \h ' for help.

Type ' \c ' to clear the current input statement.
mysql> use Wison The Database changed mysql> insert into TEST_INNODB (name) select ' Z ';
Query OK, 1 row affected (0.07 sec) records:1 duplicates:0 warnings:0 mysql> 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 does not reboot, the InnoDB table inserts data that is deleted before adding 1.

However, when the MySQL service is restarted and then the data is inserted into the InnoDB table, the largest self addition in the current InnoDB is added to add 1.

Reason:

The table of the MyISAM type storage engine will have the largest ID value recorded in the data file, regardless of whether or not the maximum ID value is restarted. But the largest ID value of the InnoDB table is in memory, and if you do not restart the MySQL service, the new data will use the largest data +1 in memory. But after the reboot, the maximum value in the current table is used and then +1

Thank you for reading this article, I hope to help you, thank you for your support for this site!