How do I generate UUID in MySQL?

Use MySQL's built-in UUID() function which generates version 1 UUIDs. For version 4, use custom functions or generate UUIDs in your application code.

Should I store UUIDs as CHAR or BINARY in MySQL?

Use BINARY(16) with UUID_TO_BIN() for better performance and storage efficiency. Use CHAR(36) for readability and easier debugging.

Are MySQL UUIDs suitable as primary keys?

Yes, but consider using UUID_TO_BIN(UUID(), 1) to reorder time bits for better index performance, especially with InnoDB storage engine.

Generate UUID in MySQL

MySQL provides built-in UUID generation through the UUID() function, which generates version 1 UUIDs. Here's how to use it:

sql

-- Generate UUID version 1 (time-based)
SELECT UUID();
-- Result: 6ccd780c-baba-1026-9564-5b8c656024db

-- Generate multiple UUIDs
SELECT UUID() as uuid1, UUID() as uuid2, UUID() as uuid3;

MySQL's UUID() function generates version 1 UUIDs by default. For other versions, you'll need custom functions or generate UUIDs in your application:

sql

-- MySQL built-in UUID (version 1 only)
SELECT UUID() as uuid_v1;

-- For UUID v4, you can create a custom function
DELIMITER //
CREATE FUNCTION UUID_V4()
RETURNS CHAR(36)
READS SQL DATA
DETERMINISTIC
BEGIN
    RETURN LOWER(CONCAT(
        HEX(RANDOM_BYTES(4)),
        '-',
        HEX(RANDOM_BYTES(2)),
        '-',
        '4',
        SUBSTR(HEX(RANDOM_BYTES(2)), 2),
        '-',
        CONCAT(
            CASE 
                WHEN (CONV(SUBSTR(HEX(RANDOM_BYTES(1)), 1, 1), 16, 10) & 3) | 8 = 8 THEN '8'
                WHEN (CONV(SUBSTR(HEX(RANDOM_BYTES(1)), 1, 1), 16, 10) & 3) | 8 = 9 THEN '9'
                WHEN (CONV(SUBSTR(HEX(RANDOM_BYTES(1)), 1, 1), 16, 10) & 3) | 8 = 10 THEN 'a'
                ELSE 'b'
            END,
            SUBSTR(HEX(RANDOM_BYTES(1)), 2)
        ),
        '-',
        HEX(RANDOM_BYTES(6))
    ));
END//
DELIMITER ;

-- Use custom UUID v4 function
SELECT UUID_V4() as uuid_v4;

Table Schema with UUID Primary Keys

Basic UUID table setup:

sql

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

-- Insert data (UUID generated automatically)
INSERT INTO users (name, email) 
VALUES ('John Doe', '[email protected]');

-- Query with UUID
SELECT * FROM users WHERE id = '6ccd780c-baba-1026-9564-5b8c656024db';

Optimized UUID storage with BINARY:

sql

-- Using BINARY(16) for better performance (MySQL 8.0+)
CREATE TABLE orders (
    id BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID())),
    user_id BINARY(16) NOT NULL,
    order_number VARCHAR(50),
    total DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id)
);

-- Insert with binary UUID
INSERT INTO orders (id, user_id, order_number, total) 
VALUES (
    UUID_TO_BIN(UUID()),
    UUID_TO_BIN('6ccd780c-baba-1026-9564-5b8c656024db'),
    'ORD-001',
    99.99
);

-- Query with binary UUID (convert for readability)
SELECT 
    BIN_TO_UUID(id) as id,
    BIN_TO_UUID(user_id) as user_id,
    order_number,
    total
FROM orders 
WHERE user_id = UUID_TO_BIN('6ccd780c-baba-1026-9564-5b8c656024db');

Performance Optimization

Time-ordered UUIDs for better index performance:

sql

-- Use swap_flag=1 to reorder time bits for better clustering
CREATE TABLE events (
    id BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID(), 1)),
    event_type VARCHAR(50),
    data JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- This creates more sequential UUIDs for better InnoDB performance
INSERT INTO events (event_type, data) 
VALUES ('user_login', '{"user_id": 123, "ip": "192.168.1.1"}');

-- Query with proper UUID conversion
SELECT 
    BIN_TO_UUID(id, 1) as id,
    event_type,
    data,
    created_at
FROM events 
ORDER BY id;

UUID indexing strategies:

sql

-- Composite indexes with UUID
CREATE TABLE user_sessions (
    id BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID(), 1)),
    user_id BINARY(16) NOT NULL,
    session_token VARCHAR(255),
    expires_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_user_sessions (user_id, expires_at),
    INDEX idx_session_token (session_token)
);

-- Partitioning with UUID (use hash partitioning)
CREATE TABLE large_dataset (
    id BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID())),
    data TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
PARTITION BY HASH(id) PARTITIONS 4;

Advanced MySQL UUID Usage

UUID validation and utilities:

sql

-- Create UUID validation function
DELIMITER //
CREATE FUNCTION IS_VALID_UUID(uuid_string VARCHAR(36))
RETURNS BOOLEAN
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE result BOOLEAN DEFAULT FALSE;
    
    IF uuid_string REGEXP '^[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}$' THEN
        SET result = TRUE;
    END IF;
    
    RETURN result;
END//
DELIMITER ;

-- Test UUID validation
SELECT IS_VALID_UUID('6ccd780c-baba-1026-9564-5b8c656024db') as valid;
SELECT IS_VALID_UUID('invalid-uuid') as invalid;

UUID version extraction:

sql

-- Extract UUID version
DELIMITER //
CREATE FUNCTION UUID_VERSION(uuid_string VARCHAR(36))
RETURNS INT
READS SQL DATA
DETERMINISTIC
BEGIN
    RETURN CONV(SUBSTR(uuid_string, 15, 1), 16, 10);
END//
DELIMITER ;

-- Get UUID version
SELECT UUID_VERSION(UUID()) as version;

Bulk UUID operations:

sql

-- Generate multiple UUIDs efficiently
SELECT 
    UUID() as id,
    CONCAT('User ', ROW_NUMBER() OVER()) as name,
    CONCAT('user', ROW_NUMBER() OVER(), '@example.com') as email
FROM 
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t1
    CROSS JOIN 
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t2
LIMIT 10;

-- Batch insert with UUIDs
INSERT INTO users (id, name, email)
SELECT 
    UUID(),
    CONCAT('User ', n),
    CONCAT('user', n, '@example.com')
FROM (
    SELECT @row := @row + 1 as n
    FROM (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) t1,
         (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) t2,
         (SELECT @row := 0) r
    LIMIT 100
) numbers;

Migration and Compatibility

Converting existing tables to UUID:

sql

-- Add UUID column to existing table
ALTER TABLE existing_table 
ADD COLUMN uuid_id BINARY(16) DEFAULT (UUID_TO_BIN(UUID(), 1));

-- Update existing rows with UUIDs
UPDATE existing_table SET uuid_id = UUID_TO_BIN(UUID(), 1) WHERE uuid_id IS NULL;

-- Make UUID column NOT NULL
ALTER TABLE existing_table MODIFY uuid_id BINARY(16) NOT NULL;

-- Add unique index
ALTER TABLE existing_table ADD UNIQUE INDEX idx_uuid (uuid_id);

MySQL version compatibility:

sql

-- MySQL 5.7 and earlier (no UUID_TO_BIN function)
CREATE TABLE legacy_uuids (
    id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
    name VARCHAR(100)
);

-- MySQL 8.0+ (with UUID_TO_BIN optimization)
CREATE TABLE modern_uuids (
    id BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID(), 1)),
    name VARCHAR(100)
);

UUID Version Comparison

Choose the right version for your MySQL application:

Version 1 - MySQL's default UUID(), time-based
Version 3 - MD5 namespace-based (custom function needed)
Version 4 - Random (custom function or app-generated)
Version 5 - SHA-1 namespace-based (custom function needed)
Version 6 - Time-ordered (custom implementation)
Version 7 - Modern time-based (custom implementation)

For MySQL applications:

Web applications: Use MySQL's built-in Version 1 with UUID_TO_BIN(UUID(), 1)
High-performance systems: Consider Version 4 generated in application
Distributed systems: Use Version 5 for deterministic cross-system IDs

How do I generate UUID in other databases?

SQL databases:

PostgreSQL - Comprehensive UUID support with extensions
SQL Server - NEWID() and NEWSEQUENTIALID()
SQLite - Custom functions and extensions
Oracle - SYS_GUID() function

NoSQL databases:

MongoDB - ObjectId and UUID generation
Redis - UUID generation with Lua scripts
Cassandra - Built-in UUID functions

← Back to Online UUID Generator

Generate UUID in MySQL ​

Table Schema with UUID Primary Keys ​

Performance Optimization ​

Advanced MySQL UUID Usage ​

Migration and Compatibility ​

UUID Version Comparison ​

How do I generate UUID in other databases? ​