MySQL: (Re)set the auto-increment value of a table
Sometimes it’s necessary to set the starting point of a MySQL auto-increment value.
Normally, MySQL starts auto-incrementing at 1. But let’s say you want to start at 10.000, because you want at least a five figure number. You can use the following query to set the MySQL auto-index:
ALTER TABLE some_table AUTO_INCREMENT=10000
If you want to delete all records from your table and restart auto-index at 1, you might be tempted to run a DELETE query, followed by the above example, setting the auto increment value to 1. There is a shortcut, however:
TRUNCATE TABLE some_table
This will basically reset the table, deleting all data and resetting the auto increment index. Do not that the truncate command is a hard-reset option. For instance, any triggers “ON DELETE” will not be fired when using truncate.
Find and Replace with a MySQL Query
There are times when you have a lot of data in a database (let’s say wp_posts for a Wordpress blog like Ariejan.net). When you need to find and replace certain strings, this can be a very tedious task. Find all posts containing the “needle” string and manually replace all these occurrences with “chocolate”. With about 200 posts, you can imagine how long this would take to do manually.
But, as I always say: “You’re a programmer! You should script the hell out of everything!”
So, I found this: MySQL has built-in support to find and replace! Just a simple query will do:
UPDATE wp_posts SET post_body = REPLACE(post_body, 'needle', 'chocolate');
That’s it. The entire table ‘wp_posts’ is searched and all occurrences of “needle” are replaced with “chocolate”. The query only took about a split second.