MySQL UUID Primary Key: BINARY(16) and UUID_TO

A UUID is a 128-bit identifier, written as 36 characters: 6ccd780c-baba-1026-9564-5b8c656024db. Two computers can create UUIDs at the same time without talking to each other, and they will not collide. That makes UUIDs useful for public identifiers and for data created on several servers.

Store it as BINARY(16)

Written as text, a UUID needs 36 bytes. As BINARY(16), it needs 16.

query.sql

SQL

CREATE TABLE documents (
    id BINARY(16) PRIMARY KEY,
    title VARCHAR(100) NOT NULL
);
INSERT INTO documents (id, title) VALUES
    (UUID_TO_BIN('6ccd780c-baba-1026-9564-5b8c656024db'), 'Contract'),
    (UUID_TO_BIN('6ccd7a12-baba-1026-9564-5b8c656024db'), 'Invoice');

SELECT BIN_TO_UUID(id) AS id, title FROM documents ORDER BY id;

Output

OUTPUT

+--------------------------------------+----------+
| id                                   | title    |
+--------------------------------------+----------+
| 6ccd780c-baba-1026-9564-5b8c656024db | Contract |
| 6ccd7a12-baba-1026-9564-5b8c656024db | Invoice  |
+--------------------------------------+----------+
2 rows in set

UUID_TO_BIN() converts text to bytes, and BIN_TO_UUID() converts back. These functions exist in MySQL 8.0 and later.

The random-key problem

InnoDB stores rows in primary key order. With an AUTO_INCREMENT key, new rows go to the end of the table. With a random UUID (version 4), every new row goes to a random place. That causes page splits, fragmentation, a larger cache footprint and slower inserts as the table grows.

UUID() in MySQL generates a version 1 UUID, which begins with a timestamp part. In version 1 the time bits come first in the text but in a “wrong” order for sorting. The swap flag reorders them so that UUIDs created one after another sort close together:

query.sql

SQL

1
2

SELECT HEX(UUID_TO_BIN('6ccd780c-baba-1026-9564-5b8c656024db'))   AS plain_order,
       HEX(UUID_TO_BIN('6ccd780c-baba-1026-9564-5b8c656024db', 1)) AS swapped_order;

Output

OUTPUT

+----------------------------------+----------------------------------+
| plain_order                      | swapped_order                    |
+----------------------------------+----------------------------------+
| 6CCD780CBABA102695645B8C656024DB | 1026BABA6CCD780C95645B8C656024DB |
+----------------------------------+----------------------------------+
1 row in set

With the swap flag set to 1, the high-order timestamp bytes come first. Use the same flag in UUID_TO_BIN and BIN_TO_UUID:

query.sql

SQL

SELECT BIN_TO_UUID(UUID_TO_BIN('6ccd780c-baba-1026-9564-5b8c656024db', 1), 1) AS round_trip;

Output

OUTPUT

+--------------------------------------+
| round_trip                           |
+--------------------------------------+
| 6ccd780c-baba-1026-9564-5b8c656024db |
+--------------------------------------+
1 row in set

A good compromise

Keep a small integer key for internal use, and add a UUID for public use:

query.sql

SQL

CREATE TABLE customers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_id BINARY(16) NOT NULL,
    name VARCHAR(100) NOT NULL,
    UNIQUE KEY uq_public_id (public_id)
);
INSERT INTO customers (public_id, name) VALUES (UUID_TO_BIN('6ccd780c-baba-1026-9564-5b8c656024db', 1), 'Maya');
SELECT id, BIN_TO_UUID(public_id, 1) AS public_id, name FROM customers;

Output

OUTPUT

+----+--------------------------------------+------+
| id | public_id                            | name |
+----+--------------------------------------+------+
|  1 | 6ccd780c-baba-1026-9564-5b8c656024db | Maya |
+----+--------------------------------------+------+
1 row in set

Joins and foreign keys use the small id. The outside world sees only public_id, so nobody can guess “the next” customer.

Comparison

	`INT UNSIGNED AUTO_INCREMENT`	`BINARY(16)` UUID
Size	4 bytes	16 bytes (in every secondary index too)
Insert pattern	Sequential, fast	Random (v4) or time-ordered (v1 with swap)
Guessable	Yes	No
Works across servers	Needs care	Yes
Readable	Yes	No

Newer standards

UUID version 7 (time-ordered) is designed for database keys, and most languages have libraries to create it. MySQL’s own UUID() function produces version 1, so version 7 is created by your application and then stored as BINARY(16). If your application uses UUID v7, you do not need the swap flag.

dangerous

Do not store UUIDs as CHAR(36)

A CHAR(36) primary key is large, and it multiplies in every index and foreign key. Use BINARY(16), or leave the table with an integer key and a separate public UUID.