Generate UUID in MySQL

Simple VARCHAR(36) - MySQL 5.x compatible

-- Simple approach - works on MySQL 5.x
-- Downside: VARCHAR(36) uses 36 bytes vs 16 bytes for BINARY(16)
-- Also causes B-tree index fragmentation with random UUIDs

CREATE TABLE users (
    id         VARCHAR(36)  NOT NULL DEFAULT (UUID()),
    name       VARCHAR(100) NOT NULL,
    email      VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

SELECT id, name FROM users;

BINARY(16) with swap_flag - MySQL 8.0+ recommended

-- BINARY(16) with swap_flag=1 - best for high-insert-rate tables
-- swap_flag reorders time bits so sequential UUIDs are adjacent in the index
-- This eliminates B-tree page splits and dramatically improves INSERT performance

CREATE TABLE events (
    id         BINARY(16)   NOT NULL DEFAULT (UUID_TO_BIN(UUID(), 1)),
    event_type VARCHAR(50)  NOT NULL,
    payload    JSON,
    created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    INDEX idx_created (created_at)
);

-- Generate UUID in application and pass it in
-- (recommended for idempotent inserts)
INSERT INTO events (id, event_type, payload)
VALUES (UUID_TO_BIN('6ccd780c-baba-1026-9564-0040f4311e29', 1), 'order.created', '{"orderId": 42}');

-- Read back as UUID string
SELECT BIN_TO_UUID(id, 1) AS id, event_type, created_at
FROM events
ORDER BY created_at DESC
LIMIT 10;

Stored procedure - generate and return UUID

DELIMITER $$

CREATE PROCEDURE create_order(
    IN  p_customer VARCHAR(100),
    IN  p_total    DECIMAL(10,2),
    OUT p_id       VARCHAR(36)
)
BEGIN
    SET p_id = UUID();
    INSERT INTO orders (id, customer, total)
    VALUES (UUID_TO_BIN(p_id, 1), p_customer, p_total);
END$$

DELIMITER ;

-- Call the procedure
CALL create_order('Alice', 99.99, @order_id);
SELECT @order_id AS created_order_id;

How do I generate a UUID in MySQL?

MySQL's built-in UUID() function returns a version 1 (time- and MAC-address-based) UUID, not a random v4. It has been available since MySQL 4.1.2. For index-friendly storage on MySQL 8.0+, wrap it with UUID_TO_BIN(UUID(), 1) and store the result in BINARY(16). MySQL has no native v4 or v7 generator, so produce those in your application layer.

Is UUID() cryptographically secure?

No. MySQL's UUID() returns a version 1 UUID derived from the timestamp and the server's MAC address, not from a CSPRNG, so it is predictable and leaks server identity. It is not suitable for session tokens, API keys, or other security-sensitive identifiers - generate a v4 UUID with a CSPRNG in your application layer for those.

What is the difference between UUID v4 and v7 in MySQL?

Note that MySQL's UUID() is v1 (time + MAC), not v4. A v4 UUID is fully random and not sortable, while v7 embeds a millisecond timestamp for chronological sorting (RFC 9562). MySQL has no native v4 or v7 function, so generate those in your application and store them as BINARY(16); use v7 for database primary keys at scale.

Do I need to install a package for UUID generation in MySQL?

No package is needed for MySQL's built-in UUID(), which returns a v1 UUID. MySQL cannot generate v4 or v7 natively, so for those you generate the UUID in your application layer and insert it.

How do I validate a UUID string in MySQL?

Use the platform's UUID parse/validation function, or test against the RFC 4122 regex: /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$/i. Always validate external input at API boundaries.

Should I use UUIDs as database primary keys in MySQL applications?

UUIDs work well as primary keys for distributed systems. Prefer native UUID/BINARY(16) column types over VARCHAR(36). For very large tables, consider UUID v7 for better B-tree insert locality.

Can I generate UUIDs in MySQL without a network connection?

Yes. UUID generation uses local OS entropy sources and does not require network access. Each call is independent and thread-safe on modern platforms.

What output formats are available in MySQL?

The standard hyphenated lowercase string (36 chars) is the default. Most APIs also support 32-char hex (no hyphens) and 16-byte binary formats. Use string format for APIs and binary for database storage.

What RFC standards apply to MySQL UUID generation?

Version 4 UUIDs follow RFC 4122. UUID v7 follows RFC 9562 (May 2024). Ensure your chosen method produces compliant version and variant bits.

When should I avoid UUID v1?

Avoid UUID v1 in security-sensitive contexts - it embeds the server MAC address and timestamp. Note that MySQL's UUID() is itself v1, so when you need unpredictability, generate a v4 in your application layer instead of relying on UUID().