MySQL database provides a wonderful feature of Autoincrement Column index. Your database table can define its primary key as Autoincrement number and MySQL will take care of its unique value while inserting new rows.
Each time you add a new row, MySQL increments the value automatically and persist it to table. But sometime you may want to reset the Autoincrement column value to 1. Say you writing a sample application and you have inserted few rows already in the table. Now you want to delete these rows and reset the autoincrement column to 1 so that new row which you insert will have primary key value 1.
There are few methods to achieve this.
1. Directly Reset Autoincrement Value
Alter table syntax provides a way to reset autoincrement column. Take a look at following example.
ALTER TABLE table_name AUTO_INCREMENT = 1;Code language: SQL (Structured Query Language) (sql)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.
2. Truncate Table
Truncate table automatically reset the Autoincrement values to 0.
TRUNCATE TABLE table_name;Code language: SQL (Structured Query Language) (sql)Use this with caution. When Truncation is used, it resets any AUTO_INCREMENT counter to zero. From MySQL 5.0.13 on, the AUTO_INCREMENT counter is reset to zero by TRUNCATE TABLE, regardless of whether there is a foreign key constraint.
Once TRUNCATE is fired, the table handler does not remember the last used AUTO_INCREMENT value, but starts counting from the beginning. This is true even for MyISAM and InnoDB, which normally do not reuse sequence values.
3. Drop & Recreate Table
This is another way of reseting autoincrement index. Although not very desirable.
DROP TABLE table_name;
CREATE TABLE table_name { ... };Code language: SQL (Structured Query Language) (sql)All these techniques are value techniques to reset autoincrement column number. Use whatever suits your requirement.
Disclaimer: The above commands can delete all your data! Be very very cautious.
Just curious to know if we can start the auto increment from the number that we desire rather than from 1
Yes you can do so. Specify whatever number you want to start the autoincrement columns value with in below query:
how can i set a autoincreament value is =001 in mysql table…..and it will starts like 001, 002 , 003
You can use the attributes unsigned zero fill and set the length of the integer. For example, you’ve set the integer length up to 5, then you also set the attribute unsigned zero fill, your auto-increment will look like this: 00001, 00002, 00003 and so on so forth. You can easily create one using phpMyAdmin or with this query: CREATE TABLE `db_name`.`tbl_name` (`col_name` INT(5) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT PRIMARY KEY);
Can U please help me, how do I Auto Re-arrange the AUTO_INCREMENT column after I delete a row from the table..
ALTER TABLE record AUTO_INCREMENT = 1
i tried this but if i deleted a row of id=5 and my last insert row was of an id=8
This method does not work at all it just remains as it is.
It does not rearrange at all.
So i see no point in using this if i have deleted a row from middle of my table.
it just work for my prob,,, thanks a ton
tnx a lot
So for a mysql table using innodb as the engine, is there a way to reset an autoincremented id back to 1.
Very helpful, thanks for the info!
nice work
Nice menu ! Would you send me the CSS and HTML codes ?
Best regards,
Walter.
Very helpful. Thanks for posting this information.
how it is work..
thanks a lot.
I m using Toad for MySQl freeware 6.7 and anyone table of my schema doesnt Works..
When i click in a table –> select to console Toad keep wait()….and i have to kill the process.
i verifyed that in tab host i have a message:
MySQL Table.Host does’t exitt. could be that?
Thank you .. Very useful to me…
.net prblem
Thank you …
Hello to Everyone, How To increment these Alphanumeric value
ABCDEFYY(Current year 14)YY(nextyear 15)00001
the result will appear like ABCDEF141500001
ABCDEF141500002…..and so on
plzzz help me…
hi
i have adde new rows with higher than regular autoincrement ID , ie it was near at 300 but i set it to 10000 to make test rows, that was mistake, and i wanted to know , whether i can use the IDs between 300 and 10000 . table is innodb. this article says “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” – and i have tested it with a test innodb table. and i have found out that even if i set the current incremental index to small value that is not used yet, if i add a new row without filling the auto increment id row in pma, it adds that to the index with the maximum use index+1 value. so even if innodb changes that without errors, it does not continue to work/function that way.
i have read the text incorrectly , i did not notice that it says “and the current sequence value is not changed”
Dude 1st method dosent work at all,it keep on incrementing the prevoiusly saved value.
There is no question of using 2nd and 3rd Method when you already have heaps of data in your database.
Is it possible to change the autoincrement when we have some rows starting from 1 and rightnow new entry will be 98 but i want that new entry will be 2501
Thanks so much! The first method worked for me – didn’t have to try the other two.
Hi I am getting error
odbc sql server driver violation of primary key constraint pk_tablename can not inser duplicate key in object dbo.tablename. the duplicate key value is (GI2, R-10000 )
Thanks
thanks
Thanks alot for zerofill