Generating UUIDv7 in MySQL and MariaDB
######################################

:category:  Misc
:date:      2024-01-20 07:06:00 +0200
:tags:      sql, uuid, uuidv7, mysql, mariadb

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

.. code-block:: sql

    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:

.. code-block:: sql

    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):

.. code-block:: sql

    -- 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!`__

.. __: link://slug/pgsql-uuid-v7