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:

SELECT mpy_id FROM members_payments WHERE mpy_id BETWEEN 1 AND 10;
+——+
| mpy_id |
+——+
| 1 |
| 4 |
| 6 |
| 9 |
| 10 |
+——+

view raw
missing-records.sql
hosted with ❤ by GitHub

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:

SET @mpy_id = (SELECT MAX(mpy_id) + 1 from members_payments);
DROP TABLE IF EXISTS members_payments_all_ids;
CREATE TEMPORARY TABLE members_payments_all_ids AS (
SELECT @mpy_id := @mpy_id 1 AS mpy_id
FROM members_payments mp1, members_payments mp2
WHERE @mpy_id > 1
);
SELECT members_payments_all_ids.mpy_id
FROM members_payments_all_ids
LEFT JOIN members_payments ON members_payments_all_ids.mpy_id = members_payments.mpy_id
WHERE members_payments.mpy_id IS NULL
ORDER BY members_payments_all_ids.mpy_id ASC
+——+
| mpy_id |
+——+
| 2 |
| 3 |
| 5 |
| 7 |
| 8 |
| … |
+——+

view raw
solution.sql
hosted with ❤ by GitHub

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s