MySQL AUTO_INCREMENT 示例
马库斯·艾伦
自动增量是什么？
自动增量是一种对数字数据类型进行操作的函数。每次将记录插入表中定义为自动增量的字段时，它都会自动生成连续的数值。
该属性适用于从 TINYINT 到 BIGINT 的任何整数类型。此外，该列必须建立索引，当其被声明为主键时，索引会自动建立。
什么时候使用自动增量？
在关于……的课程中 数据库规范化我们研究了如何通过将数据存储在许多小表中，并使用主键和外键将它们关联起来，从而以最小的冗余存储数据。
主键必须是唯一的，因为它唯一标识数据库中的每一行。但是，我们如何确保主键始终是唯一的呢？
一种可能的解决方案是使用公式生成主键，并在添加数据之前检查表中是否存在该主键。这种方法或许可行，但较为复杂且并非万无一失。两个会话同时插入数据时，仍有可能读取到相同的最大值，从而导致冲突。
为了避免这种复杂性，并确保主键始终是唯一的，我们可以使用 MySQL 使用自增功能生成主键。自增功能与 INT 数据类型配合使用。INT 数据类型支持有符号值和无符号值。无符号数据类型只能包含正数。最佳实践建议对自增主键定义无符号约束。
自动增量语法
既然理由已经明确，那就来看看用于创建电影类别表的脚本吧。
CREATE TABLE `categories` ( `category_id` int UNSIGNED NOT NULL AUTO_INCREMENT, `category_name` varchar(150) DEFAULT NULL, `remarks` varchar(500) DEFAULT NULL, PRIMARY KEY (`category_id`) );
请注意 category_id 字段上的“AUTO_INCREMENT”属性。这使得每次向表中插入新行时，类别 ID 都会自动生成。在向表中插入数据时，不会提供该 ID。 MySQL 生成它。
注意： UNSIGNED 关键字会将列的正值范围扩大一倍，而之前写成 int(11) 的显示宽度已被弃用。 MySQL 8.0.17 及更高版本。普通版 INT 这是当前形式。
默认情况下，AUTO_INCREMENT 的起始值为 1，每新增一条记录，该值将递增 1。
让我们来看一下类别表中的当前内容。
SELECT * FROM `categories`;
在以下位置执行上述脚本 MySQL 使用 Workbench 对 myflixdb 数据库进行测试，得到以下结果。
| category_id | category_name | remarks |
|---|---|---|
| 1 | Comedy | Movies with humour |
| 2 | Romantic | Love stories |
| 3 | Epic | Story acient movies |
| 4 | Horror | NULL |
| 5 | Science Fiction | NULL |
| 6 | Thriller | NULL |
| 7 | Action | NULL |
| 8 | Romantic Comedy | NULL |
现有八行数据，因此下一个生成的 ID 应该是 9。现在让我们向 categories 表中插入一个新类别，只提供名称。
INSERT INTO `categories` (`category_name`) VALUES ('Cartoons');
在 myflixdb 中执行上述脚本 MySQL 工作台 给出了如下所示的结果。
| category_id | category_name | remarks |
|---|---|---|
| 1 | Comedy | Movies with humour |
| 2 | Romantic | Love stories |
| 3 | Epic | Story acient movies |
| 4 | Horror | NULL |
| 5 | Science Fiction | NULL |
| 6 | Thriller | NULL |
| 7 | Action | NULL |
| 8 | Romantic Comedy | NULL |
| 9 | Cartoons | NULL |
请注意，我们没有提供类别 ID。 MySQL 由于类别 ID 被定义为自动递增，因此它是自动生成的。
如果你想获取由 MySQL，您可以使用 LAST_INSERT_ID 函数来执行此操作。下面显示的脚本获取生成的最后一个 ID。
SELECT LAST_INSERT_ID();
执行上述脚本可得到 INSERT 查询生成的最后一个自增编号。结果如下所示。
提示： LAST_INSERT_ID() 的作用域仅限于您自己的连接，因此其他用户插入生成的值永远不会错误地返回给您。
如何设置或重置自动递增起始值
默认序列从 1 开始，但这并非项目总是需要的。发票编号可能需要沿用旧系统，测试表也常常需要重置。 MySQL 直接暴露计数器，因此两种情况都用一个子句处理。请按照以下步骤控制起始数字。
- 创建时设置该值。 在 CREATE TABLE 语句中添加 AUTO_INCREMENT 子句。这样，插入的第一行数据将获得该子句指定的数字，而不是 1。
- 更改现有表中的值。 绝大部分储备使用 更改表 使用相同的条款。 MySQL 只有当新数字大于当前存储的最大标识符时，才接受该新数字。
- 重置已清空的表格。 TRUNCATE TABLE 会一次性删除所有行并将计数器重置为 1，而 DELETE 单独使用则无法做到这一点。
- 确认更改。 插入一行，然后使用 LAST_INSERT_ID() 读取标识符，之后再依赖新的序列。
-- Start a brand-new table at 1000 CREATE TABLE `invoices` ( `invoice_id` int UNSIGNED NOT NULL AUTO_INCREMENT, `amount` decimal(10,2), PRIMARY KEY (`invoice_id`) ) AUTO_INCREMENT = 1000; -- Move the counter on an existing table ALTER TABLE `categories` AUTO_INCREMENT = 100; -- Empty the table and reset the counter to 1 TRUNCATE TABLE `categories`;
步长也可以通过 `auto_increment_increment` 系统变量进行更改，但它应用于整个服务器，而不是单个表。它主要用于复制，其中两个服务器不能生成相同的标识符。
为什么自增序列中会出现间隙？
表格中迟早会出现诸如 1、2、5、6 之类的标识符。这很正常。计数器的设计目的是保证唯一性，而不是保证连续的数字，而且它永远不会重复输出相同的值。
出现差距的原因如下。
- 已删除的行： 当从表中删除一行时，其自动递增的 ID 不会被重新使用。 MySQL 继续按顺序生成新数字。
- 已回滚的交易： 该数字在插入操作执行的瞬间即被占用。如果事务回滚，该行数据将消失，但该数字已被占用。
- 插入失败： 被 UNIQUE 约束拒绝的语句在失败之前仍然可以消耗一个标识符。
- 散装插页： InnoDB 可能会为多行插入保留一块数字，并丢弃未使用的数字。
试图填补这些空白是错误的。重新编号行会破坏所有指向它们的外部键，而且重新编号的值本身没有任何业务意义。如果报表需要连续的列表，请在查询中生成行号，而不是重写存储的数据。
