19

I know that in MySQL at the command line I can reset a table's auto-increment field to 1 with this:

ALTER TABLE tablename AUTO_INCREMENT = 1

I am curious if there is a way to do this from within phpMyAdmin. Something like a check box to reset the auto-increment or something else along those lines?

Not that there is anything wrong with the command line approach. More one of those curiosity things I keep thinking on... Thanks in advance!

19

phpmyadmin

Perhaps you could just select the phpMyAdmin Operations tab:

  • In phpMyAdmin, click on the table you want to reset or change the AUTO_INCREMENT value
  • Click on the Operations Tab
  • In the Table Options box find the auto_increment field.
  • Enter the new auto_increment starting value
  • Click on the Go button for the Table Options box.

Since this one of the most frequently asked questions for phpmyadmin, you can learn more about this in this blog : http://trebleclick.blogspot.com/2009/01/mysql-set-auto-increment-in-phpmyadmin.html

Supplemental Info

For an empty table, another way to reset the auto_increment attribute is to run

TRUNCATE TABLE mydb.tablename;

Don't run this if you have data in it. If you want to hose the data, then be my guest.

In phpmyadmin, just click the SQL tab, enter the command, and run it.

For a nonempty table, you may want to adjust the auto_increment attribute to the highest existing id in use in case higher entries were deleted.

First, optimize the table

OPTIMIZE TABLE mydb.mytable;

Next, locate the highest value for the auto_increment column (say it is id)

SELECT MAX(id) maxid FROM mydb.mytable;

Suppose the answer returns 27. Goto the Operations tab and enter 28.

  • +1 for truncate, I've been dropping and recreating. – mckenzm Nov 19 at 16:28
  • @mckenzm Under the hood, TRUNCATE TABLE is dropping and recreating (dev.mysql.com/doc/refman/5.6/en/…) – RolandoMySQLDBA Nov 19 at 18:08
  • @mckenzm I just read your profile. 25 years of COBOL ??? You poor tormented soul. – RolandoMySQLDBA Nov 19 at 18:10
  • TBH it was probably 13 x the same two years... and a lot of it was simple, the embedded SQL, Command Level CICS,, VMS System calls...Preprocessed macros. Lego stuff. – mckenzm Nov 19 at 22:46
11

As a supplement to the other answer(s), I'd prefer a more visual approach:

  1. Click the table you want to change.

  2. Click Operations:

enter image description here

  1. Look under "Table options":

enter image description here

Also note that:

–§–  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.

  • 1
    I still use phpMyAdmin 4.0.1-rc1. I guess it is too old, since I have no AUTO_INCREMENT option on that page. – Kar.ma Mar 27 at 15:05
-2
SET @num := 0;
UPDATE tablename SET id = @num := (@num+1);
ALTER TABLE tablename AUTO_INCREMENT = 1;
  • 2
    And what about all the related data? – Colin 't Hart Aug 19 at 7:46

Not the answer you're looking for? Browse other questions tagged or ask your own question.