Introduction

MySQL can store UUID v4 values either as text or as binary. The practical answer is usually to store them in BINARY(16) for better space and index efficiency, and convert to or from the familiar dashed string format at the application boundary.

CHAR(36) Versus BINARY(16)

A UUID v4 such as 550e8400-e29b-41d4-a716-446655440000 contains 128 bits of data but takes 36 characters in string form because of the hexadecimal text and dashes.

Two common schemas are:

1CREATE TABLE users_char (
2    id CHAR(36) NOT NULL PRIMARY KEY,
3    name VARCHAR(100) NOT NULL
4);

1CREATE TABLE users_bin (
2    id BINARY(16) NOT NULL PRIMARY KEY,
3    name VARCHAR(100) NOT NULL
4);

CHAR(36) is easy to read manually, but BINARY(16) uses less storage and produces smaller indexes.

Using MySQL Conversion Functions

Current MySQL documentation provides UUID_TO_BIN() and BIN_TO_UUID() to convert between string and binary UUID values.

Insert a UUID string into a binary column:

INSERT INTO users_bin (id, name)
VALUES (UUID_TO_BIN('550e8400-e29b-41d4-a716-446655440000'), 'Ada');

Read it back as text:

SELECT BIN_TO_UUID(id) AS id, name
FROM users_bin;

If the application already generates UUID v4 values, send them as strings and let MySQL convert them during insert, or convert them in application code before binding the parameter.

A Python Example

1import uuid
2import mysql.connector
3
4conn = mysql.connector.connect(user="root", password="secret", database="demo")
5cur = conn.cursor()
6
7user_id = str(uuid.uuid4())
8cur.execute(
9    "INSERT INTO users_bin (id, name) VALUES (UUID_TO_BIN(%s), %s)",
10    (user_id, "Grace")
11)
12conn.commit()

That keeps UUID generation in Python while storing the compact binary representation in MySQL.

About the Swap Flag

MySQL's UUID_TO_BIN() supports an optional swap flag that helps reorder time-based UUID parts for index locality. That optimization is useful for time-ordered UUID variants, not random UUID v4 values. For v4, the second argument usually adds no meaningful benefit.

Choose Based on Operational Needs

Use CHAR(36) when:

• manual SQL inspection matters more than storage efficiency
• the table is small
• simplicity is more valuable than compact indexing

Use BINARY(16) when:

• the table is large
• the UUID is indexed heavily
• you care about storage and cache efficiency

For most production tables, BINARY(16) is the better default.

Application-Layer Mapping

In ORMs and service code, be explicit about when conversion happens. A common pattern is to keep UUIDs as strings or language-native UUID objects in application code and convert only at the SQL boundary. That keeps logging and debugging readable while still giving MySQL the compact binary layout.

The key is consistency. Mixing text UUID storage in one table and binary UUID storage in another creates avoidable confusion in joins, debugging, and migration scripts.

If the UUIDs are generated outside MySQL, document that clearly in the schema or service layer. Primary-key generation strategy affects every insert path, so ambiguity there tends to spread quickly through the application.

Common Pitfalls

The first pitfall is storing dashed UUID strings in VARCHAR(36) instead of a fixed-length type. If you want text storage, use CHAR(36).

Another issue is forgetting to convert binary UUIDs back to strings when debugging or exporting data.

A third pitfall is assuming the swap optimization helps UUID v4. It is aimed at time-ordered UUID layouts, not random ones.

Summary

• UUID v4 can be stored as either CHAR(36) or BINARY(16) in MySQL.
• 'BINARY(16) is usually better for storage and indexing.'
• Use UUID_TO_BIN() on insert and BIN_TO_UUID() on read.
• Keep generation and storage format choices explicit at the application boundary.
• The optional swap flag is generally not useful for random UUID v4 values.