MySQL序列（自動增長）

在本教學中，我們將向您展示如何使用MySQL序列為表的ID列自動生成唯一編號。

建立MySQL序列

在MySQL中，序列是以升序生成的整數列表，即1,2,3 ...許多應用程式需要序列來生成主要用於識別的唯一數位，例如：CRM中的客戶ID，HR中的員工編號，伺服器管理系統的裝置編號等。

要自動在MySQL中建立序列，可以在列上設定AUTO_INCREMENT屬性，這通常是主鍵列。

使用AUTO_INCREMENT屬性時，將應用以下規則：

每個表只有一個AUTO_INCREMENT列，其資料型別通常為整數。
必須對AUTO_INCREMENT列進行索引，它可以是PRIMARY KEY或UNIQUE索引。
AUTO_INCREMENT列必須具有NOT NULL約束。當您為列設定AUTO_INCREMENT屬性時，MySQL會自動將NOT NULL約束隱式新增到列中。

建立MySQL序列範例

以下語句建立一個名為employees的表，其emp_no列為AUTO_INCREMENT列：

USE testdb;
CREATE TABLE employees(
    emp_no INT(4) AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name  VARCHAR(50)
);

MySQL序列如何工作

AUTO_INCREMENT列具有以下屬性：

AUTO_INCREMENT列的起始值為1，當您向列中插入NULL值或在INSERT語句中省略其值時，它將增加1。
要獲取最後生成的序列號，請使用LAST_INSERT_ID()函式。我們經常要後續語句中使用最後一個插入ID，例如將資料插入到表中。最後生成的序列在對談中是唯一的。換句話說，如果另一個連線生成序列號，從連線中可以使用LAST_INSERT_ID()函式獲取它。
如果將新行插入到表中並指定序列列的值，如果序列號不存在於列中，則MySQL將插入序列號，如果序列號已存在，則會發出錯誤。如果插入大於下一個序列號的新值，MySQL將使用新值作為起始序列號，並生成大於當前值的唯一序列號。這會在序列中產生一段空白(不連續)。
如果使用UPDATE語句將AUTO_INCREMENT列中的值更新為已存在的值，如果該列具有唯一索引，則MySQL將發出重複鍵錯誤。如果將AUTO_INCREMENT列更新為大於列中現有值的值，MySQL將使用最後一個插入序列號加1的值作為下一行列號值。例如，如果最後一個插入序列號為3，然後又將其更新為10，那麼新插入行的序列號不是11，而是4。
如果使用DELETE語句刪除最後插入的行，MySQL可能會也可能不會根據表的儲存引擎重複使用已刪除的序列號。如果您刪除一行，則MyISAM表不會重複使用已刪除的序列號，例如，如果刪除表中的最後一個插入ID為10，則MySQL仍會為新行生成11個下一個序列號。與MyISAM表類似，InnoDB表在行被刪除時不重複使用序列號。

在列上設定AUTO_INCREMENT屬性後，可以以各種方式重置自動增量值，例如使用ALTER TABLE語句。

我們來看一下一些例子來更好地了解MySQL序列。

第一步，在employees表中插入兩行：

INSERT INTO employees(first_name,last_name)
VALUES('John','Doe'),
      ('Mary','Jane');

第二步，從employees表中查詢選擇資料：

mysql> SELECT * FROM employees;
+--------+------------+-----------+
| emp_no | first_name | last_name |
+--------+------------+-----------+
|      1 | John       | Doe       |
|      2 | Mary       | Jane      |
+--------+------------+-----------+
2 rows in set

第三步，刪除emp_no為2的第二個員工資訊：

mysql> DELETE FROM employees
WHERE emp_no = 2;
Query OK, 1 row affected

mysql> SELECT * FROM employees;
+--------+------------+-----------+
| emp_no | first_name | last_name |
+--------+------------+-----------+
|      1 | John       | Doe       |
+--------+------------+-----------+
1 row in set

第四步，插入新員工，並查詢最後一位員工資訊(emp_no)：


mysql> INSERT INTO employees(first_name,last_name)
VALUES('Jack','Lee');
Query OK, 1 row affected

mysql> SELECT * FROM employees;
+--------+------------+-----------+
| emp_no | first_name | last_name |
+--------+------------+-----------+
|      1 | John       | Doe       |
|      3 | Jack       | Lee       |
+--------+------------+-----------+
2 rows in set

因為employees表的儲存引擎是InnoDB，它不會重複使用已刪除的序列號。新行使用emp_no的值是3。

第五步，將emp_no = 3已存在新員工更新為emp_no = 1：

UPDATE employees
SET first_name = 'Joe',
    emp_no = 1
WHERE emp_no = 3;

上面語句執行時，MySQL發出主鍵重複條目的錯誤。如何來解決它？

UPDATE employees
SET first_name = 'Joe',
    emp_no = 10
WHERE emp_no = 3;

執行結果如下 -

mysql> UPDATE employees
SET first_name = 'Joe',
    emp_no = 10
WHERE emp_no = 3;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM employees;
+--------+------------+-----------+
| emp_no | first_name | last_name |
+--------+------------+-----------+
|      1 | John       | Doe       |
|     10 | Joe        | Lee       |
+--------+------------+-----------+
2 rows in set

上面步驟中，將序列號更新為10。

第六，插入新員工資料 -

mysql> INSERT INTO employees(first_name,last_name)
VALUES('Wang','Lee');
Query OK, 1 row affected

mysql> SELECT * FROM employees;
+--------+------------+-----------+
| emp_no | first_name | last_name |
+--------+------------+-----------+
|      1 | John       | Doe       |
|      4 | Wang       | Lee       |
|     10 | Joe        | Lee       |
+--------+------------+-----------+

最後插入的下一個序列號是4，因此，MySQL使用數位是4作為新行序列值，而不是11。

在本教學中，您已經學習了如何使用MySQL序列為主鍵列生成唯一的編號，方法是為列設定AUTO_INCREMENT屬性。