How to reset auto-increment values in MySQL
MySQL provides you with a useful function called auto increment. You can assign the AUTO_INCREMENT attribute to the table column to create a unique identifier for the new row. Generally, you use the AUTO_INCREMENT attribute for the primary table key column.
Whenever you insert a new row into a table, MySQL uses the AUTO_INCREMENT attribute to automatically assign an ordinal number to the column.
For example, if there are eight rows in a table and you insert a new row without specifying a value for the autoincrement column, MySQL will automatically insert a new id row with a value of 9.
Sometimes you may need to reset the autoincrement column value so that the ID of the first record you insert into the table starts with a certain number, for example, 1.
In MySQL, you can reset the auto-increment values in various ways.
EXAMPLES OF RESETTING THE VALUE OF AUTOMATIC INCREMENTS IN MYSQL
First, create a table with the name tmp and assign the attribute AUTO_INCREMENT to the id column of the primary key.
CREATE TABLE tmp (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(45) DEFAULT NULL,
PRIMARY KEY (id)
);
Second, insert a sample data in the tmp table:
Thirdly, a query to the tmp table to check the insertion operation:
We have three rows with column ID values: 1, 2, and 3. Perfect! It’s time to practice resetting the auto-increment value of the ID column.
USING ALTER TABLE INSTRUCTION
You can reset the auto-increment value with the ALTER TABLE operator. The syntax of the ALTER TABLE operator to reset the autoincrement is as follows:
ALTER TABLE table_name AUTO_INCREMENT = value;
You specify the name of the table after ALTER TABLE and the value you want to reset in the expression AUTO_INCREMENT=value.
Note that the value must be greater than or equal to the current maximum value of the auto-increment column.
Let’s delete the last entry in the tmp table with id value 3:
If you insert a new row, MySQL will assign 4 columns id of the new row. However, you can reset the number generated by MySQL to 3 using the following ALTER TABLE instruction:
ALTER TABLE tmp AUTO_INCREMENT = 3;
Now let’s try to insert a new line in the tmp table and request data from it to see the effect:
We have three lines with the last autoincrement value of 3 instead of 4, which is what we expected.
USING TRUNCATE TABLE OPERATOR
The TRUNCATE TABLE operator deletes all data from the table and resets the auto-increment value to zero.
The following illustrates the syntax of the TRUNCATE TABLE operator:
Using the TRUNCATE TABLE operator, you remove all data from the table forever and reset the auto-increment value to zero.
USING DROP TABLE AND CREATE TABLE OPERATORS
You can use a pair of operators: DROP TABLE and CREATE TABLE to reset the auto-increment column. Note that this method removes all data from the table forever.
Like the TRUNCATE TABLE operator, these operators delete the table and recreate it, so the autoincrement value is reset to zero.
DROP TABLE table_name;
CREATE TABLE table_name(. );
In this article, you have learned how to reset the autoincrement value in MySQL in various ways. The first method is preferable because it is the simplest and has no side effects.
Как сбросить 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 можно командой:
Изменение/сброс счётчика AUTO_INCREMENT без очистки таблицы
Другим вариантом является следующий SQL запрос:
Обратите внимание, что AUTO_INCREMENT можно установить на любое значение — но помните об ограничении для непустых таблиц InnoDB.
И ещё очень важное замечание об ALTER TABLE. Дело в том, что этот запрос приводит к перестроению всей таблицы. То есть MySQL создаст новую таблицу с той же структурой и добавит новое значение auto_increment и скопирует все записи с оригинальной таблицы, удалит оригинальную таблицу и переименует новую. Если таблица содержит много записей, то этот процесс может затянуться очень надолго.
Если нужно увеличить значение счётчика AUTO_INCREMENT, то проще вставить ненужную строку (а затем удалить, если нужно). Это займёт долю секунды, в то время как ALTER TABLE может потребовать дни для больших таблиц.
Допустим, я у меня есть таблица со столбцом auto_increment ID и другими столбцами col1, col2…:
Как уменьшить значение AUTO_INCREMENT для InnoDB
Предположим, в таблице имеется 10 записей, при этом у последней записи столбец AUTO_INCREMENT равен 20. Как изменить значение этого столбца ID, чтобы следующая запись имела номер 11?
Это невозможно сделать ни одним из описанных выше методов, но есть другие техники, которые могут иметь сторонние эффекты — поэтому будьте весьма осторожны с ними.
Итак, первый вариант — убрать свойство AUTO_INCREMENT у столбца ID, выполнить запрос для переназначения ID, и затем вернуть AUTO_INCREMENT:
Не нужно знать текущее максимальное значение. Этим способом счётчик автоматического прибавления будет сброшен и запущен автоматически с максимального существующего значения.
Но помните о медленной работе ALTER TABLE для больших таблиц.
Ещё один вариант — полностью удалить столбец id, а затем его вернуть с предыдущими настройками. Все поля внутри таблицы заполняются новыми значениями счётчика.
How to reset AUTO_INCREMENT in MySQL
How can I reset the AUTO_INCREMENT of a field?
I want it to start counting from 1 again.
25 Answers 25
You can reset the counter with:
For InnoDB you cannot set the auto_increment value lower or equal to the highest current index. (quote from ViralPatel):
Note that you cannot reset the counter to a value less than or equal to any that have already been used. For MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum plus one. For InnoDB, if the value is less than the current maximum value in the column, no error occurs and the current sequence value is not changed.
Simply like this:
There is a very easy way with phpMyAdmin under the "operations" tab. In the table options you can set autoincrement to the number you want.
The best solution that worked for me:
It’s fast, works with InnoDB, and I don’t need to know the current maximum value!
This way. the auto increment counter will reset and it will start automatically from the maximum value exists.
The highest rated answers to this question all recommend «ALTER yourtable AUTO_INCREMENT= value». However, this only works when value in the alter is greater than the current max value of the autoincrement column. According to the MySQL 8 documentation:
You cannot reset the counter to a value less than or equal to the value that is currently in use. For both InnoDB and MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum AUTO_INCREMENT column value plus one.
In essence, you can only alter AUTO_INCREMENT to increase the value of the autoincrement column, not reset it to 1, as the OP asks in the second part of the question. For options that actually allow you set the AUTO_INCREMENT downward from its current max, take a look at Reorder / reset auto increment primary key.
Reset AUTO INCREMENT in MySQL
AUTO_INCREMENT is a useful feature provided in MySQL. It works as follows: it generates a numerical sequence to a column for every new row inserted.
AUTO_INCREMENT generates unique numbers for identification purposes.
We can manually define the starting of this sequence set by auto_increment and reset this value to some other value later. Whenever we reset the auto_increment column’s value to a new value, the sequence is reset again so that the next automatically generated value follows succession.
In this article we will look into:
We will start by creating a table, inserting some data into it, and then do a select * to view the inserted data.
Output :-
figure 1.1
How to reset AUTO_INCREMENT in MySQL
We will look into the syntax followed by an example.
Syntax:-
Where <table_name> is the name of the table you want to reset the auto increment for a column.<value> is the value you want to reset for the sequence.
Read More:
Let us have a look at the example. We will be resetting the auto_increment value for the above-created table sales_data to start from 1000.
We get the message : ALTER TABLE sales_data AUTO_INCREMENT = 1000 0 row(s) affected Records: 0 Duplicates: 0 Warnings: 0 0.016 sec
Now insert a few more rows and do a select * on sales_data to see the change.
Output :-
figure 1.2
As we can see in figure 1.2, we were able to reset the auto_increment value successfully, and now all the new rows will be having sale_person_id to be incremented by one starting from 1000.
Truncate table reset AUTO_INCREMENT in MySQL
What if you want to remove all the previous data from a table and then reset the auto_increment value?
The solution is to truncate the table and then set the desired auto_increment value. Observe the below code and output.
We will again insert some data and select * on the sales_data table to see the change.
Output:-
figure 1.3
Note: We can change the auto_increment value to any integer here and not just 1.
Reset AUTO_INCREMENT by drop column in MySQL
Another easy way to reset the auto_increment value is to drop columns and reset the value. In the below example, we will be using the table sales_data created above with rows, as shown in figure 1.1. Observe the below query.
In the above queries, we are dropping the sale_person_id column itself and then again adding the column, making it the primary key and setting its auto_increment value to start from ‘1’.
Reset AUTO_INCREMENT after Delete in MySQL
What if the most recent entries get deleted, and we would like to reset the auto_increment value?
We can reset the auto_increment value back to start just after the row’s last id in the table. For example, if the last row had the value of id as ‘7’ and rows with id ‘8’ and ‘9’ were deleted. Inserting a new row to the table will provide the value ’10’ to id unless we execute the below code.
After executing this line, the id of new rows will start from ‘7’ instead of ’10’.
Have a detailed understanding on how to reset auto_increment with examples from : RESET AUTO_INCREMENT AFTER DELETE.
How to reset AUTO_INCREMENT in MySQL workbench
In case we do not want to write queries, we can also set the auto_increment value using the MySQL workbench, we can do it by the below steps.
STEP1: Right-click on the table and select the option Alter Table.
figure 1.4
STEP2: Select the Options button.
figure 1.5
STEP3: Set the value for Auto Increment and click Apply.
figure 1.6