UUIDs in MySQL

UUID text7 sectionsUpdated

MySQL has no UUID type. Store 16 bytes in BINARY(16) with UUID_TO_BIN, read them back with BIN_TO_UUID, and use the swap flag only for v1 identifiers.

MySQL leaves the storage decision to you, and the default choice, CHAR(36), costs more than twice the space and turns every key comparison into string work. Here is the version that behaves.

UUID textcomputed by the converter itself
in01890a5d-ac96-774b-bcce-b302099a8057out01890a5dac96774bbcceb302099a8057

A v7 — already time-ordered, so the swap flag must stay off

inf81d4fae-7dec-11d0-a765-00a0c91e6bf6outf81d4fae7dec11d0a76500a0c91e6bf6

A v1 — this is the case the swap flag was invented for

Open the converter: UUID textNothing you paste leaves the browser.
On this page (7)

The column#

sql
CREATE TABLE orders (
    id BINARY(16) PRIMARY KEY,
    created_at DATETIME(3) NOT NULL
);

INSERT INTO orders (id, created_at)
VALUES (UUID_TO_BIN('01890a5d-ac96-774b-bcce-b302099a8057'), NOW(3));

SELECT BIN_TO_UUID(id) AS id FROM orders;
Column typeBytes per rowComparison
BINARY(16)16memcmp — one pass
CHAR(32) bare hex32string, case rules apply
CHAR(36) canonical36string, and stores four hyphens per row
VARCHAR(36)37+string, plus a length byte

The swap flag, and when it helps#

UUID_TO_BIN(u, 1) moves the first group past the third before storing: the row starts with time-high, then time-mid where it already was, then time-low. For a **v1** identifier that puts the slow-moving bits of the clock first, so consecutive inserts land near each other. MySQL's own example is 6ccd780c-baba-1026-… stored as 1026baba6ccd780c…. It is a fix for the v1 field order, not a general optimisation.

IdentifierStored with flag 0Stored with flag 1
01890a5d-ac96-774b-bcce-b302099a805701890a5dac96774bbcceb302099a8057774bac9601890a5dbcceb302099a8057
f81d4fae-7dec-11d0-a765-00a0c91e6bf6f81d4fae7dec11d0a76500a0c91e6bf611d07decf81d4faea76500a0c91e6bf6

RFC 9562 § 6.13 · DBMS and Database ConsiderationsDatabase considerations, including this exact reordering idea

Reading rows by hand#

A BINARY(16) column prints as unreadable bytes in a console, which is the real cost of the compact form. Either wrap it in BIN_TO_UUID(id) on the way out, or keep a generated column alongside it for humans, at the price of the space you just saved.

sql
ALTER TABLE orders
    ADD COLUMN id_text CHAR(36)
    GENERATED ALWAYS AS (BIN_TO_UUID(id)) VIRTUAL;

MySQL UUID() is a v1#

The built-in UUID() function returns a version 1 identifier with the node taken from the server, so it embeds a MAC address and does not sort. Generate v7 in the application and pass it in; the database function is only convenient, not correct for a key.

Questions people actually ask#

How should I store a UUID in MySQL?

BINARY(16) with UUID_TO_BIN on write and BIN_TO_UUID on read. CHAR(36) more than doubles the storage and makes every comparison a string comparison.

What does the second argument of UUID_TO_BIN do?

It reorders the timestamp fields so that the slow-moving half of a v1 clock leads. Use it for v1 only: applying it to a v7 removes the time ordering.

Is MySQL UUID() good enough for a primary key?

No. It returns a v1 that embeds the server MAC and does not sort. Generate a v7 in the application.

Browse the reference