Удаление записей и формирование последовательности

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

Решение
Все зависит от типа таблицы и от того, какие записи удаляются.

Обсуждение
Пока что мы говорили только о генерировании значений последовательности для столбца AUTO_INCREMENT при добавлении записей в таблицу. Но не стоит надеяться на то, что записи никогда не будут удаляться. Что же тогда произойдет с последовательностью?

Снова обратимся к проекту Джуниора по сбору коллекции насекомых, для которого у нас пока есть такая таблица insect:

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 |
+--+----------------------+---------------+-------------+

Теперь ее надо изменить, потому что после того, как Джуниор наконец донес до дома письменные указания по выполнению работы, вы обнаружили там два факта, связанных с содержимым таблицы insect:

• Экспонатами могут быть только насекомые, а не те, кто на них похож, как многоножки (millipede) и мокрицы (millbug).

• Идея проекта в том, чтобы собрать как можно больше разных экспонатов, а не просто как можно больше насекомых.


То есть допустима только одна запись для муравья (ant).

Правила требуют удаления нескольких строк из таблицы insect, а именно, записей со значениями id, равными 2 (millipede), 8 (millbug) и 7 (дубликат для ant). Итак, несмотря на понятное разочарование Джуниора в связи с уменьшением его коллекции, вы поручаете ему удалить записи, используя предложение DELETE:mysql> DELETE FROM insect WHERE id IN (2,8,7);

Одна из причин, по которой полезны уникальные значения идентификаторов, – они позволяют однозначно идентифицировать каждую запись. Записи о муравьях идентичны во всем, кроме идентификаторов. Если бы в таблице insect не было такого столбца, было бы сложнее удалить одну запись. После удаления неподходящих записей результирующая таблица будет выглядеть так:

mysql> SELECT * FROM insect ORDER BY id;
+--+---------------------+---------------+-------------+
| id | name | date | origin |
+--+---------------------+---------------+------------+
| 1 | housefly | 2001-09-10 | kitchen |
| 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 |
+--+----------------------+--------------+-------------+

Теперь последовательность в столбце id имеет пропуск (нет строки 2), а значения 7 и 8 на вершине последовательности отсутствуют.


Как эти удаления повлияют на последующие операции вставки? Какой номер последовательности получит следующая строка?

Удаление строки 2 привело к созданию пропуска в середине последовательности. Это никак не влияет на последующие операции вставки, так как MySQL не предпринимает попыток заполнения «дыр». Но удаление строк 7 и 8 удаляет значения верхушки последовательности, и результат такой операции определятся типом таблицы:

• В таблицах ISAM и BDB следующий номер последовательности – это всегда наименьшее положительное целое, не присутствующее в столбце. Если вы удаляете строки, содержащие максимальные значения последовательности, эти значения будут использованы повторно. (То есть после удаления записей со значениями 7 и 8 следующей вставленной записи будет присвоено значение 7.)

• В таблицах MyISAM и InnoDB значения повторно не используются. Следующий номер последовательности – это наименьшее положительное целое, которое ранее не использовалось. (Для последовательности, остановившейся на значении 8, следующая запись получит значение 9, даже если предварительно будут удалены записи 7 и 8.) Если вам нужны строго монотонные последовательности, используйте один из этих типов таблиц.
Таблицы ISAM – это единственный тип таблиц, доступных в MySQL до версии 3.23, так что до этой версии вы можете только повторно использовать значения, удаленные из верхушки последовательности.


Таблицы MyISAM появляются в версии MySQL 3.23 (и с этого же момента MyISAM становится типом таблицы по умолчанию). Таблицы BDB и InnoDB доступны, начиная с версии MySQL 3.23.17 и 3.23.29 соответственно.Если вы используете таблицу, тип которой обеспечивает не то поведение в отношении повторного использования значений последовательности, которое вам необходимо, используйте предложение ALTER TABLE для изменения типа таблицы на более подходящий. Например, если вы хотите изменить тип таб-
лицы с ISAM на MyISAM (чтобы не допустить повторного использования значений после удаления записей), сделайте следующее:

ALTER TABLE имя_таблицы TYPE = MYISAM;

Если вы не знаете тип вашей таблицы, используйте предложение SHOW TABLE STATUS:

mysql> SHOW TABLE STATUS LIKE 'insect'\G;
*************************** 1. row ***************************
Name: insect
Type: MyISAM
Row_format: Dynamic
Rows: 7
Avg_row_length: 30
Data_length: 216
Max_data_length: 4294967295
Index_length: 2048
Data_free: 0
Auto_increment: 8
Create_time: 2002-01-25 16:55:32
Update_time: 2002-01-25 16:55:32
Check_time: NULL
Create_options:
Comment:

Вывод показывает, что таблица insect относится к типу MyISAM.


(Можно было использовать SHOW CREATE TABLE.)

Оцените статью: (0 голосов)
0 5 0

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