AUTO_INCREMENT 属性可以用于为新行生成唯一标识：The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows:

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 会自动分配序列号。你也可以显式将 0 分配给该列以生成序列号，除非启用了 NO_AUTO_VALUE_ON_ZERO SQL 模式。例如：No value was specified for the AUTO_INCREMENT column, so MySQL assigned sequence numbers automatically. You can also explicitly assign 0 to the column to generate sequence numbers, unless the NO_AUTO_VALUE_ON_ZERO SQL mode is enabled. For example:

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

如果该列声明为 NOT NULL，也可以将 NULL 分配给该列以生成序列号。例如：If the column is declared NOT NULL, it is also possible to assign NULL to the column to generate sequence numbers. For example:

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

当你向任何其他值插入到 AUTO_INCREMENT 列时，该列会被设置为该值，并且序列将被重置，以便下一个自动生成的值按列的最大值顺序递增。例如：When you insert any other value into an AUTO_INCREMENT column, the column is set to that value and the sequence is reset so that the next automatically generated value follows sequentially from the largest column value. For example:

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_INCREMENT 序列。Updating an existing AUTO_INCREMENT column value also resets the AUTO_INCREMENT sequence.

您可以使用 LAST_INSERT_ID() SQL 函数或 mysql_insert_id() C API 函数检索最近自动生成的 AUTO_INCREMENT 值。这些函数是针对连接特定的，所以它们的返回值不会受到其他也在执行插入操作的连接的影响。You can retrieve the most recent automatically generated AUTO_INCREMENT value with the LAST_INSERT_ID() SQL function or the mysql_insert_id() C API function. These functions are connection-specific, so their return values are not affected by another connection which is also performing inserts.

对于 AUTO_INCREMENT 列，应使用能容纳所需最大序列值的最小整数数据类型。当列达到该数据类型的上限时，下一个生成序列号的尝试将失败。如果可能，使用 UNSIGNED 属性以允许更大的范围。例如，如果使用 TINYINT，最大允许的序列号是 127。对于 TINYINT UNSIGNED，最大值是 255。有关所有整数类型的范围，请参见 第 13.1.2 节，“整数类型（精确值） - INTEGER、INT、SMALLINT、TINYINT、MEDIUMINT、BIGINT”。Use the smallest integer data type for the AUTO_INCREMENT column that is large enough to hold the maximum sequence value you require. When the column reaches the upper limit of the data type, the next attempt to generate a sequence number fails. Use the UNSIGNED attribute if possible to allow a greater range. For example, if you use TINYINT, the maximum permissible sequence number is 127. For TINYINT UNSIGNED, the maximum is 255. See Section 13.1.2, “Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT” for the ranges of all the integer types.

要从非 1 的 AUTO_INCREMENT 值开始，可使用 CREATE TABLE 或 ALTER TABLE 设置该值，如下所示：To start with an AUTO_INCREMENT value other than 1, set that value with CREATE TABLE or ALTER TABLE, like this:

mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;