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

Crushing Krisis

Comic Books, Drag Race, & Life in New Zealand

  • DC Guides
    • DC Events
    • DC New 52
    • DC Rebirth
    • Batman Guide
    • The Sandman Universe
  • Marvel Guides
    • Marvel Events
    • Captain America Guide
    • Iron Man Guide
    • Spider-Man Guide (1963-2018)
    • Spider-Man Guide (2018-Present)
    • Thor Guide
    • X-Men Reading Order
  • Indie & Licensed Comics
    • Spawn
    • Star Wars Guide
      • Expanded Universe Comics (2015 – present)
      • Legends Comics (1977 – 2014)
    • Valiant Guides
  • Drag
    • Canada’s Drag Race
    • Drag Race Belgique
    • Drag Race Down Under
    • Drag Race Sverige (Sweden)
    • Drag Race France
    • Drag Race Philippines
    • Dragula
    • RuPaul’s Drag Race
    • RuPaul’s Drag Race All Stars
  • Archive
  • 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. into the toddler groove
  2. 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 »

Primary Sidebar


Support Crushing Krisis on Patreon
Support CK
on Patreon


Follow me on Twitter Contact me 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

Spider-Man Guide

DC Comics

  • New Comics & Collected Editions: Marvel Comics – September 20, 2023
    Catch up on newly-released comic books and collected editions from Marvel Comics September 20 2023, with guides to every title & character! […]
  • New for Patrons: Guide to Monica Rambeau – Photon, Spectrum, & Captain Marvel
    It's time to shine a light on a Guide to Monica Rambeau. She's been Photon, Spectrum, & even Captain Marvel. Read EVERY issue in order. […]
  • Guide to Avengers (1963-1996)Updated: Guide to Avengers (1963-1996)
    Happy Anniversary to Marvel's Avengers! To celebrate I rebuilt my definitive guide to Avengers (1963-1996), including EVERY issue & collection. […]
  • Silver Age X-Men GuideUpdated: Silver Age X-Men Guide
    I've updated my definitive Silver Age X-Men Guide to collected editions of the earliest X-Men comics, including a new Penguin Classics & Taschen editions. […]
  • Guide to Conan the BarbarianUpdated: Guide to Conan The Barbarian
    I've overhauled my Guide to Conan the Barbarian to celebrate the release of Titan Comics' new Conan the Barbarian series written by Jim Zub. […]
  • Avengers Inc. (2023) #1 by Al Ewing & Leonard Kirk, released by Marvel Comics September 13 2023New Comics & Collected Editions: Marvel Comics – September 13, 2023
    Catch up on newly-released comic books and collected editions from Marvel Comics September 13 2023, with guides to every title & character! […]
  • Guide to Birds of PreyNew for Patrons: Guide to Birds of Prey
    Who are the Birds of Prey? Learn what cast members define one of DC's signature team with my Guide to Birds of Prey! […]
  • Fantastic Four (2022) #11 released by Marvel Comics September 6 2023New Comics & Collected Editions: Marvel Comics – September 6, 2023
    Catch up on newly-released comic books and collected editions from Marvel Comics September 6 2023, with guides to every title & character! […]
  • It’s gonna be May! Oh, wait, it’s ALREADY May…
    Hello, friends! Unfortunately, my non-CK life and […]
  • Drax GuideDrax Guide – now available to the public!
    Learn how the MCU merged several incarnations Drax into Dave Bautista's hyper-literal warrior with a vendetta agains Thanos in my Drax Guide! […]
  • Guide to GamoraGamora Guide – now available to the public!
    My Gamora Guide will help you find every Marvel comic starring Thanos's adopted daughter and the most dangerous woman in the galaxy! […]
  • Drag Race España Season 3, Episode 2 – “Drag Vision” Review & Power Rankings
    The queens of Drag Race España Season 3 make a collective stumble in this "Drag Vision" choreography challenge, earning the ire of the judges (though it's the kindest ire you'll ever see). […]
  • Star-Lord GuideStar-Lord Guide – now available to the public!
    Get ready for Guardians of the Galaxy Vol. 3 with my Star-Lord Guide, including every comics appearance of Marvel's TWO distinct Star-Lords! […]
  • Drag Race Sverige Season 1, Episode 8 – Sweden Grand Finale, “Queen Delicious” runway, & season retrospective
    It's the Drag Race Sweden Grand Finale! Our final three queens put their spin on an original song and walk a final "Queen Delicious" runway alongside their eliminated peers. […]

Content Copyright ©2000-2023 Krisis Productions

Crushing Krisis participates in affiliate programs including (but 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. If you make a qualifying purchase through an affiliate link I may receive a commission.