墨墨導(dǎo)讀：MySQL序列概述為了達(dá)到標(biāo)識(shí)的目的，許多應(yīng)用程序需要生成唯一編號(hào)，比如：商品編號(hào)、交易流水號(hào)等。

一、MySQL序列概述

為了達(dá)到標(biāo)識(shí)的目的，許多應(yīng)用程序需要生成唯一編號(hào)，比如：商品編號(hào)、交易流水號(hào)等。MySQL數(shù)據(jù)庫(kù)同樣能夠支持這樣的需求場(chǎng)景，AUTO_INCREMENT就是為MySQL實(shí)現(xiàn)序列的方式，它會(huì)自動(dòng)生成序列編號(hào)。但是它的使用是有要求的，比如：

每個(gè)表只能有一個(gè)列具備AUTO_INCREMENT屬性，并且為整數(shù)型
AUTO_INCREMENT列不能包含NULL值（MySQL會(huì)自動(dòng)設(shè)置為NOT NULL）
AUTO_INCREMENT列上必選要有索引，常見(jiàn)為primary key和unique index

備注：由于存儲(chǔ)引擎的不同對(duì)于序列的定義和使用存在差異，本文以innodb引擎作為講解，具體差異區(qū)別，可參考后期分享：《淺析MySQL存儲(chǔ)引擎序列屬性》。

二、場(chǎng)景演示

設(shè)置為AUTO_INCREMENT屬性后，每一次插入數(shù)據(jù)都會(huì)向前增加一位數(shù)，但是如果刪除行后，序列會(huì)怎么樣呢？

mysql> CREATE TABLE animals (    ->      id MEDIUMINT NOT NULL AUTO_INCREMENT,    ->      name CHAR(30) NOT NULL,    ->      PRIMARY KEY (id)    -> );Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO animals (name) VALUES    ->     ('dog'),('cat'),('penguin'),    ->     ('lax'),('whale'),('ostrich');Query OK, 6 rows affected (0.01 sec)Records: 6  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM animals;+----+---------+| id | name    |+----+---------+|  1 | dog     ||  2 | cat     ||  3 | penguin ||  4 | lax     ||  5 | whale   ||  6 | ostrich |+----+---------+6 rows in set (0.00 sec)

對(duì)于動(dòng)物編號(hào)來(lái)說(shuō)，序列的作用確實(shí)很好用，但是當(dāng)刪除某行數(shù)據(jù)后，序列會(huì)發(fā)生什么情況呢？

Query OK, 3 rows affected (0.02 sec)
mysql> SELECT * FROM animals;+----+---------+| id | name    |+----+---------+|  1 | dog     ||  3 | penguin ||  5 | whale   |+----+---------+3 rows in set (0.00 sec)


現(xiàn)在序列（id）發(fā)生了斷層。

Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM animals;+----+---------+| id | name    |+----+---------+|  1 | dog     ||  3 | penguin ||  5 | whale   ||  7 | Horse   |+----+---------+4 rows in set (0.00 sec)
mysql> INSERT INTO animals (name) VALUES ('Kangaroo');Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM animals;+----+----------+| id | name     |+----+----------+|  1 | dog      ||  3 | penguin  ||  5 | whale    ||  7 | Horse    ||  8 | Kangaroo |+----+----------+5 rows in set (0.00 sec)

在插入新數(shù)據(jù)后，原來(lái)被刪除的序列已經(jīng)不再重復(fù)使用了，而下一個(gè)序列為未使用的最小整數(shù)。刪除當(dāng)前行對(duì)于下一次序列的分配，沒(méi)有影響。

對(duì)于每次數(shù)據(jù)進(jìn)行插入，都會(huì)從AUTO_INCREMENT列中獲取最大值，在進(jìn)行偏移量增加。如默認(rèn)的偏移量為1。

+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 1     |+--------------------------+-------+1 row in set (0.02 sec)
mysql> show variables like 'auto_increment_offset';+-----------------------+-------+| Variable_name         | Value |+-----------------------+-------+| auto_increment_offset | 1     |+-----------------------+-------+1?row?in?set?(0.00?sec)

說(shuō)明：對(duì)于最大值獲取，不是簡(jiǎn)單使用max函數(shù)，這樣并不準(zhǔn)確，因?yàn)樵诓⑿惺聞?wù)中，可能會(huì)有其他會(huì)話(huà)進(jìn)行插入更改，因此當(dāng)前會(huì)話(huà)max值并不是準(zhǔn)確的，同時(shí)還會(huì)存在自增長(zhǎng)字段的值之間發(fā)生沖突，所以MySQL會(huì)調(diào)用LAST_INSERT_ID()，返回最新AUTO_INCREMENT最大值。

Query OK, 1 row affected (0.00 sec)
mysql> select last_insert_id();+------------------+| last_insert_id() |+------------------+|                9 |+------------------+1 row in set (0.00 sec)
mysql> INSERT INTO animals (name) VALUES ('DEFG');Query OK, 1 row affected (0.01 sec)
mysql> select * from animals where id =last_insert_id();+----+------+| id | name |+----+------+| 10 | DEFG |+----+------+1 row in set (0.00 sec)

對(duì)于并行事務(wù)，AUTO_INCREMENT 計(jì)數(shù)會(huì)怎么分配呢？

下面做個(gè)案例測(cè)試：

會(huì)話(huà)1：

mysql> set autocommit=0    -> ;Query OK, 0 rows affected (0.00 sec)
mysql> select * from animals;+----+----------+| id | name     |+----+----------+|  1 | dog      ||  3 | penguin  ||  5 | whale    ||  7 | Horse    ||  8 | Kangaroo ||  9 | ABC      || 10 | DEFG     |+----+----------+7 rows in set (0.00 sec)
mysql> INSERT INTO animals (name) VALUES ('LISAT1');Query OK, 1 row affected (0.00 sec)
mysql> select * from animals;+----+----------+| id | name     |+----+----------+|  1 | dog      ||  3 | penguin  ||  5 | whale    ||  7 | Horse    ||  8 | Kangaroo ||  9 | ABC      || 10 | DEFG     || 11 | LISAT1   |+----+----------+8 rows in set (0.01 sec)

會(huì)話(huà)2：

mysql> set autocommit=0    -> ;Query OK, 0 rows affected (0.00 sec)
mysql> select * from animals;+----+----------+| id | name     |+----+----------+|  1 | dog      ||  3 | penguin  ||  5 | whale    ||  7 | Horse    ||  8 | Kangaroo ||  9 | ABC      || 10 | DEFG     |+----+----------+7 rows in set (0.00 sec)
mysql> INSERT INTO animals (name) VALUES ('LISAT2');Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO animals (name) VALUES ('LISAT3');Query OK, 1 row affected (0.00 sec)
mysql>mysql> select * from animals;+----+----------+| id | name     |+----+----------+|  1 | dog      ||  3 | penguin  ||  5 | whale    ||  7 | Horse    ||  8 | Kangaroo ||  9 | ABC      || 10 | DEFG     || 12 | LISAT2   || 13 | LISAT3   |+----+----------+9 rows in set (0.00 sec)


會(huì)話(huà)1：

mysql> rollback;Query OK, 0 rows affected (0.00 sec)

會(huì)話(huà)2：

mysql> commit;Query OK, 0 rows affected (0.00 sec)
mysql> select * from animals;+----+----------+| id | name     |+----+----------+|  1 | dog      ||  3 | penguin  ||  5 | whale    ||  7 | Horse    ||  8 | Kangaroo ||  9 | ABC      || 10 | DEFG     || 12 | LISAT2   || 13 | LISAT3   |+----+----------+9 rows in set (0.00 sec)

可以從上面的測(cè)試看出，當(dāng)會(huì)話(huà)1持有該序列后，會(huì)對(duì)該序列占有語(yǔ)句鎖，會(huì)話(huà)2重新申請(qǐng)下一個(gè)序列，因此出現(xiàn)了序列不連續(xù)情況，這樣的目的其實(shí)也是為了避免線(xiàn)程沖突，性能優(yōu)先。

在高效使用AUTO_INCREMENT列時(shí)，有幾項(xiàng)注意事項(xiàng)：

自增序列的目的是得到一系列的正整數(shù)序列，，因此不支持非正數(shù)使用。
可以將AUTO_INCREMENT列定義為UNSIGED類(lèi)型，創(chuàng)建主鍵 UNSIGNED 和 AUTO_INCREMENT 連用表示從0開(kāi)始自增 (由0開(kāi)始自增，所以第一個(gè)自增的id為 1 ) 但可以增加的范圍為，不加 UNSIGNED 的兩倍
使用truncate table來(lái)清除某個(gè)表的內(nèi)容，可以將該表的序列重置為1開(kāi)始。