MySQL自增長主鍵探究，MySQL增長主鍵探究

MySQL自增長主鍵探究，MySQL增長主鍵探究

MySQL自動成長使用的關鍵字是 AUTO_INCREMENT; 因為屬於 DDL，所以不區分大小寫. 使用的列,必須被定義為 key， 比如主鍵,唯一鍵等。

本文中使用的資料庫是 MariaDB 5.5.5

預設事務隔離界別是 REPEATABLE-READ

用戶端是安裝 Windows版本 MariaDB時附帶安裝的 HeidiSQL .

社區免費版的下載頁面為: https://downloads.mariadb.org/mariadb/

建立測試表

使用用戶端串連到伺服器, 使用者為 root,密碼也是 root 如:

mysql -h localhost -P 3306 -u root -proot

先選擇切換 database:

USE `test`;

建立測試表:

DROP TABLE IF EXISTS `test_auto`;CREATE TABLE `test_auto` (    `id` INT NOT NULL AUTO_INCREMENT,    PRIMARY KEY (`id`))COMMENT='測試自動成長'COLLATE='utf8_general_ci'ENGINE=InnoDB;

用戶端1的操作

使用新的用戶端1串連到伺服器, 使用者為 root,密碼也是 root 如:

mysql -h localhost -P 3306 -u root -proot

切換 database:

USE `test`;

然後,在用戶端1之中, 開啟事務, 插入一些資料, 但是不提交.

# 在用戶端1中執行begin ;insert into test_auto() values();insert into test_auto() values();insert into test_auto() values();insert into test_auto() values();insert into test_auto() values();insert into test_auto() values();

此時,可以使用查詢語句

SELECT * FROM `test`.`test_auto`;

可以看到, 得到了6條資料, id 是 1-6, 對應著我們插入資料的SQL數。因為我們沒有提交, 所以這個結果只能在用戶端1中看見。

用戶端2的操作

使用新的用戶端2串連到伺服器, 使用者為 root,密碼也是 root 如:

mysql -h localhost -P 3306 -u root -proot

切換 database:

USE `test`;

然後,在用戶端2之中, 開啟事務, 插入一些資料, 也不提交.

# 在用戶端2中執行begin ;insert into test_auto() values();insert into test_auto() values();insert into test_auto() values();insert into test_auto() values();insert into test_auto() values();

此時,可以使用查詢語句

SELECT * FROM `test`.`test_auto`;

可以看到, 得到了5條資料, id 是 7-11, 對應著我們插入資料的SQL數。因為我們沒有提交, 所以這個結果只能在用戶端2中看見。

這裡我們可以看到,自增的主鍵是全域唯一的,如果有交易回復,那麼已經自增的部分,是不會受影響的。多個事務之間的自增主鍵也不會互相影響, 能保證唯一,但不能保證最終的記錄是連續的。

注意

通過用戶端1和用戶端2的操作,可以發現沒提交的事務操作其他用戶端是不能看到的。

這是 REPEATABLE-READ 交易隔離等級, 在開啟事務後, 還沒提交前, 用戶端看到的記錄, 是 事務開啟那一刻的快照, 加上本次會話中執行操作的結果。保證在事務執行過程中,不受其他會話所提交事務的影響。

如果事務的隔離等級是 READ COMMITtED , 只能看到提交成功的記錄。

查詢交易隔離等級: select @@tx_isolation

用戶端3的操作

使用新的用戶端3串連到伺服器, 使用者為 root,密碼也是 root 如:

mysql -h localhost -P 3306 -u root -proot

切換 database:

USE `test`;

然後,在用戶端3之中, 先使用查詢語句:

SELECT * FROM `test`.`test_auto`;

可以看到, 一條資料也沒有,因為還沒有資料被提交。

用戶端1的操作-續

回到用戶端1的視窗, 執行查詢語句:

SELECT * FROM `test`.`test_auto`;

可以看到還是原先的6條記錄。接著提交事務

commit;

用戶端2的操作-續

回到用戶端2的視窗, 執行查詢語句:

SELECT * FROM `test`.`test_auto`;

可以看到還是原先的5條記錄。接著復原事務

rollback;

再執行查詢語句:

SELECT * FROM `test`.`test_auto`;

可以看到6條記錄(ID為1-6),為什麼是6條? 因為復原時本次事務就結束,然後也不讀取快照版本,而是讀取所有可見的資料,即用戶端1提交的資料。

其他動作

接著在用戶端1中看到的也是6條記錄.

也可以繼續執行幾次插入或者事務操作,中途查詢資料,並分析結果。

小結

MySQL的自動成長列, 保證了不重複,不保證中間不跳號(當然,不跳號只有某些特殊業務有需求)。特別是在事務執行環境裡執行時, 為了不影響邏輯與效能，也只能採用這種處理方式。

GitHub版本: https://github.com/cncounter/translation/blob/master/tiemao_2015/16_MySQL_AUTO_INCREMENT/MySQL_AUTO_INCREMENT.md

作者: 鐵錨 http://blog.csdn.net/renfufei

日期: 2015年06月01日

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。