Introduction

GUIDs, or UUIDs, are convenient when ids must be generated outside the database or across multiple systems. The storage choice in MySQL matters because UUIDs are larger than integers and can hurt index locality if stored poorly. In most production schemas, the best default is BINARY(16) rather than CHAR(36) because it cuts storage in half and improves index efficiency.

Understand the Tradeoff First

A UUID is 128 bits of data. The familiar string form such as 550e8400-e29b-41d4-a716-446655440000 is easy for humans to read, but it is not efficient for indexes.

Common storage options:

• 'CHAR(36) for the canonical text form with hyphens'
• 'VARCHAR(36) for text storage, usually worse than fixed-width CHAR'
• 'BINARY(16) for compact binary storage'

CHAR(36) is easier to inspect manually, but BINARY(16) uses 16 bytes instead of 36 bytes and keeps indexes smaller. Smaller indexes usually mean better cache behavior and faster lookups.

That is why most serious MySQL schemas store the UUID in binary form and convert it only at application or query boundaries.

Create the Column as BINARY(16)

A simple table definition looks like this:

1CREATE TABLE orders (
2  id BINARY(16) NOT NULL PRIMARY KEY,
3  customer_name VARCHAR(100) NOT NULL,
4  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
5);

If you are on MySQL 8, use UUID_TO_BIN and BIN_TO_UUID for conversion:

1INSERT INTO orders (id, customer_name)
2VALUES (UUID_TO_BIN(UUID()), 'Alice');
3
4SELECT BIN_TO_UUID(id) AS id, customer_name
5FROM orders;

This gives you compact storage while still letting you view UUIDs in readable form when needed.

Use Ordered UUID Storage When Insert Locality Matters

Random UUIDs can fragment clustered indexes, especially if they are the primary key in InnoDB. MySQL 8 provides a helpful optimization for time-based UUID layouts through the second argument of UUID_TO_BIN.

1INSERT INTO orders (id, customer_name)
2VALUES (UUID_TO_BIN(UUID(), 1), 'Bob');
3
4SELECT BIN_TO_UUID(id, 1) AS id, customer_name
5FROM orders;

The swap flag is intended to improve index locality for UUID versions that contain time information. It is not a universal performance fix for every UUID variant, but it is worth understanding if you are inserting many rows into a clustered primary key.

If you are using UUID version 4 generated randomly in the application, you still gain the storage reduction of BINARY(16), even if index locality remains less ideal than sequential numeric keys.

Insert and Query from Application Code

From Python, you can convert a UUID to its 16-byte form with the standard library:

1import uuid
2
3u = uuid.uuid4()
4binary_value = u.bytes
5
6print(u)
7print(len(binary_value))

With MySQL Connector/Python, that can be inserted directly:

1import uuid
2import mysql.connector
3
4conn = mysql.connector.connect(
5    host="localhost",
6    user="root",
7    password="secret",
8    database="demo"
9)
10
11u = uuid.uuid4()
12
13with conn.cursor() as cur:
14    cur.execute(
15        "INSERT INTO orders (id, customer_name) VALUES (%s, %s)",
16        (u.bytes, "Carol")
17    )
18    conn.commit()
19
20print(str(u))

The application can keep using UUID objects or strings, while the database stores binary data efficiently.

When CHAR(36) Is Still Acceptable

There are cases where readability matters more than storage efficiency:

• small internal tools
• ad hoc reporting tables
• low-volume systems where performance is not sensitive
• environments where direct SQL inspection is constant and conversion functions are inconvenient

In those cases, CHAR(36) is acceptable if the cost is understood. The main problem is not correctness; it is avoidable bloat in hot tables and large indexes.

If you choose string storage, use fixed width CHAR(36) instead of VARCHAR(36) because UUID strings have a predictable length.

Do Not Confuse Key Strategy with Storage Format

The storage format question is separate from the data model question. You still need to decide whether UUID should be the primary key, a public identifier alongside an integer primary key, or both.

A common pragmatic design is:

• numeric auto-increment primary key for internal joins
• UUID unique column for public APIs and cross-system references

That design can improve write locality while preserving globally unique external ids. There is no single correct answer, but you should make the tradeoff consciously.

Common Pitfalls

• Storing UUIDs as VARCHAR(36) when fixed-width or binary storage would be better.
• Using CHAR(36) as the default in large hot tables without considering index size.
• Forgetting to convert with UUID_TO_BIN and BIN_TO_UUID consistently at insert and read time.
• Assuming binary UUID storage solves every write-locality issue even with fully random UUID generation.
• Mixing integer ids and UUID ids in the schema without a clear reason for each one.

Summary

• 'BINARY(16) is usually the best default storage format for GUIDs in MySQL.'
• It uses less space and keeps indexes smaller than CHAR(36).
• MySQL 8 conversion helpers make binary UUID storage practical.
• Readable text storage is acceptable in small or low-sensitivity systems, but it is a tradeoff.
• Decide key strategy and storage format separately so the schema reflects actual workload needs.