MySQL 8.0’s InnoDB storage engine uses a clustered B-tree index on the primary key. Random UUID v4 values cause heavy page splits and index fragmentation — every insert lands in a random location in the index. UUID v7’s monotonic prefix means inserts are nearly sequential, reducing fragmentation. See MySQL UUID Storage Best Practices and UUID v7 Performance Benchmarks.

Schema: BINARY(16) primary key

CREATE TABLE orders (
  id         BINARY(16)   NOT NULL,
  customer   VARCHAR(255) NOT NULL,
  total      DECIMAL(10,2) NOT NULL,
  created_at DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id)
) ENGINE=InnoDB;

Inserting UUID v7 as BINARY(16)

-- No swap (flag=0): correct for UUID v7
INSERT INTO orders (id, customer, total)
VALUES (UUID_TO_BIN('018fbe3a-4c5d-7b12-8f3e-1a2b3c4d5e6f', 0), 'Alice', 99.99);

-- Reading back as string
SELECT BIN_TO_UUID(id, 0) AS id_str, customer, total
FROM orders
ORDER BY id;  -- BINARY ORDER BY = chronological for UUID v7

Generated column for string access

-- Store as BINARY(16), expose as virtual string column
CREATE TABLE orders (
  id         BINARY(16)    NOT NULL,
  id_str     VARCHAR(36)   GENERATED ALWAYS AS (BIN_TO_UUID(id, 0)) VIRTUAL,
  customer   VARCHAR(255)  NOT NULL,
  total      DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (id),
  INDEX idx_id_str (id_str)  -- optional: lookups by string UUID
) ENGINE=InnoDB;

The generated column lets application code read id_str directly without manual BIN_TO_UUID() conversion in every query.

Application-layer UUID v7 generation (Node.js)

import mysql from 'mysql2/promise';
import { v7 as uuidv7 } from 'uuid';

const pool = mysql.createPool({ host: 'localhost', database: 'app' });

async function createOrder(customer: string, total: number) {
  const id = uuidv7();
  const binaryId = Buffer.from(id.replace(/-/g, ''), 'hex');

  await pool.execute(
    'INSERT INTO orders (id, customer, total) VALUES (?, ?, ?)',
    [binaryId, customer, total]
  );

  return id;
}

async function getOrder(id: string) {
  const binaryId = Buffer.from(id.replace(/-/g, ''), 'hex');
  const [rows] = await pool.execute(
    'SELECT BIN_TO_UUID(id, 0) as id, customer, total FROM orders WHERE id = ?',
    [binaryId]
  );
  return (rows as any[])[0];
}

Cursor pagination with UUID v7

-- UUID v7 BINARY(16) pagination — id is already time-sorted
SELECT BIN_TO_UUID(id, 0) as id, customer, total
FROM orders
WHERE id > UUID_TO_BIN(?, 0)
ORDER BY id
LIMIT 20;

Because UUID v7 bytes are chronologically ordered without swapping, WHERE id > ? gives correct keyset pagination. See UUID v7 for Cursor Pagination.

VARCHAR(36) vs BINARY(16) comparison

MetricVARCHAR(36)BINARY(16)
Storage per row36 bytes16 bytes
Index size~3.5× largerBaseline
Comparison costString compareByte compare
ReadabilityHuman-readableRequires BIN_TO_UUID
App complexityNoneUUID↔binary conversion

For production workloads above a few million rows, BINARY(16) is worth the conversion overhead. See MySQL UUID Storage Best Practices.

Further reading

External references

Frequently asked questions

Should I use BINARY(16) or VARCHAR(36) for UUID v7 in MySQL?

BINARY(16) is the correct choice for production MySQL. It stores 16 bytes instead of 36 characters — 2.25× more compact — and InnoDB's B-tree page efficiency improves because more rows fit per page. Use UUID_TO_BIN(uuid, 0) to convert UUID v7 strings to binary (the swap flag 1 is for UUID v1 time reordering; UUID v7 is already sorted and does not need it). See MySQL UUID Storage Best Practices.

Does MySQL 8 have a built-in UUID v7 function?

No. MySQL 8 provides UUID() which generates UUID v1 (time-based, MAC address). There is no built-in uuidv7(). Generate UUID v7 in the application layer using a library such as the uuid npm package for Node.js or ramsey/uuid for PHP, then insert the value. A MySQL stored function can wrap application-provided values for default columns.

What is the UUID_TO_BIN swap flag and should I use it with UUID v7?

UUID_TO_BIN(uuid, 1) reorders the time bytes of a UUID v1 so they sort chronologically in InnoDB. UUID v7 already has a chronological prefix — the first 48 bits are a Unix millisecond timestamp in big-endian order. Use UUID_TO_BIN(uuid, 0) (no swap) for UUID v7 to preserve the natural byte order. Using swap=1 on UUID v7 would corrupt the timestamp ordering.