Identifying the Primary Keys for Deleted MySQL Records

At Help Scout today I had to perform an analysis that required me to identify the primary keys of deleted MySQL records.

For example, these results contain primary keys for records 1-5 but not 6-10:

The question is how to identify records 2, 3, 5, 7, and 8.

With the help of Stack Overflow, I found a neat solution that involves creating a temporary table of all possible primary keys, then left joining it on the original table to identify the missing ones. Here’s what it looks like, customized for my situation:

This solution does require that the keys be auto-incrementing, but that should be the case most of the time. Also, it assumes the last record is not deleted.

Happy querying!

4 thoughts on “Identifying the Primary Keys for Deleted MySQL Records

      • Never thought about it, probably only by having plain text/html as alternative… which kind of defeats the purpose of the embed in the first place. Maybe just a noscript tag with a plain link to the gist?

        • That’s clever and would probably work. I probably won’t just cause I don’t want to have to keep the plain text version and the embedded version in sync when I update the code (which I tend to do often). Stinks that there’s not a more seamless way to do it. Feedly will just have to start supporting Gist embeds :).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s