Generating UUIDv7 in MySQL and MariaDB

Jan 20, 2024 • Comments •

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!