Complete reference for the UUID() function, storage strategies with BINARY(16), UUID_TO_BIN/BIN_TO_UUID, and why auto-increment vs UUID primary keys matter for performance.
Quick Start
Generate a Random UUID with UUID()
MySQL has included a built-in UUID() function since version 5.0 — no extension or plugin needed:
Unlike PostgreSQL, MySQL’s UUID() function generates a version 1 (time-based) UUID, not version 4. It embeds a timestamp and the server’s MAC address. For a random v4 UUID in MySQL, you must build one manually or generate it in application code.
Random UUID (v4-style)
Generate a Random UUID Manually in MySQL
Since UUID() is time-based, use this pattern to build an RFC-compliant random UUID v4 using MySQL 8’s UUID_TO_BIN helpers combined with random hex:
This SQL pattern is verbose. In most projects it is easier to generate UUID v4 in application code (see our Python, PHP, or JavaScript guides) and pass it into an INSERT statement as a parameter.
Storage Strategy
Storing UUIDs Efficiently: BINARY(16) vs CHAR(36)
MySQL has no native UUID type. Storing UUIDs as CHAR(36) strings wastes space and slows down indexes. The recommended pattern uses BINARY(16):
Storage Type
Size
Index Performance
CHAR(36)
36 bytes
Slow — string comparison, no locality
BINARY(16)
16 bytes
Fast — but random UUIDs still fragment B-tree indexes
CREATE TABLE orders (
id BINARY(16) PRIMARY KEY,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
— Insert: convert string UUID to binary
INSERT INTO orders (id) VALUES (UUID_TO_BIN(UUID(), TRUE));
— Read: convert binary back to readable string
SELECT BIN_TO_UUID(id, TRUE) AS id FROM orders;
✅
The TRUE argument matters
UUID_TO_BIN(uuid, TRUE) swaps the time-low and time-high fields so time-based UUIDs sort correctly as binary values — critical for keeping InnoDB clustered index inserts sequential and avoiding page fragmentation.
MySQL’s built-in UUID() function generates a version 1 (time-based) UUID, which embeds a timestamp and the server’s MAC address. This is different from most other databases and languages, which default to version 4 (random). If you need a random UUID in MySQL, generate it in application code or build one manually using RANDOM_BYTES().
Use BINARY(16). It stores the UUID as its raw 16-byte binary representation instead of a 36-character string, cutting storage by more than half and significantly improving index performance. Use UUID_TO_BIN() and BIN_TO_UUID() functions (MySQL 8.0+) to convert between the human-readable string and binary storage forms.
No. Unlike PostgreSQL, MySQL has no dedicated UUID column type. The recommended approach is to store UUIDs as BINARY(16) using UUID_TO_BIN() for conversion, which gives you compact storage and fast indexing without a native type.
The second argument (swap_flag) reorders the time-low and time-high fields of a time-based UUID so that binary values sort in roughly chronological order. This keeps InnoDB clustered index inserts sequential instead of scattered, which prevents page splits and index fragmentation — the same problem UUID v7 solves at the application level.