MySQL自增長主鍵探究

標籤：

MySQL自己主動增長使用的keyword是 AUTO_INCREMENT; 由於屬於 DDL。所以不區分大寫和小寫. 使用的列,必須被定義為 key， 比方主鍵,唯一鍵等。

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

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

client是安裝 Windows版本號碼 MariaDB時附帶安裝的 HeidiSQL .

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

建立測試表

使用client串連到server, 使用者為 root,password也是 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;

client1的操作

使用新的client1串連到server, 使用者為 root,password也是 root 如:

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

切換 database:

USE `test`;

然後,在client1之中, 開啟事務, 插入一些資料, 可是不提交.

# 在client1中運行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數。由於我們沒有提交, 所以這個結果僅僅能在client1中看見。

client2的操作

使用新的client2串連到server, 使用者為 root,password也是 root 如:

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

切換 database:

USE `test`;

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

# 在client2中運行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數。由於我們沒有提交, 所以這個結果僅僅能在client2中看見。

這裡我們能夠看到,自增的主鍵是全域唯一的,假設有交易回復,那麼已經自增的部分,是不會受影響的。

多個事務之間的自增主鍵也不會互相影響, 能保證唯一,但不能保證終於的記錄是連續的。

注意

通過client1和client2的操作,能夠發現沒提交的事務操作其它client是不能看到的。

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

假設事務的隔離等級是 READ COMMITtED , 僅僅能看到提交成功的記錄。

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

client3的操作

使用新的client3串連到server, 使用者為 root,password也是 root 如:

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

切換 database:

USE `test`;

然後,在client3之中, 先使用查詢語句:

SELECT * FROM `test`.`test_auto`;

能夠看到, 一條資料也沒有,由於還沒有資料被提交。

client1的操作-續

回到client1的表單, 執行查詢語句:

SELECT * FROM `test`.`test_auto`;

能夠看到還是原先的6條記錄。

接著提交事務

commit;

client2的操作-續

回到client2的表單, 執行查詢語句:

SELECT * FROM `test`.`test_auto`;

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

rollback;

再執行查詢語句:

SELECT * FROM `test`.`test_auto`;

能夠看到6條記錄(ID為1-6),為什麼是6條? 由於復原時本次事務就結束,然後也不讀取快照版本號碼,而是讀取全部可見的資料,即client1提交的資料。

其它操作

接著在client1中看到的也是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日

MySQL自增長主鍵探究