You’ve probably noticed that the AUTO_INCREMENT ID of a column doesn’t change after deleting rows from a table, and this may not be what you want in some situations (maybe you are testing an application, and you want ID’s to reset from 1, rather than the last available ID).
First solution would be to use TRUNCATE when deleting the rows from the table, instead of doing a row by row deletion (which is also slower in this situation). To achieve this, just type:
TRUNCATE TABLE t1;
This automatically rests the auto_increment id of any column which was set to automatically increment the last row’s value when a new row is inserted, no matter if the table (in case you’re using MySQL) is using MyISAM or InnoDB storage engines.
Another way to achieve this is by throwing the ALTER TABLE command:
ALTER TABLE t1 AUTO_INCREMENT=1;
While this solution is also great, it requires two WRITE operations over the t1 table:
- you must delete the content
- reset the auto_increment ID.
Because writes are expensive for high load databases, using TRUNCATE is highly recommended.
There would be a third option to achieve this, and just like the above ones, you may not have enough permissions to throw it in the command line: dropping the table, and adding it again.
This of course, will clean everything related to that table, including the auto_increment ID.
Here’s how it goes:
INSERT INTO t1 (c2) VALUES ('IE');
INSERT INTO t1 (c2) VALUES ('UK');
SHOW TABLE STATUS LIKE 't1';
After throwing the SHOW TABLE statement, we just look for the “auto_increment” value which should be 3.
Let’s reset the auto_increment value:
DROP TABLE t1;
CREATE TABLE t1 (
`c1` SMALLINT(2) unsigned auto_increment NOT NULL,
`c2` char(2) NOT NULL,
PRIMARY KEY (`c1`)
) ENGINE="MyISAM";
SHOW TABLE STATUS LIKE 't1';
Now, the value of the auto_increment index is 1 again.
My conclusion: to reset the auto_increment index to 1, just go with the TRUNCATE command. It’s faster, especially if there’s content inside the table.
