MySQL AUTO_INCREMENT 自增列

在 MySQL 中，如果需要一个列的值为一个有序的整数序列，请使用自增列。

自增列是 MySQL 中的一个特殊的列，该列的值可由 MySQL 服务器自动生成，并且是一个按升序增长的正整数序列。自增列能够被用来为表的新行产生唯一的标识。

语法

要定义一个自增列，请使用 AUTO_INCREMENT 关键字。定义自增列的语法如下：

column_name data_type AUTO_INCREMENT
  ...

这里：

AUTO_INCREMENT 关键字标识该列是一个自增列。
自增列不能使用全部的数据类型，它只适用于整数或者浮点数类型，包括： TINYINT, SMALLINT, INT, MEDIUMINT, BIGINT, DECIMAL, FLOAT, DOUBLE。
自增列必须是主键或者唯一键。
自增列默认是 NOT NULL 的。
每个表中只能有一个自增列。
自增列的初始值是 1。每插入一行到表中，该列的值自动增加 1。
不像生成列，在插入新行时你可以为自增列指定一个值。

自增列实例

让我们看一些实际的例子来了解自增列的用法。

以下语句创建一个名为 user 的表，该表的 user_id 是自增列，也是主键。

CREATE TABLE `user` (
  `user_id` INT AUTO_INCREMENT,
  `name` VARCHAR(45),
  PRIMARY KEY (`user_id`));

在 user 表中插入两行数据：
```
INSERT INTO `user` (`name`)
VALUES ('Tim'), ('Jim');
```
注意，在这个语句中，我们并没有使用 user_id 列。这个列的值会自动生成。

现在，让我们看一下 user 表中的行：
```
SELECT * FROM `user`;
```
```
+---------+------+
| user_id | name |
+---------+------+
|       1 | Tim  |
|       2 | Jim  |
+---------+------+
2 rows in set (0.00 sec)
```
这里，MySQL 为 user_id 列自动生成了两个连续的值： 1 和 2。

删除 user_id 是 2 的行：

DELETE FROM `user`
WHERE `user_id` = 2;

插入一个新行：

INSERT INTO `user` (`name`) VALUES ('Jim');

现在，让我们看一下 user 表中的行：

SELECT * FROM `user`;

+---------+------+
| user_id | name |
+---------+------+
|       1 | Tim  |
|       3 | Jim  |
+---------+------+
2 rows in set (0.00 sec)

注意：新行的 user_id 不是 2 而是 3。这是因为 MySQL 不会重用删除的序列值。

让我们插入数据时为自动列指定一个值。

INSERT INTO `user` (`user_id`, `name`)
VALUES (20, 'Tom');

然后，插入一行数据：

INSERT INTO `user` (`name`) VALUES ('Lucy');

现在，让我们看一下 user 表中的行：

SELECT * FROM `user`;

+---------+------+
| user_id | name |
+---------+------+
|       1 | Tim  |
|       3 | Jim  |
|      20 | Tom  |
|      21 | Lucy |
+---------+------+
4 rows in set (0.00 sec)

如何查看自增列的值

要查看自增列的下一个值，可采用如下两种方法：

从 SHOW CREATE TABLE 语句输出的表定义中查看自增列的值。

SHOW CREATE TABLE `user`\G

*************************** 1. row ***************************
      Table: user
Create Table: CREATE TABLE `user` (
  `user_id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

通过最后一行中的 AUTO_INCREMENT=22 代表了表中自增列的下一个值是 22。

如果自增列的下一个值是 1，则不会输出 AUTO_INCREMENT=1。

从数据库信息表中查询自增列的值：

SELECT AUTO_INCREMENT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'testdb'
  AND TABLE_NAME = 'user';

+----------------+
| AUTO_INCREMENT |
+----------------+
|             22 |
+----------------+
1 row in set (0.00 sec)

这种方式有时候并不准确。因为当重置或修改了表的 AUTO_INCREMENT 值之后，这里并不会立刻更新。

自定自增列初始值

自增列的初始值默认是 1，但我们可以在创建表的时候设置一个自定义的值。如下：

CREATE TABLE `user` (
  `user_id` INT AUTO_INCREMENT,
  `name` VARCHAR(45),
  PRIMARY KEY (`user_id`)
) AUTO_INCREMENT = 10;

这里，我们创建了一个表，该表的自增列的初始值被指定为 10。

修改表的自增值

如果想重置或者修改表的自增值，可以使用 ALTER TABLE 语句修改表定义。如下：

ALTER TABLE `user` AUTO_INCREMENT = 30;

注意：

如果这里设置的值小于等于表中自增列的最大值，最终设置的值还是这个最大值的下一个数值。

比如，在 user 表中，我们修改 AUTO_INCREMENT 为 1，但 user 表的 AUTO_INCREMENT 的值还是 22。
当修改了表的 AUTO_INCREMENT 值之后， INFORMATION_SCHEMA.TABLES 表中的 AUTO_INCREMENT 列并不会立刻更新。你可以使用 SHOW CREATE TABLE 语句查看。

结论

在本文中，我们要学习了 MySQL 自增列以及如何使用自增列生成唯一标识。下面总结一下自增列的用法和工作原理：

一个表中只能有一个自增列。
自增列的数据类型只能使用整数和浮点数。
自增列的初始值是 1。你可以在创建表的时候设置自增列的初始值，也可以修改自增列的值。
删除一些行后，删除的自增列的值不能重用。
你可以使用 SHOW CREATE TABLE 语句查看自增列的值。

语法

自增列实例

如何查看自增列的值

自定自增列初始值

修改表的自增值

结论

本页目录