MySQL / 11. Формирование и использование последовательностей

Как начать последовательность с определенного значения

Задача
Последовательности начинаются с 1, но вам хотелось бы использовать другое начальное значение.

Решение
Добавьте инструкцию AUTO_INCREMENT в предложение CREATE TABLE при создании таблицы. Если таблица уже создана, выполните предложение ALTER TABLE для установки начального значения.

Обсуждение
По умолчанию последовательности AUTO_INCREMENT начинаются с 1:

mysql> CREATE TABLE t
-> (id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id));
mysql> INSERT INTO t (id) VALUES(NULL);
mysql> INSERT INTO t (id) VALUES(NULL);
mysql> INSERT INTO t (id) VALUES(NULL);
mysql> SELECT id FROM t ORDER BY id;

+--+
| id |
+--+
| 1 |
| 2 |
| 3 |
+--+

В таблице MyISAM можно начинать последовательность со значения n, добавив инструкцию AUTO_INCREMENT = n в конец предложения CREATE TABLE:

mysql> CREATE TABLE t
-> (id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id))
-> AUTO_INCREMENT = 100;
mysql> INSERT INTO t (id) VALUES(NULL);
mysql> INSERT INTO t (id) VALUES(NULL);
mysql> INSERT INTO t (id) VALUES(NULL);
mysql> SELECT id FROM t ORDER BY id;

+-----+
| id     |
+-----+
| 100 |
| 101 |
| 102 |
+-----+

Есть и другой способ: вы можете создать таблицу, а затем установить начальное значение последовательности при помощи ALTER TABLE:

mysql> CREATE TABLE t
-> (id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id));
mysql> ALTER TABLE t AUTO_INCREMENT = 100;
mysql> INSERT INTO t (id) VALUES(NULL);
mysql> INSERT INTO t (id) VALUES(NULL);
mysql> INSERT INTO t (id) VALUES(NULL);
mysql> SELECT id FROM t ORDER BY id;

+-----+
| id    |
+-----+
| 100 |
| 101 |
| 102 |
+-----+

Чтобы установить начало последовательности в n для таблиц, тип которых отличается от MyISAM, требуется небольшая хитрость: нужно вставить «фальшивую» запись со значением последовательности n –1, а затем удалить ее после вставки одной или более «настоящих» записей. Следующий пример показывает, как начать последовательность со 100 в таблице InnoDB:

mysql> CREATE TABLE t
-> (id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id))
-> TYPE = InnoDB;
mysql> INSERT INTO t (id) VALUES(99);
mysql> INSERT INTO t (id) VALUES(NULL);
mysql> INSERT INTO t (id) VALUES(NULL);
mysql> INSERT INTO t (id) VALUES(NULL);
mysql> DELETE FROM t WHERE id = 99;
mysql> SELECT * FROM t ORDER BY id;

+-----+
| id    |
+-----+
| 100 |
| 101 |
| 102 |
+-----+

Помните, что если вы удаляете содержимое таблицы при помощи предложения DELETE без инструкции WHERE, начальное значение последовательности может быть установлено в 1 даже для тех типов таблиц, которые обычно не используют значения последовательности повторно. В этом случае, если вы не хотите, чтобы последовательность начиналась с 1, следует явно установить начальное значение последовательности после очистки таблицы.

Статьи по MySQL на эту тему:

Генераторы однострочных последовательностей
Добавление последовательности в существующую таблицу
Использование значений AUTO_INCREMENT для связывания таблиц
Последовательная нумерация строк вывода запроса
Управление несколькими столбцами AUTO_INCREMENT одновременно