MySQL: convert between UUID and binary


UUID string to binary

Find row that matches UUID with UUID_TO_BIN:

SELECT *
FROM my_table
WHERE uuid = UUID_TO_BIN('77dea2ad-3c8c-40c6-a278-7cf1a1ac9384');

Note: UUID_TO_BIN is only supported in MySQL 8+.

If your version of MySQL does not support the function, you can use UNHEX:

SELECT *
FROM my_table
WHERE uuid = UNHEX(REPLACE('77dea2ad-3c8c-40c6-a278-7cf1a1ac9384', '-', ''));

Binary to UUID string

Get a list of UUID’s as strings using BIN_TO_UUID:

SELECT BIN_TO_UUID(uuid) AS uuid
FROM my_table;

Note: BIN_TO_UUID is only supported in MySQL 8+.

If your version of MySQL does not support the function, you can use HEX:

SELECT LOWER(CONCAT(
  LEFT(HEX(uuid), 8), '-',
  MID(HEX(uuid), 9, 4), '-',
  MID(HEX(uuid), 13, 4), '-',
  MID(HEX(uuid), 17, 4), '-',
  RIGHT(HEX(uuid), 12)
)) AS uuid
FROM my_table;

For the unformatted version (no hyphens and all uppercase):

SELECT HEX(uuid) AS uuid
FROM my_table;

Functions

You can also polyfill the BIN_TO_UUID and UUID_TO_BIN functions.

BIN_TO_UUID

DELIMITER //

CREATE FUNCTION BIN_TO_UUID(bin BINARY(16))
RETURNS CHAR(36) DETERMINISTIC
BEGIN
  DECLARE hex CHAR(32);
  SET hex = HEX(bin);
  RETURN LOWER(CONCAT(LEFT(hex, 8), '-', MID(hex, 9, 4), '-', MID(hex, 13, 4), '-', MID(hex, 17, 4), '-', RIGHT(hex, 12)));
END; //

DELIMITER ;

See gist:

UUID_TO_BIN

DELIMITER //

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

DELIMITER ;

See gist:



If you enjoyed this post, please consider supporting this site!