create-uuid7-mysql-function.sql

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