详解MySQL自增序列|极客笔记

详解MySQL自增序列

1. 什么是自增序列

在MySQL中，自增序列（即Auto Increment）是一种特殊的列属性，用于在每次插入新数据时自动为该列生成一个唯一的递增值。这种自动递增的功能可以极大地简化数据库操作，并保证数据的唯一性。

自增序列可以应用于整数类型的列，例如INT、BIGINT等。在创建表时，我们可以为某一列指定自增序列属性，以便在每次插入新记录时自动生成递增值。

2. 如何创建自增序列列

在MySQL中，通过使用AUTO_INCREMENT关键字来创建自增序列列。以下示例演示了如何创建一张具有自增序列列的表：

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

在上述示例中，我们创建了一张名为students的表，该表包含三列：id、name和age。其中，id列被指定为自增序列列，AUTO_INCREMENT关键字告诉MySQL在每次插入新记录时自动生成递增值。

3. 使用自增序列列插入数据

在表创建完成后，我们可以通过以下方式向具有自增序列列的表插入数据：

INSERT INTO students (name, age) VALUES ('Alice', 20);
INSERT INTO students (name, age) VALUES ('Bob', 22);

在以上示例中，我们向students表中插入了两条数据。由于id列是自增序列列，所以我们并没有指定具体的id值。在执行插入操作时，MySQL会自动为id列生成唯一的递增值。

4. 获取自增序列列的值

在插入数据后，我们可以通过以下方式获取自增序列列的值：

SELECT LAST_INSERT_ID();

LAST_INSERT_ID()函数用于获取最后插入行的id值。此函数只能用于存储自增序列列的值，其他列将返回NULL。

5. 自增序列列的主键约束

自增序列列通常被用作表的主键，以保证每条记录都有唯一的标识。在创建表时，我们可以将自增序列列指定为主键。以下示例演示了如何创建包含自增序列列的主键：

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

在上述示例中，我们将id列指定为主键，并添加了AUTO_INCREMENT关键字。

6. 自增序列列的注意事项

在使用自增序列列时，有以下几个需要注意的事项：

6.1. 自增序列列的初始值与递增步长

MySQL自增序列列的默认初始值为1，递增步长为1。如果我们希望指定初始值或递增步长，可以使用AUTO_INCREMENT关键字的参数。以下示例演示了如何指定自增序列列的初始值和递增步长：

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT
) AUTO_INCREMENT = 100; -- 初始值为100

ALTER TABLE students AUTO_INCREMENT = 1000; -- 初始值为1000, 递增步长为1

6.2. 自增序列列的重置

当使用自增序列列插入大量数据后，可能需要重置自增序列列的当前值。这可以通过以下方式实现：

ALTER TABLE students AUTO_INCREMENT = 1; -- 重置自增序列列的当前值为1

需要注意的是，重置自增序列列的当前值并不会自动删除已存在的记录。重置后，下一次插入数据时，自增序列列将从指定的初始值开始递增。

7. 示例

以下示例演示了在MySQL中使用自增序列列的完整流程：

-- 创建表
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

-- 插入数据
INSERT INTO students (name, age) VALUES ('Alice', 20);
INSERT INTO students (name, age) VALUES ('Bob', 22);

-- 获取自增序列列的值
SELECT LAST_INSERT_ID();

-- 查询数据
SELECT * FROM students;

-- 表结构
SHOW CREATE TABLE students;

-- 重置自增序列列的当前值
ALTER TABLE students AUTO_INCREMENT = 1;

运行以上示例，可以得到如下结果：

+-------------------+
| LAST_INSERT_ID()  |
+-------------------+
|                 1 |
+-------------------+

+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | Alice |  20 |
|  2 | Bob   |  22 |
+----+-------+-----+

CREATE TABLE `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

ALTER TABLE students AUTO_INCREMENT = 1;

以上结果展示了自增序列列的使用过程，包括表创建、数据插入、自增值获取、数据查询、表结构展示以及重置自增序列列的操作。