This is a post about manually altering your auto_increment value in a MYSQL table. The solution was just obscure enough drive me crazy for a few minutes, so I figured it’s worth blogging for other DIY MYSQL intermediates (including myself) to stumble onto in the future.
The MYSQL query is:
ALTER TABLE Name of Table AUTO_INCREMENT = Next Value
If you don’t understand the query, or why you might use it, keep reading.
If you are working with a MYSQL table where each line has a numbered, automatically incremented field – which is probably your primary key – when you use INSERT to add a new entry the numbered, automatically incremented field will increase based on the previous highest value. If your last highest entry was 15, your newly inserted entry will be 16.
Sounds like what you want it to do, right?
Let’s say you just made three test entries – 16, 17, and 18 – but subsequently deleted them. The next time you INSERT the new row, your database – aware that the deleted 18 was the previous highest value – will INSERT the new row at 19. And, if you delete 19 – thinking it was a mistake – your next INSERT will be 20!
Typically you might not care about such things. Each of your WordPress posts and comments are numbered with an auto-incrementing primary key, but it doesn’t make a difference if a few numbers get skipped – WordPress knows how to deal with that gap.
However, what if your auto incremented number has meaning? In my case, the primary key represents the number of a song in my collection. My first song was 1, and I want to reuse those three deleted primary keys – my newest song should be 16 – not 19, 20, or the now-impending 21.
If you find yourself in this situation, delete all of your mistaken rows until you’re back to the last real row of information – in this case, 15 – and run the following SQL query:
ALTER TABLE Name of Table AUTO_INCREMENT = Next Value
In my example, the line is:
ALTER TABLE Songs AUTO_INCREMENT = 16
The query forced MYSQL to disregard our deleted previous higher numbers. Now the next time I insert information it will be correctly numbered as 16.
Yes, this means I am making revisions to my song database. It’s such a clever, useful thing – I’d like to share it with other artists who need a similar online control panel that allows them to keep all of their lyrics in one place. However, before I can do that I need to make it fully functional and exportable.
Today I’ve integrated a simple and ingenious login system, and the power to add new songs from the web rather than from the MYSQL panel (thus my auto-increment issue).
I don’t have a way to show you the backend, but you can see the resulting lyrics page, which displays only lyrics I have selected to be public. Each lyrics page is written dynamically as it draws its lyrics (and notes, if applicable) directly from my MYSQL databse.
I’m still wrestling with my tagging function – if anyone has experience building an tagging database associated with any sort of existing data I’d love to chat you up.
(Also, if you’re an artist interested in using my tool please leave a comment!)