Comments - UUID Data Type

The functions above provided by Johannes gave me cause for concern about the consistency of the new uuid data type with existing usage of the BINARY(16) data type. Eventually I worked out that, unfortunately, the functions he provided are not symmetric, and inconsistent with how the new uuid data type is represented.

Taking an example query from a database table I have, where:

• the uuid_as_binary16 column has data type BINARY(16)
• all SELECT queries shown below retrieve data from the same pre-existing row
• the uuid column has data type UUID and was populated by running UPDATE oliver_test SET uuid = uuid_as_binary16;

+--------------------------------------------------+
| HEX(UUID_TO_BIN(BIN_TO_UUID2(uuid_as_binary16))) |
+--------------------------------------------------+
| 3FEBCBE0500DAF0E4CDFFB19BDBB4847                 |
+--------------------------------------------------+

+----------------------------------+
| HEX(uuid_as_binary16)            |
+----------------------------------+
| 3FEBCBE0500D4CDFAF0EFB19BDBB4847 |
+----------------------------------+

After some research I came across https://gist.github.com/jamesgmarks/56502e46e29a9576b0f5afea3a0f595c which provided alternative functions (reprinted here in case the link target gets changed):

DELIMITER //

CREATE FUNCTION BIN_TO_UUID(b BINARY(16))
RETURNS CHAR(36)
BEGIN
   DECLARE hexStr CHAR(32);
   SET hexStr = HEX(b);
   RETURN LOWER(CONCAT(
        SUBSTR(hexStr, 1, 8), '-',
        SUBSTR(hexStr, 9, 4), '-',
        SUBSTR(hexStr, 13, 4), '-',
        SUBSTR(hexStr, 17, 4), '-',
        SUBSTR(hexStr, 21)
    ));
END//

CREATE FUNCTION UUID_TO_BIN(uuid CHAR(36))
RETURNS BINARY(16)
BEGIN
    RETURN UNHEX(REPLACE(uuid, '-', ''));
END//

DELIMITER ;

Using these functions provides symmetrical results:

+-------------------------------------------------+
| HEX(UUID_TO_BIN(BIN_TO_UUID(uuid_as_binary16))) |
+-------------------------------------------------+
| 3FEBCBE0500D4CDFAF0EFB19BDBB4847                |
+-------------------------------------------------+

+----------------------------------+
| HEX(uuid_as_binary16)            |
+----------------------------------+
| 3FEBCBE0500D4CDFAF0EFB19BDBB4847 |
+----------------------------------+

Also, crucially, these functions (unlike the ones provided by Johannes) provide a representation that is consistent with how the new data type works. Initially I had run:

UPDATE oliver_test SET uuid = uuid_as_binary16;

Then I could compare the two datatypes:

+--------------------------------------+
| BIN_TO_UUID(uuid_as_binary16)        |
+--------------------------------------+
| 3febcbe0-500d-4cdf-af0e-fb19bdbb4847 |
+--------------------------------------+

+--------------------------------------+
| uuid                                 |
+--------------------------------------+
| 3febcbe0-500d-4cdf-af0e-fb19bdbb4847 |
+--------------------------------------+

I hope this is useful to others.