Declaration
UUID_TO_BIN(string_uuid), UUID_TO_BIN(string_uuid, swap_flag)
Description
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 occurs if any parameter is invalid. The syntax for UUID_TO_BIN() includes both single- and two-parameter forms, as described below:
- Specify a string UUID value in single-parameter form. The binary result matches the order of the string parameters.
- Specify the string UUID value and
swap_flagvalue in the dual-parameter form:- If
swap_flagis 0, the binary format with two parameters is equivalent to the single-parameter format. The returned binary result matches the order of the string parameters. - If
swap_flagis 1, the format of the return value is different. The lower and higher time parts (the first and third groups of hexadecimal digits, respectively) are swapped, moving the part that changes faster to the right. If the result is stored in an index column, this can improve index efficiency.
- If
The exchange of the time portion is assumed to use values from UUID 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 portion exchange does not apply.
Examples
Take the following UUID value as an example:
SET @uuid = '6ccd780c-abcd-1026-9564-5b8c656024db';
You can use either of the following two forms of UUID_TO_BIN() for conversion:
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() into a string UUID. If you call UUID_TO_BIN() with the second parameter set to 1 to generate a binary UUID by swapping the time parts, you should also pass the second parameter 1 to BIN_TO_UUID() to undo the time part swap when converting the binary UUID back into 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 conversion methods for swapping the time parts in the two directions are different, 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