CODEWITHSUNDEEP
mysqluuiduniqueidentifier
webworm
webworm

Reputation: 11019

MySQL - Insert UUID as BIGINT yet check if unique

I am trying to create a unique identifier that can be stored as a BIGINT within a table.

I am working with the pre-existing table that uses a BIGINT for a unique identifier. The database itself does not generate the identifier rather an external program that inserts the values does this. I need to insert my own values into this table and in doing so need to generate a unique identifier that is BIGINT(24).

I was thinking of using SELECT UTC_TIMESTAMP() + 0 but microseconds are not included in the version of MySQL I am using (out of my control).

So after some research it looks as though SELECT UUID_Short() would be the better way to go. However I am concerned that the precision wont be enough.

If I use SELECT UUID_SHORT() Is there a way to check , before insertion, that the value from UUID_Short does not already exist in the table? I should mention that I would really like to do all this in SQL so I can create an event in the database that will run once every 24 hours. The number of inserts each time the event runs is about 60 records so I don't believe performance will be an issue.

Upvotes: 0

Views: 1255

Answers (1)

invisal
invisal

Reputation: 11171

This is how UUID_SHORT() value constructed

  (server_id & 255) << 56
+ (server_startup_time_in_seconds << 24)
+ incremented_variable++;

which guarantee to create an unique if you do not run it more than 2^24 = 16,777,216 per server startup. If you restart your MySQL, incremented_variable is reset, but your server_startup_time_in_seconds increase so big that it will not produce duplicated value with previous UUID_SHORT() that has been called on the previous MySQL startup.

Upvotes: 1

Related Questions