5.6.9 Using AUTO_INCREMENT — MySQL 8.4

5.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 автоматически назначил порядковые номера. Вы также можете явно задать 0 для столбца, чтобы сгенерировать порядковые номера, если не включён режим SQL NO_AUTO_VALUE_ON_ZERO. Например:

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_INCREMENT.

Вы можете получить последнее автоматически сгенерированное значение AUTO_INCREMENT с помощью функции SQL LAST_INSERT_ID() или функции API C. Эти функции зависят от соединения, поэтому их возвращаемые значения не зависят от другого соединения, которое также выполняет вставки.

Используйте самый маленький целочисленный тип данных для столбца AUTO_INCREMENT, который достаточно большой для хранения максимального требуемого значения последовательности. Когда столбец достигает верхнего предела типа данных, следующая попытка сгенерировать порядковый номер завершается ошибкой. Если возможно, используйте атрибут UNSIGNED, чтобы обеспечить больший диапазон. Например, если вы используете TINYINT, максимальное допустимое значение порядкового номера — 127. Для TINYINT UNSIGNED максимальное значение — 255. См. Раздел 13.1.2, «Целочисленные типы (точное значение) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT» для диапазонов всех целочисленных типов.

Примечание

При вставке нескольких строк функции LAST_INSERT_ID() фактически возвращают ключ AUTO_INCREMENT от первой из вставленных строк. Это позволяет корректно воспроизводить вставки нескольких строк на других серверах в настройке репликации.

Чтобы начать с значения AUTO_INCREMENT, отличного от 1, задайте это значение с помощью CREATE TABLE или ALTER TABLE, как показано ниже:

mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;

Примечания для InnoDB

Дополнительная информация об использовании AUTO_INCREMENT, специфичная для InnoDB, приведена в разделе 17.6.1.6, «Обработка AUTO_INCREMENT в InnoDB».

Примечания для MyISAM

Для таблиц MyISAM вы можете указать AUTO_INCREMENT для вторичного столбца в индексе с несколькими столбцами. В этом случае сгенерированное значение для столбца AUTO_INCREMENT вычисляется как MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. Это полезно, когда нужно поместить данные в упорядоченные группы.
```
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_INCREMENT повторно используются, если вы удаляете строку с наибольшим значением AUTO_INCREMENT в любой группе. Это происходит даже для таблиц MyISAM, для которых значения AUTO_INCREMENT обычно не повторно используются.
Если столбец AUTO_INCREMENT является частью нескольких индексов, MySQL генерирует значения последовательности, используя индекс, начинающийся со столбца AUTO_INCREMENT, если он существует. Например, если таблица animals содержала индексы PRIMARY KEY (grp, id) и INDEX (id), MySQL проигнорирует PRIMARY KEY при генерации значений последовательности. В результате в таблице будет единая последовательность, а не последовательность на каждое значение grp.

Дополнительная литература

Дополнительную информацию об AUTO_INCREMENT можно найти здесь:

Как назначить атрибут AUTO_INCREMENT столбцу: раздел 15.1.20, «Оператор CREATE TABLE» и раздел 15.1.9, «Оператор ALTER TABLE».
Как ведёт себя AUTO_INCREMENT в зависимости от режима SQL NO_AUTO_VALUE_ON_ZERO: раздел 7.1.11, «Режимы SQL сервера».
Как использовать функцию LAST_INSERT_ID() для поиска строки, содержащей последнее значение AUTO_INCREMENT: раздел 14.15, «Функции информации».
Установка используемого значения AUTO_INCREMENT: раздел 7.1.8, «Системные переменные сервера».
Раздел 17.6.1.6, «Обработка AUTO_INCREMENT в InnoDB»
AUTO_INCREMENT и репликация: Раздел 19.5.1.1, «Репликация и AUTO_INCREMENT».
Переменные системы сервера, относящиеся к AUTO_INCREMENT (auto_increment_increment и auto_increment_offset), которые можно использовать для репликации: Раздел 7.1.8, «Системные переменные сервера».