MySQL 8.0’s InnoDB storage engine uses a clustered B-tree index on the primary key. Random UUID v4 values cause heavy page splits and index fragmentation — every insert lands in a random location in the index. UUID v7’s monotonic prefix means inserts are nearly sequential, reducing fragmentation. See MySQL UUID Storage Best Practices and UUID v7 Performance Benchmarks.
Schema: BINARY(16) primary key
CREATE TABLE orders (
id BINARY(16) NOT NULL,
customer VARCHAR(255) NOT NULL,
total DECIMAL(10,2) NOT NULL,
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (id)
) ENGINE=InnoDB;
Inserting UUID v7 as BINARY(16)
-- No swap (flag=0): correct for UUID v7
INSERT INTO orders (id, customer, total)
VALUES (UUID_TO_BIN('018fbe3a-4c5d-7b12-8f3e-1a2b3c4d5e6f', 0), 'Alice', 99.99);
-- Reading back as string
SELECT BIN_TO_UUID(id, 0) AS id_str, customer, total
FROM orders
ORDER BY id; -- BINARY ORDER BY = chronological for UUID v7
Generated column for string access
-- Store as BINARY(16), expose as virtual string column
CREATE TABLE orders (
id BINARY(16) NOT NULL,
id_str VARCHAR(36) GENERATED ALWAYS AS (BIN_TO_UUID(id, 0)) VIRTUAL,
customer VARCHAR(255) NOT NULL,
total DECIMAL(10,2) NOT NULL,
PRIMARY KEY (id),
INDEX idx_id_str (id_str) -- optional: lookups by string UUID
) ENGINE=InnoDB;
The generated column lets application code read id_str directly without manual BIN_TO_UUID() conversion in every query.
Application-layer UUID v7 generation (Node.js)
import mysql from 'mysql2/promise';
import { v7 as uuidv7 } from 'uuid';
const pool = mysql.createPool({ host: 'localhost', database: 'app' });
async function createOrder(customer: string, total: number) {
const id = uuidv7();
const binaryId = Buffer.from(id.replace(/-/g, ''), 'hex');
await pool.execute(
'INSERT INTO orders (id, customer, total) VALUES (?, ?, ?)',
[binaryId, customer, total]
);
return id;
}
async function getOrder(id: string) {
const binaryId = Buffer.from(id.replace(/-/g, ''), 'hex');
const [rows] = await pool.execute(
'SELECT BIN_TO_UUID(id, 0) as id, customer, total FROM orders WHERE id = ?',
[binaryId]
);
return (rows as any[])[0];
}
Cursor pagination with UUID v7
-- UUID v7 BINARY(16) pagination — id is already time-sorted
SELECT BIN_TO_UUID(id, 0) as id, customer, total
FROM orders
WHERE id > UUID_TO_BIN(?, 0)
ORDER BY id
LIMIT 20;
Because UUID v7 bytes are chronologically ordered without swapping, WHERE id > ? gives correct keyset pagination. See UUID v7 for Cursor Pagination.
VARCHAR(36) vs BINARY(16) comparison
| Metric | VARCHAR(36) | BINARY(16) |
|---|---|---|
| Storage per row | 36 bytes | 16 bytes |
| Index size | ~3.5× larger | Baseline |
| Comparison cost | String compare | Byte compare |
| Readability | Human-readable | Requires BIN_TO_UUID |
| App complexity | None | UUID↔binary conversion |
For production workloads above a few million rows, BINARY(16) is worth the conversion overhead. See MySQL UUID Storage Best Practices.
Further reading
- MySQL UUID Storage Best Practices
- UUID v7 Performance Benchmarks
- UUID v7 for Cursor Pagination
- UUID in Databases — Storage and Indexing
External references
- MySQL 8 UUID_TO_BIN documentation
- MySQL InnoDB clustered index documentation
- RFC 9562 — UUID standard
Frequently asked questions
Should I use BINARY(16) or VARCHAR(36) for UUID v7 in MySQL?
BINARY(16) is the correct choice for production MySQL. It stores 16 bytes instead of 36 characters — 2.25× more compact — and InnoDB's B-tree page efficiency improves because more rows fit per page. Use UUID_TO_BIN(uuid, 0) to convert UUID v7 strings to binary (the swap flag 1 is for UUID v1 time reordering; UUID v7 is already sorted and does not need it). See MySQL UUID Storage Best Practices.
Does MySQL 8 have a built-in UUID v7 function?
No. MySQL 8 provides UUID() which generates UUID v1 (time-based, MAC address). There is no built-in uuidv7(). Generate UUID v7 in the application layer using a library such as the uuid npm package for Node.js or ramsey/uuid for PHP, then insert the value. A MySQL stored function can wrap application-provided values for default columns.
What is the UUID_TO_BIN swap flag and should I use it with UUID v7?
UUID_TO_BIN(uuid, 1) reorders the time bytes of a UUID v1 so they sort chronologically in InnoDB. UUID v7 already has a chronological prefix — the first 48 bits are a Unix millisecond timestamp in big-endian order. Use UUID_TO_BIN(uuid, 0) (no swap) for UUID v7 to preserve the natural byte order. Using swap=1 on UUID v7 would corrupt the timestamp ordering.