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:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SELECT mpy_id FROM members_payments WHERE mpy_id BETWEEN 1 AND 10; | |
| +———+ | |
| | mpy_id | | |
| +———+ | |
| | 1 | | |
| | 4 | | |
| | 6 | | |
| | 9 | | |
| | 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 file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 | | |
| | … | | |
| +———+ |
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!
Matt Mazur 
FYI: Github embed was fully missing in Feedly :(
Thanks, yeah, having code embeds that work across readers is something I haven’t mastered yet. Do you know of any better way to do it?
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 :).