MySQL AUTO_INCREMENT自增长

MySQL 中的序列是根据特定需求按升序（1、2、3 等）生成的整数排列。数据库中使用序列来生成唯一编号。许多应用程序要求表的每一行都包含一个不同的值，例如 student_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 属性，并且该列也作为 PRIMARY KEY。

执行以下SQL以创建表：

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 AUTO_INCREMENT 原理是什么？

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 AUTO_INCREMENT的使用。

在表中插入两条新记录。

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

接下来，我们将使用以下SQL删除 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 发出错误：Duplicate entry '2' for key 'insects.PRIMARY' column。让我们修复它

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

看下面的结果：