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

Mysql中Identity 具體引見

編輯:MySQL綜合教程

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

感激浏覽此文,願望能贊助到年夜家,感謝年夜家對本站的支撐!

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