Generating UUIDv7 in MySQL and MariaDB

A quick way to generate UUIDv7 in MySQL 8 and MariaDB 11:

SELECT
    CONCAT(
        -- Use NOW(3) to get milliseconds
        LPAD(HEX(UNIX_TIMESTAMP(NOW(3)) * 1000), 12, '0'),
        '7', -- version
        SUBSTR(HEX(RANDOM_BYTES(2)), 2),
        HEX(FLOOR(RAND() * 4 + 8)), -- variant bits
        SUBSTR(HEX(RANDOM_BYTES(8)), 2)
   ) AS uuid;

This will generate a hex string that can be inserted into MariaDB's UUID type as is, dashes will be added automatically.

If you need it as a binary value, wrap with unhex:

SELECT
    UNHEX(CONCAT(
        LPAD(HEX(UNIX_TIMESTAMP(NOW(3)) * 1000), 12, '0'),
        '7',
        SUBSTR(HEX(RANDOM_BYTES(2)), 2),
        HEX(FLOOR(RAND() * 4 + 8)),
        SUBSTR(HEX(RANDOM_BYTES(8)), 2)
    )) AS uuid;

If you specifically need a valid RFC 4122 string representation, use a bin formatter function (MySQL) or a type cast (MariaDB):

-- MySQL
SELECT
    BIN_TO_UUID(UNHEX(CONCAT(
        LPAD(HEX(UNIX_TIMESTAMP(NOW(3)) * 1000), 12, '0'),
        '7',
        SUBSTR(HEX(RANDOM_BYTES(2)), 2),
        HEX(FLOOR(RAND() * 4 + 8)),
        SUBSTR(HEX(RANDOM_BYTES(8)), 2)
    ))) AS uuid;

-- MariaDB
SELECT
    CAST((CONCAT(
        LPAD(HEX(UNIX_TIMESTAMP(NOW(3)) * 1000), 12, '0'),
        '7',
        SUBSTR(HEX(RANDOM_BYTES(2)), 2),
        HEX(FLOOR(RAND() * 4 + 8)),
        SUBSTR(HEX(RANDOM_BYTES(8)), 2)
    )) AS uuid) AS uuid;

UPD: Postgres can do it too!

Comments

Comments powered by Disqus