Insert and select UUID as binary (16)

Question

I do not understand why

SELECT UUID(); 

It returns something like:

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

But if I insert it into a binary (16) field (UUID () function), for example, before a BEFORE INSERT trigger and fires select, it returns something like:

 0782ef48-a439-11 

Please note that these two UUIDs are not the same data.

I understand the binary and the UUID string does not look identical, but shouldn't the selected data at least be as long? Otherwise, perhaps it can be as unique as possible?

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 connections.

EDIT:

before the trigger fires:

 BEGIN if NEW.UUID IS NULL THEN NEW.UUID = UUID(); END IF END 

Answer 1

So, as an answer to the comments. The correct way to store a 36-character UUID in binary (16) is to insert as follows:

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

UNHEX because the UUID is already a hexadecimal value. We trim the REPLACE dash in the statement to reduce the length to 32 ASCII characters (our 16 bytes are represented as HEX ). You can do this at any time before saving, obviously, so this should not be handled by the database.

You can get the UUID as follows:

 SELECT HEX(UUID) FROM sometable; 

Just in case, if someone comes across this topic and is not sure how it works.

And remember: if you select a row using a UUID, use UNHEX() provided :

 SELECT * FROM sometable WHERE UUID = UNHEX('3f06af63a93c11e4979700505690773f'); 

And not HEX() in the column:

 SELECT * FROM sometable WHERE HEX(UUID) = '3f06af63a93c11e4979700505690773f'; 

The second solution, although it works, requires MySQL HEX evaluate all the UUIDs before it can determine which rows match. It is very inefficient.

Change: If you are using MySQL 8, you should take a look at the UUID functions as indicated in the SlyDave answer. This answer is still correct, but it does not optimize the UUIDs that can be made using these functions.

Answer 2

Starting with MySQL 8, you can use two new UUID functions:

• BIN_TO_UUID

   SELECT BIN_TO_UUID(uuid, true) AS uuid FROM foo; -- 3f06af63-a93c-11e4-9797-00505690773f 

• UUID_TO_BIN

   INSERT INTO foo (uuid) VALUES (UUID_TO_BIN('3f06af63-a93c-11e4-9797-00505690773f', true)); 

This method also supports the permutation of the temporary uuid component to improve indexing performance (ordering it in chronological order), just set the second argument to true - this only works for UUID1.

If you use the true flag on UUID_TO_BIN to index performance (recommended), you must also set it to BIN_TO_UUID , otherwise it will not be converted back properly.

See the documentation for more details.

• https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_uuid-to-bin
• http://mysqlserverteam.com/mysql-8-0-uuid-support/

Answer 3

Other answers are correct. The UUID() function returns a 36-character string that must be converted using the functions shown ( UNHEX() or, on newer platforms, UUID_TO_BIN() ).

However, if you use your own software to create your UUIDs, you can use the hexadecimal letter instead.

Therefore, I would use the following with the MySQL UUID() function:

 INSERT INTO sometable (id) VALUES (UNHEX(REPLACE(UUID(), '-', ''))); -- all versions INSERT INTO sometable (id) VALUES (UUID_TO_BIN(UUID()); -- since v8.0 

But use this in case I generate my own UUIDs;

 INSERT INTO sometable (id) VALUES 0x3f06af63a93c11e4979700505690773f; 

Similarly, you can use hexadecimal literals in your WHERE clauses:

 SELECT * FROM sometable WHERE id = 0x3f06af63a93c11e4979700505690773f; 

This will be faster if you do not need to convert the data to a UUID string each time.

Note: 'x' in '0xaBc case sensitive. Hexadecimal digits are not, however.

Answer 4

I use MariaDB, so the BIN_TO_UUID family of functions BIN_TO_UUID not exist. I still managed to get the corresponding values.

bin -> hex

Here uuid is the binary (16) value of uuid; You will use the value below to select its readable version.

 LOWER(CONCAT( SUBSTR(HEX(uuid), 1, 8), '-', SUBSTR(HEX(uuid), 9, 4), '-', SUBSTR(HEX(uuid), 13, 4), '-', SUBSTR(HEX(uuid), 17, 4), '-', SUBSTR(HEX(uuid), 21) )) 

hex -> bin

Here cc6e6d97-5501-11e7-b2cb-ceedca613421 is the readable version of the UUID, and you will use the value below in the WHERE clause to find it.

 UNHEX(REPLACE('cc6e6d97-5501-11e7-b2cb-ceedca613421', '-', '')) 

Greetings

Answer 5

Polyfill for BIN_TO_UUID and UUID_TO_BIN for MySQL 5:

 DELIMITER $$ CREATE FUNCTION BIN_TO_UUID(b BINARY(16)) RETURNS CHAR(36) BEGIN DECLARE hexStr CHAR(32); SET hexStr = HEX(b); RETURN LOWER(CONCAT( SUBSTR(hexStr, 1, 8), '-', SUBSTR(hexStr, 9, 4), '-', SUBSTR(hexStr, 13, 4), '-', SUBSTR(hexStr, 17, 4), '-', SUBSTR(hexStr, 21) )); END$$ CREATE FUNCTION UUID_TO_BIN(uuid CHAR(36)) RETURNS BINARY(16) BEGIN RETURN UNHEX(REPLACE(uuid, '-', '')); END$$ DELIMITER ;