AUTO_INCREMENT

Описание

Атрибут AUTO_INCREMENT может использоваться для генерации уникального идентификатора для новых строк. При вставке новой записи в таблицу (или при добавлении атрибута AUTO_INCREMENT с помощью инструкции ALTER TABLE), и поле auto_increment имеет значение NULL или DEFAULT (в случае INSERT), значение будет автоматически инкрементировано. Это также относится к значению 0, если не включён SQL_MODE NO_AUTO_VALUE_ON_ZERO.

Поля AUTO_INCREMENT по умолчанию начинаются с 1. Автоматически сгенерированное значение никогда не может быть меньше 0.

В каждой таблице может быть только одно поле AUTO_INCREMENT. Оно должно быть определено как ключевое поле (не обязательно как первичный ключ PRIMARY KEY или уникальный ключ UNIQUE). В некоторых движках хранения (включая по умолчанию InnoDB), если ключ состоит из нескольких полей, поле AUTO_INCREMENT должно быть первым полем. Движки хранения, которые разрешают размещение поля в другом месте, это Aria, MyISAM, MERGE, Spider, TokuDB, BLACKHOLE, FederatedX и Federated.

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'),
    ('fox'),('whale'),('ostrich');

SELECT * FROM animals;
+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | fox     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+

SERIAL является псевдонимом для BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.

CREATE TABLE t (id SERIAL, c CHAR(1)) ENGINE=InnoDB;

SHOW CREATE TABLE t \G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `c` char(1) DEFAULT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Установка или изменение значения Auto_Increment

Вы можете использовать инструкцию ALTER TABLE для присвоения нового значения параметру таблицы auto_increment, или установить системную переменную сервера insert_id для изменения следующего значения AUTO_INCREMENT , которое будет вставлено текущей сессией.

LAST_INSERT_ID() можно использовать для просмотра последнего значения AUTO_INCREMENT , вставленного текущей сессией.

ALTER TABLE animals AUTO_INCREMENT=8;

INSERT INTO animals (name) VALUES ('aardvark');

SELECT * FROM animals;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | dog       |
|  2 | cat       |
|  3 | penguin   |
|  4 | fox       |
|  5 | whale     |
|  6 | ostrich   |
|  8 | aardvark  |
+----+-----------+

SET insert_id=12;

INSERT INTO animals (name) VALUES ('gorilla');

SELECT * FROM animals;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | dog       |
|  2 | cat       |
|  3 | penguin   |
|  4 | fox       |
|  5 | whale     |
|  6 | ostrich   |
|  8 | aardvark  |
| 12 | gorilla   |
+----+-----------+

InnoDB

До версии MariaDB 10.2.3, InnoDB использовал счетчик auto-increment, хранимый в памяти. При перезапуске сервера счетчик инициализируется наибольшим значением, использованным в таблице, что отменяет эффект любого параметра AUTO_INCREMENT = N в инструкциях таблицы.

Начиная с MariaDB 10.2.4, это ограничение снято, и AUTO_INCREMENT сохраняется.

См. также Обработка AUTO_INCREMENT в InnoDB.

Установка явных значений

Можно указать значение для поля AUTO_INCREMENT. Если ключ является первичным или уникальным, значение не должно уже существовать в ключе.

Если новое значение больше текущего максимального значения, значение AUTO_INCREMENT обновляется, поэтому следующее значение будет больше. Если новое значение меньше текущего максимального значения, значение AUTO_INCREMENT остается неизменным.

Следующий пример демонстрирует эти особенности:

CREATE TABLE t (id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY) ENGINE = InnoDB;

INSERT INTO t VALUES (NULL);
SELECT id FROM t;
+----+
| id |
+----+
|  1 |
+----+

INSERT INTO t VALUES (10); -- higher value
SELECT id FROM t;
+----+
| id |
+----+
|  1 |
| 10 |
+----+

INSERT INTO t VALUES (2); -- lower value
INSERT INTO t VALUES (NULL); -- auto value
SELECT id FROM t;
+----+
| id |
+----+
|  1 |
|  2 |
| 10 |
| 11 |
+----+

Движок хранения ARCHIVE не позволяет вставлять значение, меньшее текущего максимального.

Пропущенные значения

В столбце AUTO_INCREMENT обычно есть пропущенные значения. Это происходит потому, что если строка удаляется или значение AUTO_INCREMENT явно обновляется, старые значения никогда не используются повторно. Инструкция REPLACE также удаляет строку, и её значение теряется. В InnoDB значения могут быть зарезервированы транзакцией; но если транзакция завершается неудачно (например, из-за ROLLBACK), зарезервированное значение будет потеряно.

Таким образом, значения AUTO_INCREMENT можно использовать для сортировки результатов в хронологическом порядке, но не для создания числовой последовательности.

Репликация

Для обеспечения безопасности при использовании master-master или Galera репликации следует использовать системные переменные auto_increment_increment и auto_increment_offset для генерации уникальных значений для каждого сервера.

Ограничения CHECK, значения по умолчанию и виртуальные столбцы

Генерация значений Auto_Increment при добавлении атрибута

CREATE OR REPLACE TABLE t1 (a INT);
INSERT t1 VALUES (0),(0),(0);
ALTER TABLE t1 MODIFY a INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
SELECT * FROM t1;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
+---+

CREATE OR REPLACE TABLE t1 (a INT);
INSERT t1 VALUES (5),(0),(8),(0);
ALTER TABLE t1 MODIFY a INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
SELECT * FROM t1;
+---+
| a |
+---+
| 5 |
| 6 |
| 8 |
| 9 |
+---+

Если SQL_MODE NO_AUTO_VALUE_ON_ZERO установлен, нулевые значения не будут автоматически инкрементироваться:

SET SQL_MODE='no_auto_value_on_zero';
CREATE OR REPLACE TABLE t1 (a INT);
INSERT t1 VALUES (3), (0);
ALTER TABLE t1 MODIFY a INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
SELECT * FROM t1;
+---+
| a |
+---+
| 0 |
| 3 |
+---+

См. также

• Начало работы с индексами
• Последовательности - альтернатива auto_increment, доступная начиная с MariaDB 10.3
• Вопросы и ответы по AUTO_INCREMENT
• LAST_INSERT_ID()
• Обработка AUTO_INCREMENT в InnoDB
• BLACKHOLE и AUTO_INCREMENT
• UUID_SHORT() - Генерация уникальных идентификаторов
• Генерация идентификаторов – от AUTO_INCREMENT до последовательности (percona.com)