How can I reset the AUTO_INCREMENT
of a field?
I want it to start counting from 1
again.
This question is related to
mysql
sql
auto-increment
The auto increment counter for a table can be (re)set in two ways:
By executing a query, like others already explained:
ALTER TABLE <table_name> AUTO_INCREMENT=<table_id>;
Using Workbench or other visual database design tool. I am gonna show in Workbench how it is done - but it shouldn't be much different in other tool as well. By right click over the desired table and choosing Alter table
from the context menu. On the bottom you can see all the available options for altering a table. Choose Options
and you will get this form:
Then just set the desired value in the field Auto increment
as shown in the image.
This will basically execute the query shown in the first option.
Adding an update because the functionality changed in MySQL 5.6. As of MySQL 5.6 you CAN use the simple ALTER TABLE with InnoDB:
ALTER TABLE tablename AUTO_INCREMENT = 1;
The docs are updated to reflect this:
http://dev.mysql.com/doc/refman/5.6/en/alter-table.html
My testing also shows that the table is NOT copied, the value is simply changed.
You can also use the syntax TRUNCATE
table like this :
TRUNCATE TABLE table_name
BEWARE!! TRUNCATE TABLE your_table
will delete everything in your your_table
!!
Simply like this:
ALTER TABLE tablename AUTO_INCREMENT = value;
reference: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html
ALTER TABLE news_feed DROP id
ALTER TABLE news_feed ADD id BIGINT( 200 ) NOT NULL AUTO_INCREMENT FIRST ,ADD PRIMARY KEY (id)
I used this in some of my scripts , the id field is droped and then added back with previous settings , all the existent fields within the database table are filled in with new auto increment values , this should also work with InnoDB .
Note that all the fields within the table will be recounted and will have other ids !!!.
Here is my solution, but I will not advise to do this if your column has constraints or is connected as foreign key to other tables as it would have bad effects or will not even work.
ALTER TABLE tbl_name DROP COLUMN column_id
ALTER TABLE tbl_access ADD COLUMN `access_id` int(10) NOT NULL PRIMARY KEY AUTO_INCREMENT FIRST
This works well!
SET @num := 0;
UPDATE your_table SET id = @num := (@num+1);
ALTER TABLE your_table AUTO_INCREMENT =1;
I think this will do it
To update latest plus one id
ALTER TABLE table_name AUTO_INCREMENT =
(SELECT (id+1) id FROM table_name order by id desc limit 1);
As of MySQL 5.6 the approach below works faster due to online DDL (note algorithm=inplace
):
alter table tablename auto_increment=1, algorithm=inplace;
ALTER TABLE tablename AUTO_INCREMENT = 1
The best solution that worked for me:
ALTER TABLE my_table MODIFY COLUMN ID INT(10) UNSIGNED;
COMMIT;
ALTER TABLE my_table MODIFY COLUMN ID INT(10) UNSIGNED AUTO_INCREMENT;
COMMIT;
Its 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.
You need to follow the advice from Miles M comment and here is a little code that fixes the range in mysql. Also u need to open up my.ini(Mysql) and change max_execution_time=60 to max_execution_time=6000; for large databases. Dont use "ALTER TABLE tablename AUTO_INCREMENT = 1" it will delete everything in your database.
$con=mysqli_connect($dbhost, $dbuser, $dbpass, $database);
$res=mysqli_query($con, "select * FROM data WHERE id LIKE id ORDER BY id ASC");
$count = 0;
while ($row = mysqli_fetch_array($res)){
$count++;
mysqli_query($con, "UPDATE data SET id='".$count."' WHERE id='".$row['id']."'");
}
echo 'Done reseting id';
mysqli_close($con);
it is for empty table:
ALTER TABLE `table_name` AUTO_INCREMENT = 1;
if you have data but you want to tidy up it, i recommend use this :
ALTER TABLE `table_name` DROP `auto_colmn`;
ALTER TABLE `table_name` ADD `auto_colmn` INT( {many you want} ) NOT NULL AUTO_INCREMENT FIRST ,ADD PRIMARY KEY (`auto_colmn`);
I googled and found this question, but the answer I am really looking for fulfils two criteria:
Since I couldn't find exactly what I want here, I have cobbled the answer from various answers and sharing it here.
Few things to note:
id
with type as int
as primary keycreate a stored procedure like this:
DELIMITER //
CREATE PROCEDURE reset_autoincrement(IN tablename varchar(200))
BEGIN
SET @get_next_inc = CONCAT('SELECT @next_inc := max(id) + 1 FROM ',tablename,';');
PREPARE stmt FROM @get_next_inc;
EXECUTE stmt;
SELECT @next_inc AS result;
DEALLOCATE PREPARE stmt;
set @alter_statement = concat('ALTER TABLE ', tablename, ' AUTO_INCREMENT = ', @next_inc, ';');
PREPARE stmt FROM @alter_statement;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //
DELIMITER ;
Then run it.
Before run, it looks like this when you look under Stored Procedures in your database.
When I run, I simply select the stored procedure and press Run Selection
Note: the delimiters part are crucial. Hence if you copy and paste from the top selected answers in this question, they tend not to work for this reason.
After I run, I should see the stored procedure
If you need to change the stored procedure, you need to delete the stored procedure, then select to run again.
This time you can simply use normal MySQL queries.
call reset_autoincrement('products');
Originally from my own SQL queries notes in https://simkimsia.com/library/sql-queries/#mysql-reset-autoinc and adapted for StackOverflow
There are good options given in How To Reset MySQL Autoincrement Column
Note that ALTER TABLE tablename AUTO_INCREMENT = value;
does not work for InnoDB
I suggest you to go to Query Browser and do the following:
Go to schemata and find the table you want to alter.
Right click and select copy create statement.
Open a result tab and paste the create statement their.
Go to the last line of the create statement and look for the Auto_Increment=N, (Where N is a current number for auto_increment field.)
Replace N with 1.
Press ctrl+enter.
Auto_increment should reset to one once you enter new row int the table.
I don't know what will happen if you try to add a row where an auto_increment field value already exist.
Best way is remove the field with AI and add it again with AI, works for all tables
I tried to alter the table and set auto_increment to 1 but it did not work. I resolved to delete the column name I was incrementing, then create a new column with your preferred name and set that new column to increment from the onset.
SET @num := 0;
UPDATE your_table SET id = @num := (@num+1);
ALTER TABLE your_table AUTO_INCREMENT =1;
You can simply truncate the table to reset the sequence
TRUNCATE TABLE TABLE_NAME
There is a very easy way with phpmyadmin under the "operations" tab, you can set, in the table options, autoincrement to the number you want.
Try Run This Query
ALTER TABLE tablename AUTO_INCREMENT = value;
Or Try This Query For The Reset Auto Increment
ALTER TABLE `tablename` CHANGE `id` `id` INT(10) UNSIGNED NOT NULL;
And Set Auto Increment Then Run This Query
ALTER TABLE `tablename` CHANGE `id` `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
Source: Stackoverflow.com