11.4 序列

在本教程中，我们将向您展示如何使用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属性。