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

Генерирование значений последовательности

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

Решение
Вставьте в столбец NULL или просто пропустите в предложении INSERT. И в том и в другом случае MySQL создаст для вас новый номер последовательности.

Обсуждение
Одним из полезных свойств столбца AUTO_INCREMENT является то, что вы не должны присваивать ему значения – это сделает MySQL. Есть два способа формирования новых значений AUTO_INCREMENT, о которых будет рассказано на примере столбца id таблицы insect. Во-первых, можно явно установить столбец id в NULL.1 Следующее предложение вставляет четыре первых экспоната Джуниора в таблицу insect именно так:

mysql> INSERT INTO insect (id,name,date,origin) VALUES
-> (NULL,'housefly','2001-09-10','kitchen'),
-> (NULL,'millipede','2001-09-10','driveway'),
-> (NULL,'grasshopper','2001-09-10','front yard'),
-> (NULL,'stink bug','2001-09-10','front yard');

Во-вторых, можно полностью убрать столбец id из предложения INSERT. В MySQL можно создавать записи, не указывая явно значение каждого столбца. MySQL автоматически присваивает неуказанным столбцам значения по умолчанию, а умолчание для столбца AUTO_INCREMENT – это следующий номер последовательности. То есть вы можете вставлять записи в таблицу insect, не упоминая о столбце id. Это предложение добавляет четыре следующих экспоната Джуниора в таблицу insect вторым способом:

mysql> INSERT INTO insect (name,date,origin) VALUES
-> ('cabbage butterfly','2001-09-10','garden'),
-> ('ant','2001-09-10','back yard'),
-> ('ant','2001-09-10','back yard'),
-> ('millbug','2001-09-10','under rock');

Какой бы способ вы ни выбрали, MySQL определяет следующий номер последовательности для каждой записи и присваивает его столбцу id:

mysql> SELECT * FROM insect ORDER BY id;
+---+--------------------+---------------+-------------+
| id | name                    | date             | origin        |
+---+--------------------+---------------+-------------+
| 1 | housefly                | 2001-09-10 | kitchen      |
| 2 | millipede                | 2001-09-10 | driveway   |
| 3 | grasshopper         | 2001-09-10 | front yard |
| 4 | stink bug               | 2001-09-10 | front yard |
| 5 | cabbage butterfly | 2001-09-10 | garden      |
| 6 | ant                        | 2001-09-10 | back yard  |
| 7 | ant                        | 2001-09-10 | back yard  |
| 8 | millbug                   | 2001-09-10 | under rock |
+--+----------------------+--------------+--------------+

Когда Джуниор найдет следующих насекомых, вы сможете добавить в таблицу новые записи, которым будут присвоены следующие значения последовательности (9, 10, …).

Принцип, лежащий в основе формирования столбца AUTO_INCREMENT, достаточно прост: каждый раз, когда вы создаете новую строку, MySQL генерирует новый номер в последовательности и присваивает его строке. Но необходимо иметь в виду некоторые тонкости, а также особенности обработки последовательностей AUTO_INCREMENT для разных типов таблиц, тогда вы сможете более эффективно работать с последовательностями и избежать неприятных сюрпризов. Например, вот что может произойти, если вы явно устанавливаете столбец id в значение не-NULL:

• Если такое значение уже присутствует в таблице, выдается ошибка:

mysql> INSERT INTO insect (id,name,date,origin) VALUES
-> (3,'cricket','2001-09-11','basement');
ERROR 1062 at line 1: Duplicate entry '3' for key 1

Она возникает, так как при создании столбца AUTO_INCREMENT вы должны создать для него индекс PRIMARY KEY или UNIQUE, поэтому он не может содержать повторяющиеся значения.

• Если значение не присутствует в таблице, MySQL вставляет запись с этим значением. Кроме того, если оно больше текущего значения счетчика последовательности, то счетчику присваивается значение, на единицу превышающее вставленное. Сейчас таблица insect содержит значения последовательности от 1 до 8. Если вы вставляете новую строку, в которой столбец id установлен в 20, это значение становится новым максимумом.

При последующих вставках, автоматически формирующих значения id, значения будут начинаться с 21. В результате значения с 9 по 19 останутся неиспользованными, и в последовательности возникнет разрыв.

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

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

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