UUID Generator / MySQL Generators 100% local

UUID primary keys in MySQL: UUID(), UUID_TO_BIN, and the fragmentation problem

MySQL has had a UUID() function since 5.0, but storing the result well took until MySQL 8.0. The naive approach burns disk and shreds your B-tree; the modern approach is two function calls and a binary column.

Stop using CHAR(36) for UUIDs

The obvious thing to do is CHAR(36) because that is what UUID() returns: a 36-character string with hyphens. It works, and it is wasteful.

A UUID is 128 bits, which is 16 bytes. CHAR(36) stores the textual form: 36 bytes plus charset overhead, so 108 bytes for utf8mb4. That is roughly 7 times the storage, and every B-tree page holds 7 times fewer rows. Index lookups, range scans, joins, all suffer.

The right column type is BINARY(16). It stores the raw 128 bits and nothing else. It compares byte by byte without collation rules, which is also faster.

UUID_TO_BIN and BIN_TO_UUID round-trip the values

MySQL 8.0 added two helper functions to bridge string UUIDs and binary storage:

SELECT UUID_TO_BIN('1a2b3c4d-5e6f-4a8b-9c0d-1e2f3a4b5c6d');
SELECT BIN_TO_UUID(@bin);

Both functions take an optional second argument: the swap flag. With UUID_TO_BIN(uuid, 1) MySQL rearranges the byte order so the time component sits at the front of the binary value. The point is index locality, see the next section.

Pre-8.0 MySQL does not have these. You can implement them with UNHEX(REPLACE(uuid, '-', '')) and INSERT(INSERT(...)) to reintroduce hyphens, but the two builtins are cleaner and you should be on 8.0 anyway.

UUIDv4 fragments your primary key index

UUID() in MySQL is UUIDv1 by default (timestamp + node ID). Pre-8.0 advice often said "use UUIDv4 because v1 leaks the MAC address". MySQL 8.0 added support for inserting truly random UUIDv4-style values via your application, but the fragmentation problem cuts the other way.

A UUIDv4 primary key is essentially random. Every INSERT goes to a random page in the clustered index. InnoDB has to load the page, modify it, possibly split it, write it back. After a few million rows you have an index where every leaf page is half-empty and every insert is a near-miss for the buffer pool.

A UUIDv1 with the swap flag (UUID_TO_BIN(UUID(), 1)) puts the timestamp first. New rows cluster at the end of the index, exactly like an autoincrement BIGINT. You keep the distributed-friendly property of UUIDs and you stop hating your INSERTs.

When BIGINT autoincrement still wins

If your table is purely internal, never replicates across regions, never gets an ID handed to a client before insert, never federates with another system, BIGINT AUTO_INCREMENT is still the cheapest, fastest option. Eight bytes, sequential, indexed perfectly, supported everywhere.

UUIDs earn their cost when you need: client-side ID generation (no round-trip before the row exists), merging data from multiple shards or regions, security through unguessable IDs, or zero collisions across services. If none of those apply, stay on BIGINT. If even one does, use BINARY(16) with UUID_TO_BIN(UUID(), 1) and you get the best of both.

Working example

sql

-- MySQL 8.0+. UUID v1 with swap flag for index locality.
CREATE TABLE orders (
  id BINARY(16) PRIMARY KEY,
  amount DECIMAL(10, 2) NOT NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
);

-- Insert with a fresh UUID v1 (timestamp first byte order)
INSERT INTO orders (id, amount)
VALUES (UUID_TO_BIN(UUID(), 1), 19.99);

-- Read it back as a human-readable string
SELECT BIN_TO_UUID(id, 1) AS id, amount, created_at
FROM orders
ORDER BY created_at DESC
LIMIT 10;

-- Lookup by string UUID (e.g. coming from an HTTP request)
SELECT BIN_TO_UUID(id, 1) AS id, amount
FROM orders
WHERE id = UUID_TO_BIN('1a2b3c4d-5e6f-11ee-9c0d-1e2f3a4b5c6d', 1);

Just need the result?

When you are seeding a migration script or pasting a fresh UUID into a YAML config, generating it in MySQL is overkill. The browser-based UUID generator at aldeacode.com produces RFC 9562-compliant values via the Web Crypto API instantly, and you can pipe them into UUID_TO_BIN at insert time.

Open UUID v4 Generator →

Frequently asked questions

Why does my UUID column take 108 bytes per row?

You declared it CHAR(36) on a utf8mb4 charset. Each character reserves up to 3 bytes plus storage overhead. Switch the column to BINARY(16) and use UUID_TO_BIN / BIN_TO_UUID at the boundaries. The migration is one ALTER TABLE plus an UPDATE.

Is the swap_flag in UUID_TO_BIN safe to enable on existing data?

Only if you migrate the existing rows. The flag changes the byte order of the stored value. Mixing flag-on and flag-off rows in the same column means BIN_TO_UUID returns garbage for the wrong half. Pick one and rewrite the whole column with UPDATE in a single transaction.

Should I use UUID() or generate UUIDs in my application?

Application-side, almost always. Generating in MySQL means the row exists before the app knows the ID, which complicates idempotency, retries, and event sourcing. Generate with crypto.randomUUID in Node, uuid.uuid4 in Python, java.util.UUID in Java, then INSERT with UUID_TO_BIN.