nickdnk nickdnk - 4 years ago 167
SQL Question

Inserting and selecting UUIDs as binary(16)

I don't understand why

SELECT UUID();


Returns something like:

3f06af63-a93c-11e4-9797-00505690773f


But if I insert it into a binary(16) field (the UUID() function) with for instance a BEFORE INSERT trigger and run a select, it returns something like:

0782ef48-a439-11


Note that these two UUIDs are not the same data.

I realize binary and an UUID string doesn't look identical, but shouldn't the selected data at least be just as long? Otherwise how can it possibly be equally likely to be unique?

Is it better to store it as char(36)? I just need it to be unique to prevent duplicate inserts. It is never selected or used for joins.

EDIT:

before trigger would be like:

BEGIN

if NEW.UUID IS NULL THEN

NEW.UUID = UUID();

END IF

END

Answer Source

So, as a response to comments. The correct way of storing a 36-char UUID as binary(16) is to perform the insert in a manner like:

INSERT INTO sometable (SOMECOLUMN,UUID) VALUES ("Something",UNHEX(REPLACE("3f06af63-a93c-11e4-9797-00505690773f", "-","")))

Unhex because an UUID is already a hexed value. This means retreiving the UUID can be done like:

SELECT HEX(UUID) FROM sometable;

Just in case someone comes across this thread and is unsure how this works.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download