How To Reset MYSQL Table ID

Do you like this?

Summary:
This quick tutorial shows you how to reset MySQL tables so that the generated IDs of new entries can start from 0 or any number you want.


Content:

Software: you can use any software of your choice. I normally use PHPMyAdmin for MySQL so in this tutorial, I assume you are also using PHPMyAdmin.

I have encountered this situation several times because I usually use code from old projects as the base for new projects. In other words, I reuse my existing applications including their databases.

The problem is that the databases normally have data in it. In that case, even though I have removed existing entries, MySQL still gives the next ID but not from 0 or 1. For example, table T has 27 entries. After I remove all the 27 entries, the next entry will have the ID of 28 not from 1 as I want it to be.

The solution is:
1) Open PHPMyAdmin.
2) Click on the tables you want to reset the IDs.
3) Click on "Export" tab to get the SQL codes.
4) Copy only the "create table" codes. It will give you "create table" and "insert into" statements. You only need to have the "create table" codes.
5) Remove the tables.
6) Modify the "create table" codes you have already copied from step 4. Change the AUTO_INCREMENT to 0 or 1 depending on what ID you want for the first entry.
7) Run the SQL codes to re-create the former tables.

One example of the "create table" code:

CREATE TABLE IF NOT EXISTS `myproject_article` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(100) NOT NULL,
  `short_desc` longtext NOT NULL,
  `status` smallint(6) NOT NULL,
  `text` longtext NOT NULL,
  `url` varchar(100) NOT NULL,
  `createdDate` datetime NOT NULL,
  `image` varchar(500) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

 
comments powered by Disqus