Skip to content

Instantly share code, notes, and snippets.

@janedbal
Created March 17, 2023 14:13
Show Gist options
  • Save janedbal/bb4cf93114ba7ab9c4329b0bd9a7b480 to your computer and use it in GitHub Desktop.
Save janedbal/bb4cf93114ba7ab9c4329b0bd9a7b480 to your computer and use it in GitHub Desktop.
CREATE FUNCTION UUID7()
RETURNS CHAR(36)
BEGIN
DECLARE timestamp CHAR(12);
DECLARE h1 CHAR(8);
DECLARE h2 CHAR(4);
DECLARE h3 CHAR(3);
DECLARE h4 CHAR(4);
DECLARE h5 CHAR(12);
SET timestamp = LPAD(HEX(ROUND(UNIX_TIMESTAMP(CURTIME(4)) * 1000)), 12, '0');
-- 1th and 2nd block are made of 6 bytes containing timestamp in milliseconds
SET h1 = SUBSTR(timestamp, 1, 8);
SET h2 = SUBSTR(timestamp, 9, 4);
-- 3th block will start with a 7 indicating the version, remaining is random
SET h3 = SUBSTR(HEX(RANDOM_BYTES(2)), 2, 3);
-- 4th block first nibble can only be 8, 9 A or B, remaining is random
SET h4 = CONCAT(HEX(FLOOR(ASCII(RANDOM_BYTES(1)) / 64) + 8), SUBSTR(HEX(RANDOM_BYTES(2)), 2, 3));
-- 5th block is made of 6 random bytes
SET h5 = HEX(RANDOM_BYTES(6));
-- Build the complete UUID
RETURN LOWER(CONCAT(h1, '-', h2, '-7', h3, '-', h4, '-', h5));
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment