MySQL UUID Storage — BINARY(16) vs VARCHAR(36)

MySQL does not have a native UUID column type. Every team that uses UUIDs in MySQL makes a storage choice — and the most common choice, VARCHAR(36), is also the worst one. This article explains why BINARY(16) is correct, how the difference affects index performance, and how to migrate an existing table.

The two options

-- Common but wrong
id VARCHAR(36) DEFAULT (UUID())

-- Correct
id BINARY(16)

VARCHAR(36) stores the UUID as a 36-character ASCII string: 32 hex digits plus 4 hyphens. BINARY(16) stores the raw 128-bit value as 16 bytes.

Storage cost

At a million rows the difference is ~21 MB of table data. For index pages (which are copied separately), the difference doubles — each index entry for a VARCHAR(36) key is 36 bytes versus 16 for binary, so a secondary index on a UUID column is more than twice the size.

Index performance

MySQL’s InnoDB engine uses B-tree indexes. The comparison operation for each index lookup is cheaper on fixed-width binary values than on variable-length strings. More importantly, UUID v4 inserts scatter randomly across the index regardless of storage format — but with BINARY(16) the per-row comparison cost is lower on every lookup, and the index pages are smaller so more of the working set fits in the buffer pool.

For UUID v7, BINARY(16) also preserves the correct sort order. Storing v7 as VARCHAR(36) causes MySQL to sort the string lexicographically, which correctly produces chronological order for v7. Storing as BINARY(16) also produces chronological order. Neither format breaks v7’s sortability in MySQL, but BINARY(16) does it with half the storage.

UUID_TO_BIN and BIN_TO_UUID

MySQL 8.0 added helper functions for converting between string and binary:

-- Insert: convert string UUID to binary
INSERT INTO users (id, email)
VALUES (UUID_TO_BIN('019236a7-b4f2-7000-8d3e-9c1a2b3d4e5f'), 'user@example.com');

-- Select: convert binary back to string
SELECT BIN_TO_UUID(id), email FROM users;

-- With swap_flag=1 for UUID v1 time ordering (not needed for v4/v7)
INSERT INTO users (id) VALUES (UUID_TO_BIN(UUID(), 1));

The swap_flag argument to UUID_TO_BIN reorders the time bytes of a UUID v1 to improve B-tree sort order. For UUID v4 and v7 you do not need it — pass 0 or omit it.

Defining the table

CREATE TABLE users (
  id         BINARY(16)   NOT NULL,
  email      VARCHAR(255) NOT NULL,
  created_at DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The application generates the UUID v7 value and inserts it via UUID_TO_BIN:

-- Application generates: 019236a7-b4f2-7000-8d3e-9c1a2b3d4e5f
INSERT INTO users (id, email)
VALUES (UUID_TO_BIN(?), ?);

Migrating an existing VARCHAR(36) table

If you have an existing table with a VARCHAR(36) UUID primary key, migration is a multi-step process to avoid downtime:

-- Step 1: Add a new BINARY(16) column
ALTER TABLE users ADD COLUMN id_bin BINARY(16) AFTER id;

-- Step 2: Back-fill from the existing string column
UPDATE users SET id_bin = UUID_TO_BIN(id);

-- Step 3: Add NOT NULL constraint
ALTER TABLE users MODIFY id_bin BINARY(16) NOT NULL;

-- Step 4: Drop old primary key and promote new column
ALTER TABLE users
  DROP PRIMARY KEY,
  ADD PRIMARY KEY (id_bin);

-- Step 5: Drop the old VARCHAR column (after updating all app queries)
ALTER TABLE users DROP COLUMN id;
ALTER TABLE users RENAME COLUMN id_bin TO id;

Run each step separately and test between them. Steps 1–3 can run online; steps 4–5 require a brief lock.

Application layer: inserting and querying

With BINARY(16) storage, your application must convert between the UUID string and binary for every insert and select. Most ORMs handle this automatically with a UUID type mapping:

Node.js / Drizzle:

import { mysqlTable, binary } from 'drizzle-orm/mysql-core';
import { v7 as uuidv7 } from 'uuid';

export const users = mysqlTable('users', {
  id: binary('id', { length: 16 }).primaryKey()
      .$defaultFn(() => Buffer.from(uuidv7().replace(/-/g, ''), 'hex')),
});

Python / SQLAlchemy:

from sqlalchemy import Column, LargeBinary
import uuid

class User(Base):
    __tablename__ = 'users'
    id = Column(LargeBinary(16), primary_key=True,
                default=lambda: uuid.uuid7().bytes)

Summary

Use BINARY(16) for all UUID columns in MySQL. It is half the storage of VARCHAR(36), produces smaller indexes, and allows faster comparisons. MySQL 8.0’s UUID_TO_BIN and BIN_TO_UUID functions handle the string conversion at the database layer if needed.

Further reading

• UUID in Databases — storage types for PostgreSQL, MySQL, SQLite, and MongoDB
• UUID v4 vs UUID v7 — which version to use and when
• UUID v7 ORM Support in 2026 — Prisma, Drizzle, GORM, SQLAlchemy configuration