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
BINARY(16) is therefore the most suitable MySQL data type. No character set, no collation, just 16 bytes. Furthermore, if GUID type is used for primary key, the gain is even greater, as in InnoDB the primary key value is copied into all secondary index values.
The MySQL function UUID_TO_BIN() is used to convert the valid string UUID of human-readable format to the binary UUID in a compact format. In contrast, BIN_TO_UUID() converts the binary UUID to the string UUID. The data type of the result generated by the UUID_TO_BIN() function is VARBINARY(16).
MySQL's BINARY data type, like CHAR , holds fixed-length strings. You still have to specify the width of the column, e.g. BINARY(20) . The only difference is that MySQL treats the data within the column as raw bytes rather than encoded text.
Universally Unique Identifiers, or UUIDS, are 128 bit numbers, composed of 16 octets and represented as 32 base-16 characters, that can be used to identify information across a computer system. This specification was originally created by Microsoft and standardized by both the IETF and ITU.
So, as a response to comments. The correct way to store a 36-char UUID as binary(16) is to perform the insert in a manner like:
INSERT INTO sometable (UUID) VALUES (UNHEX(REPLACE("3f06af63-a93c-11e4-9797-00505690773f", "-","")))
UNHEX
because an UUID is already a hexed value. We trim (REPLACE
) the dashes in the statement to bring the length down to 32 characters (our 16 bytes represented as HEX
). You can do this at any point before storing it, obviously, so it doesn't have to be handled by the database.
You may retrieve the UUID like this:
SELECT HEX(UUID) FROM sometable;
Just in case someone comes across this thread and is unsure how this works.
And remember: If you're selecting a row using the UUID, use UNHEX()
on the condition:
SELECT * FROM sometable WHERE UUID = UNHEX('3f06af63a93c11e4979700505690773f');
or literal notation (as mentioned by Alexis Wilke):
SELECT * FROM sometable WHERE UUID = 0x3f06af63a93c11e4979700505690773f;
And NOT HEX()
on the column:
SELECT * FROM sometable WHERE HEX(UUID) = '3f06af63a93c11e4979700505690773f';
The last solution, while it works, requires that MySQL HEX
es all UUIDs before it can determine which rows match. It's very inefficient.
Edit: If you're using MySQL 8 you should have a look at the UUID functions as mentioned in SlyDave's answer. This answer is still correct, but it doesn't optimise the UUID indexes which can be done natively using those functions. If you're on < MySQL 8 you can implement Devon's polyfill, which provides identical functionality on previous versions of MySQL.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With