zaLinux.ru

Как сбросить AUTO_INCREMENT в MySQL/MariaDB


AUTO_INCREMENT — это свойство поля таблицы, суть которого заключается в том, что при добавлении новых записей значение этого столбца устанавливается автоматически, причём значение равно на единицу больше предыдущего. Это свойство удобно применять для создания уникальных идентификаторов каждой строки — не нужно получать количество уже имеющихся строк или последнее значение, нет опасности запутаться.

Но у AUTO_INCREMENT есть одно интересное свойство — при удалении строк значение счётчика AUTO_INCREMENT не уменьшается. То есть, например, в таблицу было добавлено 10 записей, после этого все записи были удалены, а затем стали добавлять новые записи — в результате у первой новой записи значение столбца с AUTO_INCREMENT будет 11, у второй будет 12 и так далее.

Отсюда иногда возникает задача — сбросить значение AUTO_INCREMENT или установить AUTO_INCREMENT на единицу.

Сброс AUTO_INCREMENT для InnoDB баз данных

Если таблица не пустая, то для таких таблиц с движком хранения InnoDB невозможно установить AUTO_INCREMENT равное или меньшее значению в последней записи. Это вполне логично, поскольку в противном случае это привело бы к дублированию значения, которое должно быть уникальным для каждой строки.

Как сбросить AUTO_INCREMENT в phpMyAdmin

В веб интерфейсе phpMyAdmin вы можете просмотреть текущее значение AUTO_INCREMENT и изменить его. Для этого выберите таблицу, для которой вы хотите поменять AUTO_INCREMENT, затем перейдите в Операции → Параметры таблицы → AUTO_INCREMENT:

Установите желаемое значение — помните об ограничении для не пустых таблиц InnoDB.

Очистка таблицы и сброс AUTO_INCREMENT

Как уже было сказано, если удалить записи обычными средствами, то это удаление не сбрасывает значение AUTO_INCREMENT даже если удалены абсолютно все строки.

Выполнить удаление всех записей вместе со сбросом счётчика AUTO_INCREMENT можно командой:

TRUNCATE TABLE имяВашейТаблицы;

Изменение/сброс счётчика AUTO_INCREMENT без очистки таблицы

Другим вариантом является следующий SQL запрос:

ALTER TABLE имяВашейТаблицы AUTO_INCREMENT = 1

Обратите внимание, что AUTO_INCREMENT можно установить на любое значение — но помните об ограничении для непустых таблиц InnoDB.


И ещё очень важное замечание об ALTER TABLE. Дело в том, что этот запрос приводит к перестроению всей таблицы. То есть MySQL создаст новую таблицу с той же структурой и добавит новое значение auto_increment и скопирует все записи с оригинальной таблицы, удалит оригинальную таблицу и переименует новую. Если таблица содержит много записей, то этот процесс может затянуться очень надолго.

Если нужно увеличить значение счётчика AUTO_INCREMENT, то проще вставить ненужную строку (а затем удалить, если нужно). Это займёт долю секунды, в то время как ALTER TABLE может потребовать дни для больших таблиц.

Допустим, я у меня есть таблица со столбцом auto_increment ID и другими столбцами col1, col2…:

INSERT INTO имяВашейТаблицы SET ID = 10000000;
DELETE FROM имяВашейТаблицы WHERE ID = 10000000;

Как уменьшить значение AUTO_INCREMENT для InnoDB

Предположим, в таблице имеется 10 записей, при этом у последней записи столбец AUTO_INCREMENT равен 20. Как изменить значение этого столбца ID, чтобы следующая запись имела номер 11?

Это невозможно сделать ни одним из описанных выше методов, но есть другие техники, которые могут иметь сторонние эффекты — поэтому будьте весьма осторожны с ними.

Итак, первый вариант — убрать свойство AUTO_INCREMENT у столбца ID, выполнить запрос для переназначения ID, и затем вернуть AUTO_INCREMENT:

ALTER TABLE my_table MODIFY COLUMN ID INT(10) UNSIGNED;
COMMIT;
ALTER TABLE my_table MODIFY COLUMN ID INT(10) UNSIGNED AUTO_INCREMENT;
COMMIT;

Не нужно знать текущее максимальное значение. Этим способом счётчик автоматического прибавления будет сброшен и запущен автоматически с максимального существующего значения.

Но помните о медленной работе ALTER TABLE для больших таблиц.

Ещё один вариант — полностью удалить столбец id, а затем его вернуть с предыдущими настройками. Все поля внутри таблицы заполняются новыми значениями счётчика.


ALTER TABLE news_feed DROP id
ALTER TABLE news_feed ADD id BIGINT( 200 ) NOT NULL AUTO_INCREMENT FIRST ,ADD PRIMARY KEY (id)

Помните о том, что:

  • все поля внутри таблицы будут посчитаны заново и у них будут другие id
  • описанный метод полностью поломает foreign keys

Основывается на:


Рекомендуемые статьи:

1 Комментарий

  1. Вячеслав

    Добрый день!
    Спасибо за помощь!

Оставить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *