0

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.

webworm
  • 10,587
  • 33
  • 120
  • 217

1 Answers1

1

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.

invisal
  • 11,075
  • 4
  • 33
  • 54
  • My concern is that entries are also being made by another process and though unlikely the other process could insert a value that I could later conflict with. Hence my looking for a way to check first that the entry is not a duplicate. – webworm Jun 08 '13 at 15:58
  • which field that you do not want to have duplicate value? – invisal Jun 08 '13 at 16:02
  • is there any way to generalize what other system might input. At least some ranges of input that other system can input. – invisal Jun 08 '13 at 16:10
  • The field I do not want to duplicate is the one I am generating the `UUID_SHORT`number for. – webworm Jun 08 '13 at 16:11
  • but if other system also use UUID_SHORT, and you also use UUID_SHORT in the query. Then, there won't be any duplicate value. – invisal Jun 08 '13 at 16:13
  • I have no idea if the other system uses UUID_SHORT. Its a black box to me unfortunately. – webworm Jun 08 '13 at 16:22
  • Do you have any sample of other system input that we can generalize what is impossible for them to insert. How can other system ensure that they input is not duplicate? Can putting `UUID_SHORT` field as primary key a solution? – invisal Jun 08 '13 at 16:24
  • I have no control over what they insert hence why I need to check first – webworm Jun 08 '13 at 16:32
  • I think you mean [INSERT IGNORE vs INSERT … ON DUPLICATE KEY UPDATE](http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update). Those two commands check whether the row is existed before insert. – invisal Jun 08 '13 at 16:39