如何使用 AUTO_INCRMENT 属性创建 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列：

CREATE TABLE employees (
    emp_no INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);Code language: SQL (Structured Query Language) (sql)

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 将使用最后一个插入序列号的下一个数字作为下一行。例如，如果最后一个插入序列号为 3，则将其更新为 10，则新行的序列号为 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');Code language: SQL (Structured Query Language) (sql)

其次，从employees表中选择数据：

SELECT * FROM employees;Code language: SQL (Structured Query Language) (sql)

第三，删除emp_no为2的第二个员工：

DELETE FROM employees 
WHERE emp_no = 2;Code language: SQL (Structured Query Language) (sql)

四、插入新员工：

INSERT INTO employees(first_name,last_name)
VALUES('Jack','Lee');Code language: SQL (Structured Query Language) (sql)

由于employees表的存储引擎是InnoDB，因此不会复用已删除的序列号。新行的emp_no为 3。

第五，将emp_no 3 的现有员工更新为 1：

UPDATE employees 
SET 
    first_name = 'Joe',
    emp_no = 1
WHERE
    emp_no = 3;Code language: SQL (Structured Query Language) (sql)

MySQL 发出主键重复条目错误。让我们解决它。

UPDATE employees 
SET 
    first_name = 'Joe',
    emp_no = 10
WHERE
    emp_no = 3;Code language: SQL (Structured Query Language) (sql)

第六，将序列号更新为10后插入新员工：

INSERT INTO employees(first_name,last_name)
VALUES('Wang','Lee');Code language: SQL (Structured Query Language) (sql)

最后一次插入的下一个序列号是数字 4，因此，MySQL 使用数字 4 作为新行而不是 11。

在本教程中，您学习了如何使用 MySQL 序列通过向列分配AUTO_INCREMENT属性来为主键列生成唯一编号。

本教程有帮助吗？