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.