MySQL UUID – Universally Unique Identifiers for Scalable & Secure IDs

Introduction – Why Use UUIDs in MySQL?

In MySQL, primary keys are commonly implemented as auto-incrementing integers. But when security, distribution, or uniqueness across multiple systems is required, UUIDs (Universally Unique Identifiers) are a better fit. UUIDs are 128-bit strings that guarantee uniqueness without requiring centralized generation.

In this guide, you’ll learn:

• What a UUID is and its structure
• How to generate UUIDs in MySQL
• How to store, index, and optimize UUIDs
• When to use UUIDs vs AUTO_INCREMENT
• Best practices for performance and integrity

What Is a UUID?

A UUID (or GUID) is a 128-bit value formatted as:

550e8400-e29b-41d4-a716-446655440000

Structure:

8-4-4-4-12 (hexadecimal digits)

Provides a unique identifier across space and time
Used in databases, APIs, distributed systems, and software licensing

1. Generating UUIDs in MySQL

Syntax:

SELECT UUID();

Generates a new version 1 UUID (based on timestamp and MAC address).

Example:

550e8400-e29b-41d4-a716-446655440000

2. Creating a Table with UUID Primary Key

CREATE TABLE users (
  id CHAR(36) PRIMARY KEY,
  name VARCHAR(100)
);

Explanation:

• CHAR(36) to store standard 36-character UUID
• Used as primary key instead of AUTO_INCREMENT

3. Inserting with UUID

INSERT INTO users (id, name)
VALUES (UUID(), 'Alice');

Automatically generates and stores a new unique ID for each row.

4. UUID vs Binary UUID

To save space and improve performance, you can store UUIDs as BINARY(16) instead of CHAR(36).

Convert to Binary Format:

INSERT INTO users (id, name)
VALUES (UNHEX(REPLACE(UUID(), '-', '')), 'Bob');

Retrieve as Text:

SELECT 
  INSERT(
    INSERT(
      INSERT(
        INSERT(HEX(id), 9, 0, '-'), 
      14, 0, '-'), 
    19, 0, '-'), 
  24, 0, '-') AS uuid
FROM users;

BINARY(16) reduces space from 36 bytes to 16 bytes

UUID vs AUTO_INCREMENT – Comparison

Performance Tips

• Use BINARY(16) instead of CHAR(36) for faster indexing
• Pre-generate UUIDs in the app layer for better control
• For better locality, consider UUID_TO_BIN() with time-based ordering (MySQL 8.0.13+)

Example with UUID_TO_BIN():

CREATE TABLE logs (
  id BINARY(16) PRIMARY KEY,
  message TEXT
);

INSERT INTO logs (id, message)
VALUES (UUID_TO_BIN(UUID()), 'System started');

Retrieve:

SELECT BIN_TO_UUID(id) AS uuid, message FROM logs;

🚨 Limitations of UUID

• Takes more storage space than integers
• Slower inserts and joins (especially if random UUIDs are used)
• Harder to read/debug manually
• Not auto-sequential (unless using UUIDv1 or time-based bin)

Best Practices

Use UUIDs when:

• IDs are shared across multiple systems/services
• You need non-guessable public IDs (security)
• You’re building distributed or sharded databases

Use UUID_TO_BIN() and BIN_TO_UUID() for better performance
Avoid using UUIDs in high-volume OLTP unless properly indexed
Consider hybrid approach: UUID for external use, auto-increment for internal ops

Summary – Recap & Next Steps

UUIDs in MySQL provide a secure and globally unique method for identifying records, making them ideal for distributed apps, APIs, and multi-tenant systems. With careful storage and indexing, you can enjoy their benefits without compromising performance.

Key Takeaways

• UUID() generates 128-bit unique identifiers
• Store as BINARY(16) using UUID_TO_BIN() for optimal performance
• Use UUIDs in distributed and externally exposed systems
• Combine with indexing strategies and versioning where needed

Real-World Relevance
UUIDs are standard in microservices, REST APIs, multi-region systems, and analytics platforms where data isolation and non-sequential IDs are crucial.

FAQ – MySQL UUID

Is UUID unique across tables and systems?
Yes. UUIDs are globally unique due to timestamp/random/MAC generation.

Should I use UUID for primary keys?
If you require uniqueness across systems, yes. Otherwise, AUTO_INCREMENT may perform better.

What’s the difference between UUID() and UUID_TO_BIN()?
UUID() returns a string. UUID_TO_BIN() converts it to compact binary for storage.

Can UUID be indexed efficiently?
Yes, when stored as BINARY(16) and using UUID_TO_BIN() (especially ordered UUIDs).

Are UUIDs secure to expose publicly?
Yes. They are unguessable and non-sequential, making them suitable for APIs and URLs.