跳到主要内容

3.6.9 使用 AUTO_INCREMENT

AUTO_INCREMENT 属性可用于为新行生成唯一标识：

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
);

INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');

SELECT * FROM animals;

以上返回：

+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+

没有为 AUTO_INCREMENT 列指定值，因此 MySQL 自动分配序列号。除非启用 NO_AUTO_VALUE_ON_ZERO SQL 模式，否则也可以将 0 显式分配给列以生成序列号。例如：

INSERT INTO animals (id,name) VALUES(0,'groundhog');

如果列声明为 NOT NULL，也可以将 NULL 分配给列以生成序列号。例如：

INSERT INTO animals (id,name) VALUES(NULL,'squirrel');

当你将任何其他值插入 AUTO_INCREMENT 列时，该列将被设置为该值，序列将被重置，以便下一个自动生成的值从最大的列值开始依次出现。例如：

INSERT INTO animals (id,name) VALUES(100,'rabbit');
INSERT INTO animals (id,name) VALUES(NULL,'mouse');
SELECT * FROM animals;
+-----+-----------+
| id  | name      |
+-----+-----------+
|   1 | dog       |
|   2 | cat       |
|   3 | penguin   |
|   4 | lax       |
|   5 | whale     |
|   6 | ostrich   |
|   7 | groundhog |
|   8 | squirrel  |
| 100 | rabbit    |
| 101 | mouse     |
+-----+-----------+

更新现有的 AUTO_INCREMENT 列值也会重置 AUTO_INCREENT 序列。

你可以使用 LAST_INSERT_ID() SQL 函数或 mysql_insert_id() C API 函数检索最近自动生成的 AUTO_INCREMENT 值。这些函数是特定于连接的，因此它们的返回值不受另一个执行插入的连接的影响。

为 AUTO_INCREMENT 列使用最小的整数数据类型，该数据类型足够大，可以容纳所需的最大序列值。当列达到数据类型的上限时，下一次生成序列号的尝试将失败。如果可能，请使用 UNSIGNED 属性以允许更大的范围。例如，如果使用 TINYINT，则允许的最大序列号为 127。对于 TINYINT UNSIGNED，最大值为 255。有关所有整数类型的范围，参阅章节 11.1.2，“整数类型（精确值）——Integer、INT、SMALLINT、TINYINT、MEDIUMINT、BIGINT”。

提示

对于多行插入，LAST_INSERT_ID() 和 mysql_insert_id() 实际上从插入的第一行返回 AUTO_INCREMENT 键。这允许在复制设置中的其他服务器上正确复制多行插入。

要从非 1 的 AUTO_INCREMENT 值开始，请使用 CREATE TABLE 或 ALTER TABLE 设置该值，如下所示：

mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;

InnoDB 备注​

有关特定于 InnoDB 的 AUTO_INCREMENT 用法的信息，参阅章节 15.6.1.6，“InnoDB 中的 AUTO_INCREMENT 处理”。

MyISAM 备注​

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;

INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;

以上返回：

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

在这种情况下（当 AUTO_INCREMENT 列是多列索引的一部分时），如果删除任何组中 AUTO_INCREENT 值最大的行，则会重用 AUTO_INCREMENT 值。即使对于 MyISAM 表也会发生这种情况，对于这些表，AUTO_INCREMENT 值通常不会被重用。

  • 如果 AUTO_INCREMENT 列是多个索引的一部分，MySQL 将使用以 AUTO_INCREMENT 列开头的索引（如果有）生成序列值。例如，如果 animals 表包含索引 PRIMARY KEY (grp, id) 和 INDEX (id)，MySQL 将忽略 PRIMARY KEY 来生成序列值。因此，该表将包含单个序列，而不是每个 grp 值的序列。

延伸阅读​

有关 AUTO_INCREMENT 的更多信息，请访问：

原文链接