MySQL:
UUID_TO_BIN
Syntax:
UUID_TO_BIN(string_uuid), UUID_TO_BIN(string_uuid, swap_flag)
Converts a string UUID to a binary UUID and returns the result. (The
IS_UUID() function description lists the permitted string UUID
formats.) The return binary UUID is a VARBINARY(16) value. If the UUID
argument is NULL, the return value is NULL. If any argument is invalid,
an error occurs.
UUID_TO_BIN() takes one or two arguments:
o The one-argument form takes a string UUID value. The binary result is
in the same order as the string argument.
o The two-argument form takes a string UUID value and a flag value:
o If swap_flag 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.
o If swap_flag is 1, the format of the return value differs: The
time-low and time-high parts (the first and third groups of
hexadecimal digits, respectively) are swapped. This moves the more
rapidly varying part to the right and can improve indexing
efficiency if the result is stored in an indexed column.
Time-part swapping assumes the use of UUID version 1 values, such as
are generated by the UUID() function. For UUID values produced by other
means that do not follow version 1 format, time-part swapping provides
no benefit. For details about version 1 format, see the UUID() function
description.
Suppose that you have the following string UUID value:
mysql> SET @uuid = '6ccd780c-baba-1026-9564-5b8c656024db';
To convert the string UUID to binary with or without time-part
swapping, use UUID_TO_BIN():
mysql> SELECT HEX(UUID_TO_BIN(@uuid));
+----------------------------------+
| HEX(UUID_TO_BIN(@uuid)) |
+----------------------------------+
| 6CCD780CBABA102695645B8C656024DB |
+----------------------------------+
mysql> SELECT HEX(UUID_TO_BIN(@uuid, 0));
+----------------------------------+
| HEX(UUID_TO_BIN(@uuid, 0)) |
+----------------------------------+
| 6CCD780CBABA102695645B8C656024DB |
+----------------------------------+
mysql> SELECT HEX(UUID_TO_BIN(@uuid, 1));
+----------------------------------+
| HEX(UUID_TO_BIN(@uuid, 1)) |
+----------------------------------+
| 1026BABA6CCD780C95645B8C656024DB |
+----------------------------------+
To convert a binary UUID returned by UUID_TO_BIN() to a string UUID,
use BIN_TO_UUID(). If you produce a binary UUID by calling
UUID_TO_BIN() with a second argument of 1 to swap time parts, you
should also pass a second argument of 1 to BIN_TO_UUID() to unswap the
time parts when converting the binary UUID back to a string UUID:
mysql> SELECT BIN_TO_UUID(UUID_TO_BIN(@uuid));
+--------------------------------------+
| BIN_TO_UUID(UUID_TO_BIN(@uuid)) |
+--------------------------------------+
| 6ccd780c-baba-1026-9564-5b8c656024db |
+--------------------------------------+
mysql> SELECT BIN_TO_UUID(UUID_TO_BIN(@uuid,0),0);
+--------------------------------------+
| BIN_TO_UUID(UUID_TO_BIN(@uuid,0),0) |
+--------------------------------------+
| 6ccd780c-baba-1026-9564-5b8c656024db |
+--------------------------------------+
mysql> SELECT BIN_TO_UUID(UUID_TO_BIN(@uuid,1),1);
+--------------------------------------+
| BIN_TO_UUID(UUID_TO_BIN(@uuid,1),1) |
+--------------------------------------+
| 6ccd780c-baba-1026-9564-5b8c656024db |
+--------------------------------------+
If the use of time-part swapping is not the same for the conversion in
both directions, the original UUID will not be recovered properly:
mysql> SELECT BIN_TO_UUID(UUID_TO_BIN(@uuid,0),1);
+--------------------------------------+
| BIN_TO_UUID(UUID_TO_BIN(@uuid,0),1) |
+--------------------------------------+
| baba1026-780c-6ccd-9564-5b8c656024db |
+--------------------------------------+
mysql> SELECT BIN_TO_UUID(UUID_TO_BIN(@uuid,1),0);
+--------------------------------------+
| BIN_TO_UUID(UUID_TO_BIN(@uuid,1),0) |
+--------------------------------------+
| 1026baba-6ccd-780c-9564-5b8c656024db |
+--------------------------------------+
URL: https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html
Example