MySQL/MariaDB BIN_TO_UUID and UUID_TO_BIN Polyfill

gistfile1.txt

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 ;

Thanks a lot!

Awesome, lot of work saved, thanks! :)

Thank you!!!!

This implementation of BIN_TO_UUID only works for UUIDs was created using the non-MySQL-8-compatible UUID_TO_BIN() function shown here, or the MySQL 8 UUID_TO_BIN(uuid, 1) - see https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_uuid-to-bin for the meaning of the second flag.

See https://mariadb.com/kb/en/guiduuid-performance/ for why this implementation is a bad idea and for an example of an implementation that matches the MySQL UUID_TO_BIN(uuid) implementation (i.e. the swap flag is default set to 0).

In response to @guss77

DELIMITER //

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

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

DELIMITER ;

@theking2 - yes, this is more or less what I use. I came here looking for an implementation and after I found it lacking, I just wanted to warn people away (assuming they are looking for a correct implementation). If you continue looking elsewhere, you'd find the implementation that you posted, posted in many other places - hopefully with a good explanation of what it does and why it is good, which I saw no need to duplicate again.

excellent. I just happened to stumble on your gist first. Hence my two cents.