MySQL 序列

MySQL序列是在特定需求下生成的一组按升序排列的整数（1、2、3等等）。序列在数据库中用于生成唯一的编号。许多应用程序要求表的每一行包含一个不同的值，例如学生_table中的学生编号、HR中的员工编号、CRM中的客户ID等等。为了满足这种安排，我们使用序列提供了一种简单的方法来生成它们。

MySQL并不提供任何内置函数来创建表行或列的序列。但是我们可以通过SQL查询来生成它。在本文中，我们将介绍如何使用SQL查询在MySQL中创建序列。

使用AUTO_INCREMENT创建序列

在MySQL中创建序列的最简单方法是在表创建过程中将列定义为AUTO_INCREMENT，这个列应该是一个主键列。

当我们使用AUTO_INCREMENT属性时，应考虑以下规则：

• 我们在每个表中只能创建一个AUTO_INCREMENT列，该列的数据类型为整数。
• AUTO_INCREMENT列还应具有PRIMARY或UNIQUE KEY索引。
• AUTO_INCREMENT列必须包含 NOT NULL 但是，当我们将列设置为AUTO_INCREMENT属性时，MySQL会自动隐式地将NOT NULL约束添加到列中。

示例：

为了帮助理解，下面给出一个示例。首先，我们需要创建一个新的表，并确保有一列具有AUTO_INCREMENT属性，并且这列还是主键。

执行以下查询来创建表：

mysql> CREATE TABLE Insects (
   Id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (id),
   Name VARCHAR(30) NOT NULL, 
   Type VARCHAR(30) NOT NULL,
   Origin VARCHAR(30) NOT NULL 
);

接下来，我们将向此表中插入几行数据，无需为每一行提供id，因为MySQL会自动递增。

mysql> INSERT INTO Insects (Name, Type, Origin) VALUES
    ('Cockroach', 'Crawling', 'Kitchen'),
    ('Mosquito', 'Flying', 'Driveway'),
    ('Spider' ,'Crawling', 'Court yard'),
    ('Grasshopper', 'Flying', 'Front yard');

现在执行 SELECT语句 来验证记录：

mysql> SELECT * FROM Insects;

我们可以在下面的图片中看到结果。

在上面的图像中，我们使用PRIMARY KEY和AUTO_INCREMENT选项定义了Id列，该选项自动增加了该列的值，并始终存储唯一的值。

当我们执行INSERT查询时，我们不需要为Id列提供值，但是MySQL会自动生成一个序列。

MySQL序列的工作原理是什么？

MySQL中的AUTO_INCREMENT列包含以下属性：

• AUTO_INCREMENT列的起始值为1。当我们在INSERT语句中省略其值或插入NULL时，该列总是按1递增。
• 我们可以使用LAST_INSERT_ID()函数来获取最后生成的序列号。但是，我们也可以将最后的插入ID用于后续语句中，这些语句应在会话中是唯一的。
• 如果我们将新行插入到表中时，还指定了序列列的值，那么MySQL首先会检查指定的值是否已经存在。如果不存在，它将在列中插入序列号；否则，会发出错误。另外，如果我们插入的值大于下一个序列号，MySQL将使用它作为起始序列号。现在，MySQL将根据当前序列号生成下一个序列值。需要注意的是，这将在我们的序列中创建间隔。
• 如果我们使用UPDATE语句更新已存在的AUTO_INCREMENT列的值，如果该列只存储不同的值，MySQL将发出重复键错误。如果我们将AUTO_INCREMENT列更新为大于现有值的值，MySQL将为下一行插入下一个序列号。例如，AUTO_INCREMENT列的最后一个序列值为3，我们想将其更新为10，那么下一行的序列号应为4。
• 如果我们使用DELETE语句删除最后插入的行，MySQL不一定会再次使用已删除的序列号，因为这取决于表的存储引擎。例如，如果我们使用MyISAM表并删除最后一个插入的Id为5，MySQL仍然会为新行插入下一个序列号6。

让我们看一些更多的示例，以更好地理解MySQL序列的使用。

向表中插入两条新记录。

mysql> INSERT INTO Insects (Id, Name, Type, Origin) VALUES
(6, 'Bee', 'Flying', 'Court yard'),
(7, 'Ant', 'Crawling', 'Front yard');

并执行SELECT语句以验证输出：

接下来，我们将使用以下查询语句 删除id为6的昆虫 ：

mysql> DELETE FROM Insects WHERE Id = 6;

再次，我们将使用以下语句将新行插入到表中：

mysql> INSERT INTO Insects (Name, Type, Origin) VALUES
('Cricket', 'Crawling', 'Front yard');

我们将再次执行SELECT语句以查看输出结果:

在上面的图片中，我们可以看到MySQL不会重复使用已删除的序列号。这是因为Insects表的存储引擎是 InnoDB 。因此，插入查询将在Insects表中添加新的序列号为8的记录。

现在，我们将更新一个现有的昆虫，其 Id为3，更新为Id=2 ：

mysql> UPDATE Insects SET Name = 'Bee', Id = 2 WHERE Id = 3;

MySQL发生错误： key ‘insects.PRIMARY’列中的重复条目’2’ 。让我们解决它

mysql> UPDATE Insects SET Name = 'Bee', Id = 5 WHERE Id = 3;

看下面的图片。