UUID in MySQL

MySQL has no uuid column type, so UUIDs go into BINARY(16) through UUID_TO_BIN() — 16 bytes instead of 36. The one detail that trips people up is the swap flag: it fixes sorting for v1 and breaks it for v7.

By the Withuse team · Updated

The schema you want

CREATE TABLE orders (
  id     BINARY(16) PRIMARY KEY,
  total  DECIMAL(10,2) NOT NULL
);

-- Insert an application-generated UUID (v7 recommended, see below)
INSERT INTO orders (id, total)
VALUES (UUID_TO_BIN('01a01180-0e00-7000-8000-000000000000'), 42.00);

-- Read it back as text
SELECT BIN_TO_UUID(id) AS id, total FROM orders;

-- Validate a string before converting
SELECT IS_UUID('550e8400-e29b-41d4-a716-446655440000');  -- 1

If you would rather not write the conversion everywhere, add a generated column: id_text BINARY(16) … paired with uuid_text VARCHAR(36) AS (BIN_TO_UUID(id)) VIRTUAL gives you a readable view without storing the text form.

The swap flag, and why it is a trap for v7

UUID_TO_BIN(value, 1) swaps the first and third groups of the UUID before storing — the time-low and time-high fields:

550e8400-e29b-41d4-a716-446655440000

UUID_TO_BIN(u)     ->  550e8400 e29b 41d4 a716446655440000
UUID_TO_BIN(u, 1)  ->  41d4 e29b 550e8400 a716446655440000
                       └─ 3rd ─┘└2nd┘└─ 1st ──┘

This exists because a version 1 UUID stores its timestamp with the least significant part first, so raw bytes do not sort by time. We verified the effect in both directions by constructing UUIDs with controlled timestamps:

VersionPlain bytes sort chronologically?With swap flag?
v1 (across a time-low wrap)❌ no — sorts backwards✅ yes
v7 (timestamps an hour apart)✅ yes❌ no — swapping breaks it

The rule is short: use the swap flag only for v1. Much of the advice written before RFC 9562 recommends UUID_TO_BIN(u, 1) unconditionally, because at the time v1 was the only time-based version MySQL could produce. Applying it to a v7 identifier undoes exactly the property you chose v7 for.

MySQL's UUID() is version 1, not version 4

SELECT UUID();
-- e.g. 9c0224be-a2a2-11f1-8000-aabbccddeeff
--                        ^ version digit is 1

The values embed the server's MAC address and the creation time, so they are not unpredictable and they disclose hardware information. Never use one as a token or any value that must be unguessable — see UUID v4 for that. MySQL offers no built-in v4 or v7 function, so generate those in your application.

InnoDB makes random keys more expensive than elsewhere

InnoDB stores the table in primary key order as a clustered index, so the primary key determines the physical layout of the rows themselves — not just an index beside them. A random v4 key therefore scatters row data across pages on insert, causing page splits in the table structure and a working set that outgrows the buffer pool sooner. On top of that, every secondary index stores a full copy of the primary key, so each extra byte in the PK is paid for once per secondary index entry.

This is why the v4-versus-v7 choice matters more in MySQL than in PostgreSQL, where tables are heaps and the primary key is an ordinary index. If a table needs UUID keys and takes meaningful insert traffic, use time-ordered v7 — the reasoning is laid out in UUID v4 vs v7.

Practical checklist

Frequently asked questions

Should I store a UUID as CHAR(36) or BINARY(16) in MySQL?

Use BINARY(16). MySQL has no native UUID type, so the choice is yours, and the text form costs more than twice the space: 36 bytes for the hyphenated string against 16 for the raw value. In InnoDB that penalty is multiplied, because every secondary index silently carries a full copy of the primary key, so a wide PK inflates every index on the table rather than just the row. Text storage also loses validation, letting a malformed value into the column, and turns every join and lookup into a string comparison. Convert at the boundary with UUID_TO_BIN() on insert and BIN_TO_UUID() on read, or let your application layer handle the 16 bytes directly.

What does the swap flag in UUID_TO_BIN actually do?

UUID_TO_BIN(value, 1) swaps the first and third hyphen-separated groups before storing — the time-low and time-high fields. It exists for version 1 UUIDs, whose 60-bit timestamp is split across the value with its least significant part first, so raw byte order does not follow creation time. We verified both directions by constructing UUIDs with controlled timestamps. Two v1 values microseconds apart that straddle a time-low wrap begin fffffff0 and 00000010, so plain bytes sort the later one first, while the swapped form puts the shared 11f1 time-high prefix in front and orders them correctly. The reverse holds for UUID v7: its 48-bit timestamp already sits at the front in big-endian order, so plain bytes sort chronologically and the swap destroys that ordering.

Does MySQL's UUID() function return a v4 UUID?

No, it returns version 1 — a timestamp and node based identifier, not a random one. That surprises people who assume UUID() matches uuid4() in Python or crypto.randomUUID() in JavaScript, and it has two practical consequences. First, the values embed a MAC address and creation time, so they are neither unpredictable nor free of hardware information; do not use them where a value must be unguessable, such as a password reset token or a hard-to-enumerate public URL. Second, they need the swap flag to sort usefully in an index. You can confirm the version yourself by reading the first digit of the third group, which is 1 rather than 4. If you want random v4 or time-ordered v7 identifiers in MySQL, generate them in your application, because the server offers no built-in function for either version.

How do I generate UUID v7 in MySQL?

You generate it in your application and insert the value, because MySQL ships no v7 function — unlike PostgreSQL 18, which added a native uuidv7(). Every major language already has an implementation: uuid.uuid7() in Python 3.14, uuid.NewV7() in Go, the uuid package in JavaScript, java-uuid-generator in Java. Pass the result through UUID_TO_BIN() without the swap flag, since v7 bytes are already in chronological order and swapping would break it. Note that this rules out a column DEFAULT, so the identifier has to be supplied by the inserting code or a trigger rather than by the schema. The payoff is the same as in any B-tree database: new rows land at the right-hand edge of the clustered index instead of scattering, which keeps inserts cheap as the table grows.

Are UUID primary keys slow in MySQL?

Random ones cost more in MySQL than in most databases, because InnoDB stores the table itself in primary key order as a clustered index. A random v4 key therefore does not merely scatter index entries — it scatters the row data, causing page splits in the table structure and pushing the working set beyond what the buffer pool can hold. The same key also gets copied into every secondary index, so a 16-byte PK is 8 bytes of extra weight per entry compared with a BIGINT. Time-ordered v7 values remove most of the penalty by keeping inserts sequential, which is why they are the sensible default when a table needs UUID keys.

Method note: the byte-layout and sort-order results above were computed and verified independently against the documented UUID_TO_BIN transformation. The SQL statements follow the MySQL 8.0 reference manual; unlike the other guides in this series they were not run against a live server, so no query output is quoted as observed.

References

Need UUIDs to seed a MySQL table? Generate up to 1,000 at once with our free UUID generator. Also in this series: UUID in PostgreSQL · length & storage size