Updating Auto-Increment in MySQL »
I was playing with the database yesterday, and decided that some of my auto-incremented values (id numbers for particular tables, specifically Movable Type's PluginData) were simply getting too high.
I was curious about resetting them. Even if I deleted all the rows in a table, the value wouldn't reset. So I went looking for the answer.
The simplest way to reset this is if you make use of phpMyAdmin (of course, you have to have access to phpMyAdmin).
To do so, simply select the table, then click the operations tab, and set the value there. This is especially useful if you're like myself and simply don't use SQL enough to have the storage space for something else that you won't use very often.
If you don't have phpMyAdmin, however, you may need to use some command-line SQL. In that case, I had to look a little further.
I finally found this useful article at ScriptyGoddess, where I found a couple ways to accomplish this task.
The first is a simple SQL statement that will do the trick:
TRUNCATE TABLE (TABLENAME)
The problem with this is that it clears your data! Luckily, there is also another SQL statement that you can use:
ALTER TABLE tbl_name AUTO_INCREMENT = xxx
Where the value in xxx is an integer (12, 144, whatever).
Comments (7)
I faily comfortable with Microsoft SQL2000, but I haven't played with the MySQL database. Mostly I just don't know where to enter and run SQL statements. In SQL2000, I use the query analyzer.
Posted by Ted on January 6, 2005 6:57 AM
I think there might be a GUI of some sort fo MySQL, if you're running under Windows, but I'm not sure. If you have MySQL on your host, as many people do, then you'll often also have phpMyAdmin, which you can use for the task. Simply select the database, then the table, then you have an operations tab, which has an option there for setting the auto-increment value.
Posted by Chad Everett on January 6, 2005 7:47 AM
I think the point of researching this subject is to reset the values automagically, while the script is running. My reason for googling here is similar, i wrote a script which parses several files. When the script runs, it first deletes all existing data, and then ads the new stuff; however, as was stated, the auto incriment key gets a bit out of hand. so it will be nice to run this query after the delete queries to clean it up and keep the numbers a bit more realistic.
As far as mysql GUI, the best i've found is premiumsoft Navicat, newest version offers support for stored procs.
Posted by Sean Benoit on February 3, 2005 5:08 PM
There are a few ways you can keep your auto_increment values from getting out of hand. Here is one*using the pear db_object to connet*:
$getnumRow = $db_object->query("SELECT idFROM table");
$newID = 1;
$useLastRow = $getnumRow->numRows();
for($i = 1;$i fetchRow();
$cleanup = $db_object->query("UPDATE table SET id=$newID WHERE id='".$usenumRow['imgID']."'");
$newID++;
}
--$newID;
$setAT = $db_object->query("ALTER TABLE table AUTO_INCREMENT=$newID");
Posted by Robert on February 8, 2005 12:08 AM
It's worth making clear that the truncate statement will completely clear the table of data - wouldn't want to run it by mistake!
Posted by Tim on May 5, 2006 10:31 PM
Thanks for the tip! Found this page on Google and used phpMyAdmin to change the auto-increment field from a table I had imported that had imported the previous auto-increment id.
Posted by Michael @ SEOG on July 12, 2006 12:48 AM
Thanks for letting me know that, but your black bars with the code in it. Man, I couldn't find the code at first! They look like dividers!
Posted by Adam on May 6, 2008 1:18 PM