【重学 MySQL】六十五、auto_increment 的使用

【重学 MySQL】六十五、auto_increment 的使用

在 MySQL 中，AUTO_INCREMENT 属性用于在一个表中生成唯一的数字序列，通常用于主键（Primary Key）字段。它确保每次插入新记录时，该字段的值会自动递增，从而避免手动设置重复值。

创建表时使用 AUTO_INCREMENT

在创建表时，可以通过以下方式定义 AUTO_INCREMENT 字段：

CREATE TABLE example (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (id)
);

在上述示例中，id 字段被设置为 AUTO_INCREMENT，并且它是主键。每次插入新记录时，id 字段的值会自动递增。

特点和要求

• 一个表最多只能有一个自增长列
• 当需要产生唯一标识符或顺序值时，可设置自增长
• 自增长列约束的列必须是键列（主键列，唯一键列）
• 自增约束的列的数据类型必须是整数类型
• 如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接赋值为具体值。

错误演示：

create table employee(
    eid int auto_increment,
    ename varchar(20)
);
# ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key   

create table employee(
    eid int primary key,
    ename varchar(20) unique key auto_increment
);
# ERROR 1063 (42000): Incorrect column specifier for column 'ename'  因为ename不是整数类型

插入数据

插入数据时，无需为 AUTO_INCREMENT 字段提供值，MySQL 会自动处理：

INSERT INTO example (name) VALUES ('Alice');
INSERT INTO example (name) VALUES ('Bob');

插入后的表内容：

查看当前 AUTO_INCREMENT 值

可以使用 SHOW TABLE STATUS 命令来查看表的当前 AUTO_INCREMENT 值：

SHOW TABLE STATUS LIKE 'example';

在结果中，Auto_increment 列显示了下一个 AUTO_INCREMENT 值。

设置初始 AUTO_INCREMENT 值

在创建表时，可以通过 ALTER TABLE 语句设置或更改 AUTO_INCREMENT 的起始值：

ALTER TABLE example AUTO_INCREMENT = 1000;

设置后，下一次插入的记录的 id 将从 1000 开始。

重置 AUTO_INCREMENT 值

要重置 AUTO_INCREMENT 值，可以将其设置为比当前最大值更大的值，或者通过删除所有记录并重新设置：

TRUNCATE TABLE example;  -- 删除所有记录，并重置 AUTO_INCREMENT 值

或者：

DELETE FROM example;
ALTER TABLE example AUTO_INCREMENT = 1;  -- 重置为 1 或其他值

注意事项

1. 唯一性：AUTO_INCREMENT 字段通常用于主键，以确保其唯一性。
2. 数据类型：AUTO_INCREMENT 字段通常是整数类型（如 INT、BIGINT）。
3. 删除和插入：删除记录不会重置 AUTO_INCREMENT 值，除非使用 TRUNCATE TABLE。
4. 事务：如果插入操作在一个事务中被回滚，AUTO_INCREMENT 值仍然会增加。

示例：组合主键和 AUTO_INCREMENT

有时，你可能需要在组合主键中使用 AUTO_INCREMENT 字段，但这通常不常见。更多情况下，AUTO_INCREMENT 用于单一主键字段。

CREATE TABLE composite_key_example (
    part1 INT NOT NULL,
    part2 INT NOT NULL,
    id INT NOT NULL AUTO_INCREMENT,
    value VARCHAR(100),
    PRIMARY KEY (part1, part2, id)
);

在这个例子中，part1 和 part2 与 id 一起构成组合主键，而 id 仍然使用 AUTO_INCREMENT。

MySQL8.0 新特性：自增变量的持久化

MySQL 8.0 版本引入了自增变量的持久化这一新特性，旨在解决之前版本中自增主键在数据库重启后可能重置的问题。

背景与问题

在 MySQL 8.0 之前的版本中，如果自增主键 AUTO_INCREMENT 的值大于当前表中的最大值加1（max(primary key) + 1），在数据库重启后，AUTO_INCREMENT 的值可能会重置为 max(primary key) + 1。这种情况在某些业务场景下可能会导致主键冲突或其他难以发现的问题。

自增变量持久化的引入

为了解决上述问题，MySQL 8.0 引入了自增变量的持久化特性。这意味着，从 MySQL 8.0 开始，当前最大的自增计数器每当发生变化时，其值会被写入 redo log（重做日志）中，并在每个检查点时保存在 engine-private system table（引擎私有系统表）中。这样，即使数据库重启，AUTO_INCREMENT 的值也不会改变，因为它会从持久化的存储中恢复。

持久化实现机制

1. 写入 redo log：每次自增计数器发生变化时，MySQL 会将其值写入 redo log 中。这是为了确保在数据库崩溃或重启时，能够恢复最新的自增值。
2. 保存在 engine-private system table：此外，MySQL 还会在每个检查点将自增值保存在引擎私有系统表中。这样做是为了在数据库正常关闭和重启时，能够从该表中读取最新的自增值。

相关系统变量

在 MySQL 8.0 中，与自增变量持久化相关的系统变量包括：

• auto_increment_increment：表示每个分片（shard）上的自增步长。这个变量可以设置为全局或会话级别，并且可以通过 SET GLOBAL 或 SET SESSION 命令进行修改。同时，它也可以被持久化到配置文件中，以便在数据库重启后保持设置不变。但需要注意的是，这里的“持久化”是指将变量的值保存在配置文件中，而不是指自增变量本身的持久化。自增变量本身的持久化是通过 redo log 和引擎私有系统表实现的。
• auto_increment_offset：表示每个分片上的自增起始值。与 auto_increment_increment 类似，这个变量也可以设置为全局或会话级别，并且可以通过 SET GLOBAL 或 SET SESSION 命令进行修改和持久化。

使用示例

以下是一个使用自增变量持久化的示例：

-- 创建一个表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
);

-- 设置自增变量的持久化参数
SET PERSIST auto_increment_increment = 2;
SET PERSIST auto_increment_offset = 1;

-- 插入数据
INSERT INTO users (name) VALUES ('Alice'), ('Bob'), ('Charlie');

-- 查看自增变量的值
SHOW VARIABLES LIKE 'auto_increment%';

在上述示例中，我们首先创建了一个名为 users 的表，并设置了自增主键 id 和一个 name 字段。然后，我们通过 SET PERSIST 命令设置了 auto_increment_increment 和 auto_increment_offset 的值，以实现自增变量的持久化。接着，我们插入了三条数据，并查看了自增变量的值。

总结

MySQL 8.0 的自增变量持久化特性解决了之前版本中自增主键在数据库重启后可能重置的问题。通过将自增计数器的值写入 redo log 和保存在引擎私有系统表中，MySQL 8.0 确保了即使数据库重启，自增变量的值也不会改变。这一特性在分布式系统、负载均衡系统以及集群环境中非常有用，因为它可以确保数据的一致性和连续性。

总结

AUTO_INCREMENT 是一个强大的工具，可以简化主键生成的过程，确保数据 的唯一性和一致性。通过合理使用 AUTO_INCREMENT，你可以避免手动管理主键值的麻烦，并专注于数据的其他方面。