How to Create UUID in MySQL

How to Generate UUID in MySQL

MySQL's UUID() function generates version 1 UUIDs (timestamp-based with MAC address):

SQL

-- Generate a UUID v1
SELECT UUID();
-- Result: 550e8400-e29b-41d4-a716-446655440000

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

-- Insert without specifying ID (auto-generated)
INSERT INTO users (name, email) 
VALUES ('John Doe', 'john@example.com');

-- Query by UUID
SELECT * FROM users 
WHERE id = '550e8400-e29b-41d4-a716-446655440000';

Important: While VARCHAR(36) works, it uses 36 bytes. For better performance, use BINARY(16) storage (see next section).

Optimized Binary Storage (Recommended)

Store UUIDs as BINARY(16) instead of VARCHAR(36) to save 55% storage space and improve performance:

SQL - Binary Storage

-- Create table with BINARY(16) storage
CREATE TABLE users (
    id BINARY(16) PRIMARY KEY DEFAULT (UNHEX(REPLACE(UUID(), '-', ''))),
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert with auto-generated UUID
INSERT INTO users (name, email) 
VALUES ('Jane Smith', 'jane@example.com');

-- Query using UNHEX for conversion
SELECT 
    LOWER(CONCAT(
        HEX(SUBSTRING(id, 1, 4)), '-',
        HEX(SUBSTRING(id, 5, 2)), '-',
        HEX(SUBSTRING(id, 7, 2)), '-',
        HEX(SUBSTRING(id, 9, 2)), '-',
        HEX(SUBSTRING(id, 11))
    )) AS uuid,
    name,
    email
FROM users;

-- Query by UUID (convert string to binary)
SELECT * FROM users 
WHERE id = UNHEX(REPLACE('550e8400-e29b-41d4-a716-446655440000', '-', ''));

Performance Tip: BINARY(16) reduces storage by 55% (16 bytes vs 36 bytes) and significantly improves index performance.

MySQL 8.0+ UUID Functions

MySQL 8.0 introduced UUID_TO_BIN() and BIN_TO_UUID() for easier UUID handling:

SQL - MySQL 8.0+

-- Create table with optimized binary storage
CREATE TABLE products (
    id BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID())),
    name VARCHAR(200) NOT NULL,
    sku VARCHAR(50) UNIQUE,
    price DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert with auto-generated UUID
INSERT INTO products (name, sku, price) 
VALUES ('Laptop', 'LAP-001', 999.99);

-- Convert binary UUID back to string for display
SELECT 
    BIN_TO_UUID(id) AS uuid,
    name,
    sku,
    price
FROM products;

-- Query by UUID string (automatically converted)
SELECT * FROM products 
WHERE id = UUID_TO_BIN('550e8400-e29b-41d4-a716-446655440000');

-- UUID_TO_BIN with swap_flag for time-based ordering
-- swap_flag=1 reorders bytes for better index performance
CREATE TABLE orders (
    id BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID(), 1)),
    customer_id BINARY(16) NOT NULL,
    total DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Query with swap_flag
SELECT BIN_TO_UUID(id, 1) AS uuid FROM orders;

MySQL 8.0+: Use UUID_TO_BIN(UUID(), 1) with swap_flag=1 to reorder time bytes for better B-tree index performance on INSERT operations.

Storage Comparison: VARCHAR vs BINARY

Storage Type	Size	Performance	Recommendation
VARCHAR(36)	36 bytes	Slower indexes	Not recommended
CHAR(36)	36 bytes (fixed)	Slower indexes	Not recommended
BINARY(16)	16 bytes	Fast indexes	✓ Recommended
BINARY(16) + swap	16 bytes	Fastest (MySQL 8.0+)	✓ Best for high inserts

Creating Custom UUID v4 Function

MySQL's UUID() generates v1 (timestamp). Create a v4 (random) generator:

SQL - Custom UUID v4

-- Create UUID v4 generator function
DELIMITER $$

CREATE FUNCTION UUID_V4()
RETURNS CHAR(36)
DETERMINISTIC
BEGIN
    RETURN LOWER(CONCAT(
        HEX(FLOOR(RAND() * 0xFFFFFFFF)),
        '-',
        HEX(FLOOR(RAND() * 0xFFFF)),
        '-4',
        HEX(FLOOR(RAND() * 0xFFF)),
        '-',
        HEX(FLOOR(RAND() * 0x3FFF) + 0x8000),
        '-',
        HEX(FLOOR(RAND() * 0xFFFFFFFFFFFF))
    ));
END$$

DELIMITER ;

-- Use custom UUID v4
SELECT UUID_V4();

-- Create table with UUID v4
CREATE TABLE sessions (
    id VARCHAR(36) PRIMARY KEY DEFAULT (UUID_V4()),
    user_id BINARY(16) NOT NULL,
    token VARCHAR(255),
    expires_at TIMESTAMP
);

Indexing UUID Columns

Optimize UUID column indexing for better query performance:

SQL - Indexing

-- Primary key index (automatic)
CREATE TABLE users (
    id BINARY(16) PRIMARY KEY,
    email VARCHAR(255)
);

-- Foreign key index
CREATE TABLE orders (
    id BINARY(16) PRIMARY KEY,
    user_id BINARY(16) NOT NULL,
    total DECIMAL(10,2),
    INDEX idx_user_id (user_id)
);

-- Composite index
CREATE INDEX idx_user_created 
ON orders(user_id, created_at);

-- Check index usage
SHOW INDEX FROM users;

-- Analyze query performance
EXPLAIN SELECT * FROM users 
WHERE id = UUID_TO_BIN('550e8400-e29b-41d4-a716-446655440000');

Laravel Integration

Using UUIDs with Laravel Eloquent ORM:

PHP - Laravel Migration

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateUsersTable extends Migration
{
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->uuid('id')->primary();
            $table->string('name');
            $table->string('email')->unique();
            $table->timestamps();
        });
    }
}

PHP - Laravel Model

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Str;

class User extends Model
{
    protected $keyType = 'string';
    public $incrementing = false;

    protected static function boot()
    {
        parent::boot();
        
        static::creating(function ($model) {
            if (empty($model->id)) {
                $model->id = (string) Str::uuid();
            }
        });
    }
}

Other Database UUID Guides

PostgreSQL Native UUID type SQL Server NEWID() function SQLite uuid() 3.38+ MongoDB UUID() Binary type Oracle SYS_GUID()

Generate UUID for MySQL