UUID_TO_BIN

Syntax

UUID_TO_BIN(string_uuid), UUID_TO_BIN(string_uuid, swap_flag)

Description

UUID_TO_BIN() converts a string UUID to a binary UUID and returns the result. For more information about the string UUID format, see **IS_UUID()**. The binary UUID returned by UUID_TO_BIN() is a VARBINARY(16) value. If the UUID parameter is NULL, the return value is NULL. An error will be returned if any parameter is invalid. The syntax of UUID_TO_BIN() includes single-parameter and double-parameter forms, as described below:

• The single-parameter form specifies a string UUID value. The binary result has the same order as the string parameter.
• The double-parameter form specifies a string UUID value and a swap_flag value:
  • If swap_flag is 0, the double-parameter form is equivalent to the single-parameter form. The binary result has the same order as the string parameter.
  • If swap_flag is 1, the format of the return value is different. The time low and time high parts (the first and third groups of hexadecimal digits, respectively) are swapped, moving the faster-changing part to the right. If the result is stored in an indexed column, it can improve the efficiency of the index.

Swapping the time parts is assumed to use UUID values of version 1, such as those generated by the UUID() function. For UUID values generated by other methods that do not follow the version 1 format, swapping the time parts is not applicable.

Examples

Consider the following string UUID value:

SET @uuid = '6ccd780c-abcd-1026-9564-5b8c656024db';

Convert it using both forms of UUID_TO_BIN():

SELECT HEX(UUID_TO_BIN(@uuid));
+----------------------------------+
| HEX(UUID_TO_BIN(@uuid))          |
+----------------------------------+
| 6CCD780CABCD102695645B8C656024DB |
+----------------------------------+
1 row in set (0.001 sec)

SELECT HEX(UUID_TO_BIN(@uuid, 0));
+----------------------------------+
| HEX(UUID_TO_BIN(@uuid, 0))       |
+----------------------------------+
| 6CCD780CABCD102695645B8C656024DB |
+----------------------------------+
1 row in set (0.001 sec)

SELECT HEX(UUID_TO_BIN(@uuid, 1));
+----------------------------------+
| HEX(UUID_TO_BIN(@uuid, 1))       |
+----------------------------------+
| 1026ABCD6CCD780C95645B8C656024DB |
+----------------------------------+
1 row in set (0.001 sec)

Use BIN_TO_UUID() to convert the binary UUID returned by UUID_TO_BIN() back to a string UUID. If you use the second parameter 1 to call UUID_TO_BIN() to generate a binary UUID by swapping the time parts, you should also pass the second parameter 1 to BIN_TO_UUID() to swap the time parts back when converting the binary UUID back to a string UUID.

SELECT BIN_TO_UUID(UUID_TO_BIN(@uuid));
+--------------------------------------+
| BIN_TO_UUID(UUID_TO_BIN(@uuid))      |
+--------------------------------------+
| 6ccd780c-abcd-1026-9564-5b8c656024db |
+--------------------------------------+
1 row in set (0.001 sec)

SELECT BIN_TO_UUID(UUID_TO_BIN(@uuid,0),0);
+--------------------------------------+
| BIN_TO_UUID(UUID_TO_BIN(@uuid,0),0)  |
+--------------------------------------+
| 6ccd780c-abcd-1026-9564-5b8c656024db |
+--------------------------------------+
1 row in set (0.001 sec)

SELECT BIN_TO_UUID(UUID_TO_BIN(@uuid,1),1);
+--------------------------------------+
| BIN_TO_UUID(UUID_TO_BIN(@uuid,1),1)  |
+--------------------------------------+
| 6ccd780c-abcd-1026-9564-5b8c656024db |
+--------------------------------------+
1 row in set (0.001 sec)

If the time parts are swapped in different ways in both directions, the original UUID cannot be correctly restored:

SELECT BIN_TO_UUID(UUID_TO_BIN(@uuid,0),1);
+--------------------------------------+
| BIN_TO_UUID(UUID_TO_BIN(@uuid,0),1)  |
+--------------------------------------+
| abcd1026-780c-6ccd-9564-5b8c656024db |
+--------------------------------------+
1 row in set (0.001 sec)

SELECT BIN_TO_UUID(UUID_TO_BIN(@uuid,1),0);
+--------------------------------------+
| BIN_TO_UUID(UUID_TO_BIN(@uuid,1),0)  |
+--------------------------------------+
| 1026abcd-6ccd-780c-9564-5b8c656024db |
+--------------------------------------+
1 row in set (0.001 sec)