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 responses to “Updating Auto-Increment in MySQL”
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!
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.
It’s worth making clear that the truncate statement will completely clear the table of data – wouldn’t want to run it by mistake!
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”);
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.
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.
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.