Syntax
UUID_TO_BIN(string_uuid), UUID_TO_BIN(string_uuid, swap_flag)
Purpose
The UUID_TO_BIN() function 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- and double-parameter forms, as described in the following sections:
- The single-parameter form specifies the string UUID value. The binary result has the same order as the string parameter.
- The double-parameter form specifies the string UUID value and the
swap_flagvalue:- If
swap_flagis 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_flagis 1, the binary result has a different format. The time low and time high parts (the first and third groups of hexadecimal digits, respectively) are swapped. The time low part changes more frequently and is moved to the right. If the result is stored in an indexed column, the index efficiency is improved.
- If
The time part swap is assumed to be used with 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, the time part swap is not applicable.
Examples
Consider the following string UUID value:
SET @uuid = '6ccd780c-abcd-1026-9564-5b8c656024db';
Convert the string UUID to a binary UUID using both forms of UUID_TO_BIN():
obclient> SELECT HEX(UUID_TO_BIN(@uuid));
+----------------------------------+
| HEX(UUID_TO_BIN(@uuid)) |
+----------------------------------+
| 6CCD780CABCD102695645B8C656024DB |
+----------------------------------+
1 row in set
obclient> SELECT HEX(UUID_TO_BIN(@uuid, 0));
+----------------------------------+
| HEX(UUID_TO_BIN(@uuid, 0)) |
+----------------------------------+
| 6CCD780CABCD102695645B8C656024DB |
+----------------------------------+
1 row in set
obclient> SELECT HEX(UUID_TO_BIN(@uuid, 1));
+----------------------------------+
| HEX(UUID_TO_BIN(@uuid, 1)) |
+----------------------------------+
| 1026ABCD6CCD780C95645B8C656024DB |
+----------------------------------+
1 row in set
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.
obclient> SELECT BIN_TO_UUID(UUID_TO_BIN(@uuid));
+--------------------------------------+
| BIN_TO_UUID(UUID_TO_BIN(@uuid)) |
+--------------------------------------+
| 6ccd780c-abcd-1026-9564-5b8c656024db |
+--------------------------------------+
1 row in set
obclient> 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
obclient> 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
If the time part swap is not applied in both directions, the original UUID cannot be correctly restored:
obclient> 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
obclient> 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