resetting ID incremental field with phpMyAdmin

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
fariquzeli
Forum Contributor
Posts: 144
Joined: Mon Jun 24, 2002 9:16 am
Location: Chicago
Contact:

resetting ID incremental field with phpMyAdmin

Post by fariquzeli »

I've been deleting ID's and test records from my tables, I was just wondering how to get it to restart at 0. Cause now with an empty table my first record is starting at like 13, and so on.

any help?
calebsg
Forum Commoner
Posts: 28
Joined: Tue Jun 18, 2002 10:41 am

Post by calebsg »

saw this on mysql.com the other day:
I finally figured out how to reset the AUTO_INCREMENT counter: ALTER TABLE tbl_name AUTO_INCREMENT = 1;
see if that works
fariquzeli
Forum Contributor
Posts: 144
Joined: Mon Jun 24, 2002 9:16 am
Location: Chicago
Contact:

Post by fariquzeli »

thanks a bunch, works great.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

If you're deleting all the information from a table, instead of doing it row by row just empty the data instead.

Code: Select all

DELETE FROM table
Then the auto-increment field is automatically reset.

Mac
EricS
Forum Contributor
Posts: 183
Joined: Thu Jul 11, 2002 12:02 am
Location: Atlanta, Ga

Auto_Increment rules

Post by EricS »

Prior to MySQL 3.23
Auto_Increment would always increment from the highest value found in the table. So deleting all the rows one-by-one or all at once would reset the counter.

As of MySQL 3.23
Auto_Increment in only forward sequential and will not reuse previous values even when they are erased. You must do a DELETE FROM tbl_name. This is because this command not only erases the rows but RESETS the table as well, which resets the counter.

FFR
Post Reply