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;
Which returns:
+----+---------+
| 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 |
+-----+-----------+
更新InnoDB
table 中的现有AUTO_INCREMENT
列值不会像MyISAM
和NDB
table 那样重置AUTO_INCREMENT
序列。
您可以使用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”。
Note
对于多行插入,LAST_INSERT_ID()和mysql_insert_id()实际上从插入的行的第一个返回AUTO_INCREMENT
键。这样可以在复制设置中的其他服务器上正确地复制多行插入。
要以非 1 的AUTO_INCREMENT
值开头,请使用CREATE TABLE或ALTER TABLE设置该值,如下所示:
mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;
InnoDB Notes
有关InnoDB
专用的AUTO_INCREMENT
用法的信息,请参阅第 14.6.1.6 节“ InnoDB 中的 AUTO_INCREMENT 处理”。
MyISAM Notes
- 对于
MyISAM
table,您可以在多列索引的第二列中指定AUTO_INCREMENT
。在这种情况下,AUTO_INCREMENT
列的生成值计算为MAX(auto_increment_column)1 个前缀=给定前缀。当您要将数据放入有序组中时,这很有用。
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;
Which returns:
+--------+----+---------+
| grp | id | name |
+--------+----+---------+
| fish | 1 | lax |
| mammal | 1 | dog |
| mammal | 2 | cat |
| mammal | 3 | whale |
| bird | 1 | penguin |
| bird | 2 | ostrich |
+--------+----+---------+
在这种情况下(当AUTO_INCREMENT
列是多列索引的一部分时),如果删除任何组中具有最大AUTO_INCREMENT
值的行,则将重用AUTO_INCREMENT
值。即使对于MyISAM
table(通常不会重复使用AUTO_INCREMENT
值),也会发生这种情况。
- 如果
AUTO_INCREMENT
列是多个索引的一部分,则 MySQL 使用从AUTO_INCREMENT
列开始的索引(如果有)生成序列值。例如,如果animals
table 包含索引PRIMARY KEY (grp, id)
和INDEX (id)
,则 MySQL 将忽略PRIMARY KEY
来生成序列值。结果,该 table 将包含单个序列,而不是每个grp
值的序列。
Further Reading
有关AUTO_INCREMENT
的更多信息,请参见:
-
如何将
AUTO_INCREMENT
属性分配给列:第 13.1.18 节“ CREATE TABLE 语句”和第 13.1.8 节“ ALTER TABLE 语句”。 -
AUTO_INCREMENT
的行为取决于NO_AUTO_VALUE_ON_ZERO SQL 模式:第 5.1.10 节“服务器 SQL 模式”。 -
如何使用LAST_INSERT_ID()函数查找包含最新
AUTO_INCREMENT
值的行:第 12.15 节“信息功能”。 -
设置要使用的
AUTO_INCREMENT
值:第 5.1.7 节“服务器系统变量”。 -
AUTO_INCREMENT
和复制:第 16.4.1.1 节“复制和 AUTO_INCREMENT”。 -
与
AUTO_INCREMENT
(auto_increment_increment和auto_increment_offset)有关的服务器系统变量,可用于复制:第 5.1.7 节“服务器系统变量”。