3.6.9 使用 AUTO_INCREMENT

AUTO_INCREMENT 属性可用于为新记录生成唯一标识:

  1. CREATE TABLE animals (
  2.      id MEDIUMINT NOT NULL AUTO_INCREMENT,
  3.      name CHAR(30) NOT NULL,
  4.      PRIMARY KEY (id)
  5. );
  7. INSERT INTO animals (name) VALUES
  8.     ('dog'),('cat'),('penguin'),
  9.     ('lax'),('whale'),('ostrich');
  11. SELECT * FROM animals;

返回结果为:

  1. +----+---------+
  2. | id | name    |
  3. +----+---------+
  4. |  1 | dog     |
  5. |  2 | cat     |
  6. |  3 | penguin |
  7. |  4 | lax     |
  8. |  5 | whale   |
  9. |  6 | ostrich |
  10. +----+---------+

AUTO_INCREMENT 列没有指定值, 所以 MySQL 会自动分配序列号. 你也可以显式的为列分配0来生成序列号, 除非启用了 NO_AUTO_VALUE_ON_ZERO 模式. 例如:

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

如果列被声明为 NOT NULL, 也可以将 NULL 赋给列以生成序列号. 例如:

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

当你插入其它的值到 AUTO_INCREMENT 列, 列会被设置为该值, 序列会被重置, 以便下一个自动生成的值按照最大列值的顺序生成. 例如:

  1. INSERT INTO animals (id,name) VALUES(100,'rabbit');
  2. INSERT INTO animals (id,name) VALUES(NULL,'mouse');
  3. SELECT * FROM animals;
  4. +-----+-----------+
  5. | id  | name      |
  6. +-----+-----------+
  7. |   1 | dog       |
  8. |   2 | cat       |
  9. |   3 | penguin   |
  10. |   4 | lax       |
  11. |   5 | whale     |
  12. |   6 | ostrich   |
  13. |   7 | groundhog |
  14. |   8 | squirrel  |
  15. | 100 | rabbit    |
  16. | 101 | mouse     |
  17. +-----+-----------+

更新已有的 AUTO_INCREMENT 列的值也会重置 AUTO_INCREMENT 序列.

你可以使用 LAST_INSERT_ID() SQL 函数或者 mysql_insert_id() C API 函数检索最近自动生成的 AUTO_INCREMENT 值. 这些函数是基于连接的, 所以它们的返回值不受其他执行插入的连接的影响.

对 AUTO_INCREMENT 列使用最小的整数数据类型, 该列要足够大, 可以容纳所需的最大序列值. 当列达到数据类型的上限时, 尝试生成下一个序列号将会失败. 使用 UNSIGNED 属性允许更大的范围. 例如, 如果你使用 TINYINT, 最大允许的序列号时 127. 对于 TINYINT UNSIGNED, 最大值为 255. 参阅 Section 11.2.1, “整数类型(精确值) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT” 获取所有整数类型的范围.

注意

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

AUTO_INCREMENT 从超过 1 的值开始, 使用 CREATE TABLE 或者 ALTER TABLE 设置值, 像这样:

  1. mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;

InnoDB Notes

有关在 InnoDB 中关于 AUTO_INCREMENT 特定用法, 参阅 Section 15.6.1.4, “InnoDB 中的 AUTO_INCREMENT 处理”.

MyISAM Notes

  • 对于 MyISAM 表, 你可以指定在多列索引的第二列上指定 AUTO_INCREMENT. 在本例中, AUTO_INCREMENT 列生成的值计算为 MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. 对于你想将数据放入到有序组中时, 非常有用.

    1.   CREATE TABLE animals (
    2.       grp ENUM('fish','mammal','bird') NOT NULL,
    3.       id MEDIUMINT NOT NULL AUTO_INCREMENT,
    4.       name CHAR(30) NOT NULL,
    5.       PRIMARY KEY (grp,id)
    6.   ) ENGINE=MyISAM;
    8.   INSERT INTO animals (grp,name) VALUES
    9.       ('mammal','dog'),('mammal','cat'),
    10.       ('bird','penguin'),('fish','lax'),('mammal','whale'),
    11.       ('bird','ostrich');
    13.   SELECT * FROM animals ORDER BY grp,id;

    返回结果为:

    1.   +--------+----+---------+
    2.   | grp    | id | name    |
    3.   +--------+----+---------+
    4.   | fish   |  1 | lax     |
    5.   | mammal |  1 | dog     |
    6.   | mammal |  2 | cat     |
    7.   | mammal |  3 | whale   |
    8.   | bird   |  1 | penguin |
    9.   | bird   |  2 | ostrich |
    10.   +--------+----+---------+

    在本例中 (当 AUTO_INCREMENT 列是多列索引中的一部分时), 如果删除了任何组中 AUTO_INCREMENT 值最大的记录, 那么会重复使用 AUTO_INCREMENT 值. 即便是 MyISAM 表也会发生这种亲口光, AUTO_INCREMENT 值通常不会被重复使用.

  • 如果 AUTO_INCREMENT 列是多列索引的一部分, MySQL 使用 AUTO_INCREMENT 列作为索引的开始(如果有的话)生成序列值. 例如, 如果 animals 表保存索引 PRIMARY KEY (grp, id) 和 INDEX (id), MySQL 将会忽略用于生成序列值的 PRIMARY KEY. 因此, 该表将包含单个序列, 而不是每个 grp 值的序列.

进一步阅读

有关 AUTO_INCREMENT 的更多信息请点击这里: