Syntax
UUID_TO_BIN(string_uuid), UUID_TO_BIN(string_uuid, swap_flag)
Purpose
UUID_TO_BIN() converts a string UUID to a binary UUID and returns the result. For more information about the format of the string UUID, see IS_UUID(). The return value is a binary UUID of the VARBINARY(16) data type. If the UUID argument is NULL, the return value is NULL. If an argument is invalid, an error occurs. You can specify a single argument or two arguments in the syntax of this function:
- Specify only a string UUID. The returned binary value is in the same order as the string argument.
- Specify a string UUID and a
swap_flagvalue.- If the
swap_flagvalue is 0, the two-argument form is equivalent to the one-argument form. The binary result is in the same order as the string argument. - If the
swap_flagvalue 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) are swapped. This moves the more rapidly varying part to the right. If the result is stored in an indexed column, indexing efficiency can be improved.
- If the
Only the time parts in UUID v1 values, such as values generated by the UUID() function, can be swapped. Swapping is not applicable to UUID values in other versions.
Examples
The following string UUID is used as an example:
SET @uuid = '6ccd780c-abcd-1026-9564-5b8c656024db';
Call the UUID_TO_BIN() function in two forms to convert the string UUID:
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
Call the BIN_TO_UUID() function to convert the binary UUID returned by the UUID_TO_BIN() function to a string UUID. If you use the second argument value 1 to call the UUID_TO_BIN() function to generate a binary UUID by swapping the time part, you must pass the second parameter 1 to the BIN_TO_UUID() so that swapping can be canceled when you convert the binary UUID back to the 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 is converted in different ways in the two functions, the original UUID cannot be 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