程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> Mysql中Identity 詳細介紹,mysqlidentity

Mysql中Identity 詳細介紹,mysqlidentity

編輯:MySQL綜合教程

Mysql中Identity 詳細介紹,mysqlidentity


假如表中包含一列為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

感謝閱讀此文,希望能幫助到大家,謝謝大家對本站的支持!

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved