UUID_TO_BIN and BIN_TO_UUID for MySQL 5.6 and 5.7

Store UUIDs in 16 bytes, time-ordered like MySQL 8, on older MySQL versions and Amazon RDS.

15 August 2018 · 1 min read

From the archive: first published on mohamedsharaf.net in August 2018. Rewritten and checked in 2026; some details may be out of date.

Storing UUIDs as text takes 36 bytes and, for time-based UUIDs, scatters inserts all over the index. MySQL 8 solved this with UUID_TO_BIN(uuid, 1), which packs a UUID into 16 bytes and moves its time part to the front so new rows land next to each other. On MySQL 5.6 and 5.7 (including Amazon RDS) you can add the same behaviour yourself:

DELIMITER //
CREATE FUNCTION uuid_to_bin16(_uuid CHAR(36)) RETURNS BINARY(16)
  DETERMINISTIC NO SQL
RETURN UNHEX(CONCAT(
  SUBSTR(_uuid, 15, 4), SUBSTR(_uuid, 10, 4), SUBSTR(_uuid, 1, 8),
  SUBSTR(_uuid, 20, 4), SUBSTR(_uuid, 25)))//

CREATE FUNCTION bin16_to_uuid(_bin BINARY(16)) RETURNS CHAR(36)
  DETERMINISTIC NO SQL
RETURN LCASE(CONCAT_WS('-',
  HEX(SUBSTR(_bin, 5, 4)), HEX(SUBSTR(_bin, 3, 2)), HEX(SUBSTR(_bin, 1, 2)),
  HEX(SUBSTR(_bin, 9, 2)), HEX(SUBSTR(_bin, 11))))//
DELIMITER ;

The byte order matches MySQL 8’s UUID_TO_BIN(uuid, 1), so the stored data stays compatible when you upgrade. I give the functions their own names on purpose: once you are on MySQL 8, the built-in UUID_TO_BIN takes over, and custom functions with the same name cause confusion. On RDS with binary logging, you may need log_bin_trust_function_creators enabled to create them.