How MySQL 8.0 Optimizes UUID Storage with UUID_TO_BIN and BIN_TO_UUID

Background

UUID is commonly used, a 128‑bit string, e.g.: 12345678-1234-5678-1234-567812345678 UUID has versions defined by RFC 4122; MySQL implements version 1, composed of a timestamp, UUID version, and MAC address.

Benefits

Using UUID in MySQL is a good alternative to AUTO_INCREMENT PRIMARY KEY, offering:

Keys are unique across tables, databases, and servers.

Better security; hard to guess.

Can be generated offline.

Simplifies replication.

Drawbacks

However, there are downsides:

Increases storage space.

Makes debugging harder.

Performance issues because the values are longer and unordered.

MySQL 8.0 handling

MySQL 8.0 adds three functions:

UUID_TO_BIN

BIN_TO_UUID

IS_UUID

These functions make UUID usage easier and address the mentioned drawbacks. UUID_TO_BIN compresses a UUID string from 32 characters to 16 bytes. BIN_TO_UUID performs the reverse conversion.

Example usage:

Create table CREATE TABLE t (id binary(16) PRIMARY KEY); Insert INSERT INTO t VALUES(UUID_TO_BIN(UUID())); Select

SELECT BIN_TO_UUID(id) FROM t;

+--------------------------------------+ 
| BIN_TO_UUID(id)                     |
+--------------------------------------+ 
| 586bcc2d-9a96-11e6-852c-4439c456d444 |
| 5942e49a-9a96-11e6-852c-4439c456d444 |
| af0f27e2-9aad-11e6-852c-4439c456d444 |
+--------------------------------------+

Compression reduces storage size, but unordered UUIDs still cause performance issues because inserts occur at random positions in index trees, leading to extra I/O. UUID_TO_BIN accepts an optional second argument; setting it to true generates sequential, ordered values.

INSERT INTO t VALUES(UUID_TO_BIN(UUID(), true));

IS_UUID

validates whether a given argument is a legal UUID (32 hexadecimal characters optionally surrounded by '{', '-', '}').

SELECT IS_UUID('{12345678-1234-5678-1234-567812345678}');
SELECT IS_UUID('12345678123456781234567812345678');
SELECT IS_UUID('12345678-1234-5678-1234-567812345678');