淺析 MySQL 存儲引擎序列屬性

一、MySQL序列概述

為了達到標識的目的，許多應用程式需要生成唯一編號，比如：商品編號、交易流水號等。MySQL資料庫同樣能夠支持這樣的需求場景，AUTO_INCREMENT就是為MySQL實現序列的方式，它會自動生成序列編號。但是它的使用是有要求的，比如：

• 每個表只能有一個列具備AUTO_INCREMENT屬性，並且為整數型
• AUTO_INCREMENT列不能包含NULL值（MySQL會自動設置為NOT NULL）
• AUTO_INCREMENT列上必選要有索引，常見為primary key和unique index

二、MyISAM存儲引擎

MyISAM存儲引擎是支持AUTO_INCREMENT屬性的，它有如下幾個特點：

• 單調遞增，每次序列使用完後，下一個序列將從當前最大序列開始。每次序列值都會存在數據文件中，因此當服務重啟後，依舊可以進行序列遞增。

備註：兩種情況比較特殊，第一種是使用truncate 後，序列將重新開始。第二種，如果是使用複合索引產生的多個序列，那麼這些序列將會被重用。

mysql> CREATE TABLE myisamdemo1 ( -> id int unsigned not null auto_increment, -> city varchar(10) NOT NULL, -> name varchar(30) NOT NULL, -> primary key(city,id)) ENGINE = MyISAM;Query OK, 0 rows affected (0.01 sec)mysql> INSERT INTO myisamdemo1 (city,name) VALUES (&39;,&39;);Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO myisamdemo1 (city,name) VALUES (&39;,&39;);Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO myisamdemo1 (city,name) VALUES (&39;,&39;);Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO myisamdemo1 (city,name) VALUES (&39;,&39;);Query OK, 1 row affected (0.00 sec)mysql> select * from myisamdemo1;+----+----------+---------+| id | city | name |+----+----------+---------+| 1 | GuiYang | albert || 2 | GuiYang | tony || 1 | ChenDu | jake || 1 | ShangHai | Jessica |+----+----------+---------+4 rows in set (0.00 sec)

如果使用了這種複合索引來創建多個序列，那麼從各個序列頂端的值，也是可以進行復用的。

Query OK, 1 row affected (0.00 sec)mysql> select * from myisamdemo1;+----+----------+---------+| id | city | name |+----+----------+---------+| 1 | GuiYang | albert || 1 | ChenDu | jake || 1 | ShangHai | Jessica |+----+----------+---------+3 rows in set (0.00 sec)mysql> INSERT INTO myisamdemo1 (city,name) VALUES (&39;,&39;);Query OK, 1 row affected (0.00 sec)mysql> select * from myisamdemo1;+----+----------+---------+| id | city | name |+----+----------+---------+| 1 | GuiYang | albert || 1 | ChenDu | jake || 1 | ShangHai | Jessica || 1 | XiAn | 368 |+----+----------+---------+4 rows in set (0.00 sec)mysql> INSERT INTO myisamdemo1 (city,name) VALUES (&39;,&39;);Query OK, 1 row affected (0.00 sec)mysql> select * from myisamdemo1;+----+----------+---------+| id | city | name |+----+----------+---------+| 1 | GuiYang | albert || 2 | GuiYang | Kevin || 1 | ChenDu | jake || 1 | ShangHai | Jessica || 1 | XiAn | 368 |+----+----------+---------+5 rows in set (0.00 sec)

• 默認序列從1開始，當然也可以在建表中指定起始序列號

-> id int unsigned not null auto_increment, -> nname varchar(30) NOT NULL, -> primary key(id)) ENGINE = MyISAM auto_increment=100;Query OK, 0 rows affected (0.02 sec)mysql> INSERT INTO myisamdemo (nname) VALUES (&39;);Query OK, 1 row affected (0.00 sec)mysql> select * from myisamdemo;+-----+--------+| id | nname |+-----+--------+| 100 | Albert |+-----+--------+1 row in set (0.00 sec)mysql> INSERT INTO myisamdemo (nname) VALUES (&39;);Query OK, 1 row affected (0.01 sec)mysql> select * from myisamdemo;+-----+--------+| id | nname |+-----+--------+| 100 | Albert || 101 | tony |+-----+--------+2 rows in set (0.00 sec)

• 可以通過alter table來修改已存在的序列號。
  可以調整下一個序列的起始值，比如：

mysql> select * from myisamdemo;+-----+--------+| id | nname |+-----+--------+| 100 | Albert || 101 | tony |+-----+--------+2 rows in set (0.00 sec)mysql> alter table myisamdemo auto_increment=200;Query OK, 2 rows affected (0.01 sec)Records: 2 Duplicates: 0 Warnings: 0mysql> INSERT INTO myisamdemo (nname) VALUES (&39;);Query OK, 1 row affected (0.00 sec)mysql> select * from myisamdemo;+-----+--------+| id | nname |+-----+--------+| 100 | Albert || 101 | tony || 200 | nike |+-----+--------+3 rows in set (0.00 sec)

還可以重用頂端的序列，比如當前最大的序列為202，刪除後通過指定起始值，是可以重新使用該序列。但是需要注意的是，不能在序列包含裡面，比如100至201之間。

mysql> select * from myisamdemo;+-----+--------+| id | nname |+-----+--------+| 100 | Albert || 200 | nike || 201 | jake || 202 | harry |+-----+--------+4 rows in set (0.00 sec)mysql> delete from myisamdemo where id=202;Query OK, 1 row affected (0.00 sec)mysql> select * from myisamdemo;+-----+--------+| id | nname |+-----+--------+| 100 | Albert || 200 | nike || 201 | jake |+-----+--------+3 rows in set (0.00 sec)mysql> alter table myisamdemo auto_increment=201;Query OK, 3 rows affected (0.01 sec)Records: 3 Duplicates: 0 Warnings: 0mysql> INSERT INTO myisamdemo (nname) VALUES (&39;);Query OK, 1 row affected (0.00 sec)mysql> select * from myisamdemo;+-----+--------+| id | nname |+-----+--------+| 100 | Albert || 200 | nike || 201 | jake || 202 | sunny |+-----+--------+4 rows in set (0.00 sec)

三、MEMORY存儲引擎

MEMORY存儲引擎同樣是支持AUTO_INCREMENT屬性的：

• 默認序列從1開始，當然也可以在建表中指定起始序列號

mysql> CREATE TABLE memorydemo ( -> id int unsigned not null auto_increment, -> name varchar(30) NOT NULL, -> primary key(id)) ENGINE = memory auto_increment=100;Query OK, 0 rows affected (0.00 sec)mysql> INSERT INTO memorydemo (name) VALUES (&39;);Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO memorydemo (name) VALUES (&39;);Query OK, 1 row affected (0.00 sec)mysql> select * from memorydemo;+-----+-------+| id | name |+-----+-------+| 100 | tony || 101 | Tommy |+-----+-------+2 rows in set (0.00 sec)

• 可以通過alter table 調整調整初始序列

mysql> select * from memorydemo;+-----+-------+| id | name |+-----+-------+| 100 | tony || 101 | Tommy |+-----+-------+2 rows in set (0.00 sec)mysql> alter table memorydemo auto_increment=400;Query OK, 2 rows affected (0.01 sec)Records: 2 Duplicates: 0 Warnings: 0mysql> mysql> INSERT INTO memorydemo (name) VALUES (&39;);Query OK, 1 row affected (0.00 sec)mysql> select * from memorydemo;+-----+---------+| id | name |+-----+---------+| 100 | tony || 101 | Tommy || 400 | Jessica |+-----+---------+3 rows in set (0.00 sec)

• 頂端序列被刪除後，不能被重用

+-----+---------+| id | name |+-----+---------+| 100 | tony || 101 | Tommy || 400 | Jessica |+-----+---------+3 rows in set (0.00 sec)mysql> delete from memorydemo where id=400;Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO memorydemo (name) VALUES (&39;);Query OK, 1 row affected (0.00 sec)mysql> select * from memorydemo;+-----+-------+| id | name |+-----+-------+| 100 | tony || 101 | Tommy || 401 | marry |+-----+-------+3 rows in set (0.00 sec)

• 不能使用複合索引生成多個序列，因為有且只能定義一個主鍵列

四、INNODB存儲引擎

...

✨ 接下來內容請訪問原文（https://www.modb.pro/db/29406?YYF）進行查看～

更多資料庫相關內容，可訪問墨天輪（https://www.modb.pro/?YYF）進行瀏覽。