• Skip to primary navigation
  • Skip to content
  • Skip to primary sidebar

Crushing Krisis

The Newest Oldest Blog In New Zealand

  • Archive
  • DC Guides
    • DC New 52
    • DC Events
    • DC Rebirth
    • Batman Guide
  • Marvel Guides
    • Omnibus & Oversize Hardcover DB
    • Marvel Events
  • Star Wars Guide
    • Expanded Universe Comics (2015 – present)
    • Legends Comics (1977 – 2014)
  • Valiant Guides
  • Contact!
You are here: Home / Personal / topics / day in the life / Revising your auto_increment in MYSQL

Revising your auto_increment in MYSQL

April 20, 2008 by krisis

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!)

Related posts:

  1. How to turn off post revisions in WordPress 3.3
  2. tuesday morning brain
  3. Making Music Work: Write It Down, Pt. 1
  4. #MusicMonday: “Live and Die” – The Avett Brothers
  5. into the toddler groove
  6. Track-by-Track: Lady Gaga’s Joanne – “Come to Mama” (Track 09)

Filed Under: day in the life, webdesign

Previous Post: « Week-Weary
Next Post: How To Edit Your MySpace Music Profile »

Reader Interactions

Primary Sidebar


Support Crushing Krisis on Patreon
Support CK
on Patreon


Follow me on Twitter Like me on Facebook Contact me
Follow me on Instagram Watch me on Youtube Subscribe to the CK RSS Feed

About CK

About Crushing Krisis
About My Music
About Your Author
Blog Archive
Comics Blogs Only
Contact Krisis
Terms & Conditions

Crushing Comics

Marvel Comics
Marvel Events Guide
Marvel Omnibus Guide
Spider-Man Guide

DC Comics
DC New 52
DC Rebirth

Valiant Comics

Copyright © 2017 · Foodie Pro Theme by Shay Bocks · Built on the Genesis Framework · Powered by WordPress

Crushing Krisis is supported by SuperHeroic Sponsor Omnibuds' Café


Links from Crushing Krisis to retailer websites may be in the form of affiliate links. If you purchase through an affiliate link I will receive a minor credit as your referrer. My credit does not affect your purchase price. Affiliate programs and affiliations include, but are not limited to: Amazon Services LLC Associates Program (in the US, UK, Canada, France, Germany, Italy, and Spain), eBay Partner Network, and iTunes Affiliate Program. Note that URLs including the "geni.us" domain name are affiliate short-links.