How To Specify An AUTO_INCREMENT Value in MySQL

I needed to reset the value of an auto_increment number after doing a bunch of tests in a MySQL database. I just wanted the number to be more accurate & reflect more closely the number of actual items in the database, since every deletion just leaves a "hole", as the incremented value doesn't reset after a delete.

This is a real quick solution, so here goes:

Problem
I've deleted a bunch of items in a database with an auto_increment field in one of the tables. (In my case, I did a ton of testing with importing/creating new posts on a Drupal site, and deleted them all to start fresh with the "real" content. In Drupal, new nodes just keep incrementing after those deletions, so my node values where way off... I wanted to align them more correctly with the new "real" import.)

Solution
Run a simple SQL command on the particular table with the auto_increment to set the value where you want it.

ALTER TABLE this_is_the_table AUTO_INCREMENT = 100;

That's it! I won't go into specific Drupal issues with doing this on the 'nid' field of your nodes table, but mainly, you should be careful to make this new value higher than the number of nodes you currently have in the database.

It's possible that ALTER will actually count the rows in the table and just +1 the value if you have fewer than the number you're asking for. IE: you have 110 rows already, but run the above command... I think it will just set the auto_increment value to 111, instead of failing. (Do your own research on that, though... I'm too lazy right now to do it for you. :P )

Cheers!

(BTW, how do you like the ultra-spiffy new code highlighting??!? Awesome how it sets up links to research the keywords, I thought. I'm going to have fun with that puppy. :)