Mysql Identity details, mysqlidentity

Mysql Identity details, mysqlidentity

Assume that the table contains a column named auto_increment,

If it is a Myisam engine, the maximum ID + 1 deleted last time will still be used after the next insert, regardless of whether or not Mysql is restarted.

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: 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 name 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[2@a 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) 2000, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be 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[2@a data]$ sudo service mysql restartShutting down MySQL... SUCCESS!Starting MySQL.. SUCCESS![2@a 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) 2000, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql> use 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) 

We can see that when the mysql database is not restarted, the newly inserted data in the innodb table will be the previously deleted data plus 1.

However, when the Mysql service is restarted and data is inserted into the InnodB auto-incrementing table, the maximum auto-incrementing column in the current Innodb table is used to add 1.

Cause:

The maximum ID value of a table in the Myisam storage engine is recorded in a data file. No matter whether the table is restarted or not, the maximum ID value is not lost. However, the maximum ID value of the InnoDB table exists in the memory. If the Mysql service is not restarted, the maximum data in the memory will be used when new data is added + 1. however, after the restart, the maximum value in the current table will be used plus 1

Thank you for reading this article and hope to help you. Thank you for your support for this site!