Comments - UUID Data Type

To simulate the new UUID type insert and retrieve to a BINARY(16) format these functions might come in handy:

to convert (and store) a UUID in a 16 byte binary

CREATE FUNCTION `UUID_TO_BIN`(uuid char(36))
RETURNS binary(16)
BEGIN
  RETURN UNHEX( CONCAT( 
    SUBSTRING(uuid, 25, 12),
    SUBSTRING(uuid, 20, 4),
    SUBSTRING(uuid, 15, 4),
    SUBSTRING(uuid, 10, 4),
    SUBSTRING(uuid,  1, 8)
  ));
END

To retrieve and convert back to UUID

CREATE FUNCTION `BIN_TO_UUID2`(b binary(16))
RETURNS char(36) CHARSET ascii
BEGIN
   DECLARE hexStr CHAR(32);
   SET hexStr = HEX(b);
   RETURN LOWER(CONCAT(
     SUBSTR(hexStr, 25, 12), '-',
     SUBSTR(hexStr, 21, 4), '-',
     SUBSTR(hexStr, 13, 4), '-',
     SUBSTR(hexStr, 17, 4), '-',
     SUBSTR(hexStr, 1, 12)
  ));
END

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.