Generate a UUID in MySQL

The built in function: UUID()

SELECT UUID();
-- d08822c0-bd1d-11f1-95fb-05b4d2f77661

UUID() returns a version 1 UUID as a 36 character string. Two things follow from that. It contains the server's MAC address and the time, so it is not anonymous. And because v1 puts the low bits of the time first, consecutive values do not sort, which matters for indexes.

Store it as BINARY(16), sorted by time

CREATE TABLE orders (
    id BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID(), 1)),   -- MySQL 8.0.13+ expression default
    status VARCHAR(20) NOT NULL
);

INSERT INTO orders (status) VALUES ('new');

SELECT BIN_TO_UUID(id, 1) AS id, status FROM orders;
SELECT * FROM orders WHERE id = UUID_TO_BIN('d08822c0-bd1d-11f1-95fb-05b4d2f77661', 1);

The second argument, the swap flag, is the important part. With 1, UUID_TO_BIN moves the time high bits of a v1 to the front, so the binary value increases with time and inserts append to the end of the index. Use the same flag in BIN_TO_UUID or you get a different string back. Never use the swap flag on a v4 or v7 value, it would scramble them.

Random v4 in pure SQL

MySQL has no v4 function. Most teams generate v4 or v7 in the application, see the PHP, Java and Python pages. If it has to happen in SQL, this expression builds a correct v4 from RANDOM_BYTES:

SELECT LOWER(CONCAT(
    HEX(RANDOM_BYTES(4)), '-',
    HEX(RANDOM_BYTES(2)), '-4',
    SUBSTR(HEX(RANDOM_BYTES(2)), 2, 3), '-',
    HEX(FLOOR(ASCII(RANDOM_BYTES(1)) / 64) + 8),
    SUBSTR(HEX(RANDOM_BYTES(2)), 2, 3), '-',
    HEX(RANDOM_BYTES(6))
)) AS uuid_v4;

The 4 sets the version and the FLOOR(...) + 8 gives one of 8, 9, A or B for the variant.

Validate

SELECT IS_UUID('d08822c0-bd1d-11f1-95fb-05b4d2f77661');   -- 1
SELECT IS_UUID('not a uuid');                               -- 0

IS_UUID accepts the hyphenated form, 32 plain hex characters, and the braced form. It checks the shape only, not the version bits.

MariaDB

-- MariaDB 10.7+: a native UUID type, stored as 16 bytes, shown as text
CREATE TABLE orders (id UUID PRIMARY KEY DEFAULT UUID(), status VARCHAR(20));

-- MariaDB 11.7+
SELECT UUID_v4(), UUID_v7();

MariaDB's UUID type also sorts v1 values by time internally, so the swap flag dance is not needed there.

Mistakes to avoid

• CHAR(36) primary keys. 36 bytes in every secondary index too, since InnoDB copies the primary key into each one. BINARY(16) cuts that by more than half.
• Forgetting the swap flag on one side. UUID_TO_BIN(x, 1) must pair with BIN_TO_UUID(y, 1).
• Selecting UUID() inside a multi row INSERT and expecting one value. It is evaluated per row, which is usually what you want, but not if you meant to reuse one id.

Frequently asked questions

Which UUID version does MySQL's UUID() return?

Version 1, time based, with the server's MAC address in the last group. Use UUID_TO_BIN(UUID(), 1) to store it in a sortable 16 byte form.

How do I generate a UUID v4 or v7 in MySQL?

There is no built in function. Generate in the application, or use the RANDOM_BYTES expression above for v4. MariaDB 11.7 adds UUID_v4() and UUID_v7().

Should I use BINARY(16) or CHAR(36)?

BINARY(16). Smaller rows, smaller indexes, faster joins. Convert with UUID_TO_BIN and BIN_TO_UUID at the edge, or let your ORM do it.