Mysql中Identity 具體引見。本站提示廣大學習愛好者：（Mysql中Identity 具體引見）文章只能為提供參考，不一定能成為您想要的結果。以下是Mysql中Identity 具體引見正文
假設表中包括一列為auto_increment，
假如是Myisam類型的引擎，那末在刪除最新一筆數據，不管能否重啟Mysql，下一次拔出以後依然會應用前次刪除的最年夜ID+1.
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> 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: 0 mysql> 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)
上面是對Innodb表的測試。
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> 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: 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 Welcome 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) 2000, 2014, 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 wison 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) 2000, 2014, 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 wison 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)
可以看到在mysql數據庫沒有重啟時，innodb的表新拔出數據會是之前被刪除的數據再加1.
然則當Mysql辦事被重啟後，再向InnodB的自增表內外拔出數據，那末會應用以後Innodb內外的最年夜的自增列再加1.
緣由：
Myisam類型存儲引擎的表將最年夜的ID值是記載到數據文件中，不論能否重啟最年夜的ID值都不會喪失。然則InnoDB表的最年夜的ID值是存在內存中的，若不重啟Mysql辦事，新參加數據會應用內存中最年夜的數據+1.然則重啟以後，會應用以後表中最年夜的值再+1
感激浏覽此文，願望能贊助到年夜家，感謝年夜家對本站的支撐！
