0

I am using a MySQL database with a table containing random records. The only column that is interesting for my use case is a BIGINT column called "ID". This is also the primary key of that table, but it is not an AUTO-INCREMENT column and since this data is fetched from external sources, these IDs are not continuously.

Sample-Data from that Table:

[ID] 201101 201504 201641 201755 ...

I need to find an efficient way to find all the IDs that are NOT yet stored in that database within a specific range. For instance (pseudo):

GetUnusedIDs(RangeStart = 201100; RangeEnd = 201600); -> 201100 -> 201102 -> 201103 -> ... -> 201503 -> 201505 -> ... -> 201600

What I did so far was fetching all values within that range into a PHP-Array, then within a FOR-Loop from RangeStart to RangeEnd checking for each number if it is contained in that specific array and if not, adding it to a new array containing only the numbers that don't yet exist in the database.

I think there must be a better (more efficient) way to do this.

Thank you in advance!

Improve this question
1

1 Answer 1

Reset to default
0

You can do this within MySQL by creating the sequential integers in a seed table and checking which don't exist in your main table, be sure to filter the seed table by the minimum and maximum to increase performance, and alter the number of cross joins to fit the range

with seed as ( select null as n union all select null union all select null union all select null union all select null ), numbers as ( select row_number() OVER ( ORDER BY a.n ) + "YOUR_MIN_ID" n from seed a, seed b, seed c, seed d, seed e, seed f, seed g ) select n from numbers where not exists ( select null from Table where n = ID )
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.