Skip to content

CONCEPT Cited by 1 source

UUID_TO_BIN swap flag

MySQL 8.0+ ships a built-in helper function UUID_TO_BIN(uuid_string, swap_flag) that converts a 36-character UUID string into the 16-byte binary representation — and, when the optional swap flag is set to 1, rearranges the byte layout of a UUIDv1 to put the high-order timestamp bits first. The result is a COMB-UUID: a v1 UUID whose byte-wise lexicographic order matches its temporal order, giving sequential-insert locality in a B+tree. (Source: sources/2026-04-21-planetscale-the-problem-with-using-a-uuid-primary-key-in-mysql.)

What the swap flag does

UUIDv1 stores the timestamp across three segments in the order time_low–time_mid–time_hi_and_version — putting the least significant bytes first. Byte-wise lexicographic sort doesn't match temporal sort.

The swap flag rearranges this to time_hi_and_version–time_mid–time_low, putting the most significant timestamp bits first. Successive UUIDv1 values generated within the same epoch now produce near-monotonically-increasing binary values.

Worked example

Same UUIDv1 input, two outputs (from the post):

SET @uuidvar = 'd211ca18-d389-11ee-a506-0242ac120002';

-- Without swap flag
SELECT HEX(UUID_TO_BIN(@uuidvar)) AS UUIDAsHex;
-- +----------------------------------+
-- | UUIDAsHex                        |
-- +----------------------------------+
-- | D211CA18D38911EEA5060242AC120002 |
-- +----------------------------------+

-- With swap flag
SELECT HEX(UUID_TO_BIN(@uuidvar, 1)) AS UUIDAsHex;
-- +----------------------------------+
-- | UUIDAsHex                        |
-- +----------------------------------+
-- | 11EED389D211CA18A5060242AC120002 |
-- +----------------------------------+

Notice: 11EED389 (the time_hi bytes) now sits at the front. Successive UUIDv1 values generated milliseconds apart produce binary values that sort correctly by byte — InnoDB inserts them on the right-most path of the B+tree like a sequential integer.

Typical use pattern

Use the swap flag alongside MySQL's built-in UUID() function (which generates UUIDv1 only) to produce a COMB-UUID pipeline entirely inside the database:

CREATE TABLE orders (
  id BINARY(16) PRIMARY KEY,
  customer_id BIGINT NOT NULL,
  created_at DATETIME(3) NOT NULL
);

INSERT INTO orders (id, customer_id, created_at)
VALUES (UUID_TO_BIN(UUID(), 1), 12345, NOW(3));

UUID() mints a v1; UUID_TO_BIN(..., 1) reorders it and returns 16 bytes. Every subsequent insert lands on the right edge of the primary-key B+tree.

Round-trip: BIN_TO_UUID

MySQL ships the inverse BIN_TO_UUID(bin, swap_flag). The same swap_flag argument is needed on the way out to reconstruct the original UUIDv1 string. Mismatched flags produce garbage UUIDs.

What the swap flag does not do

Doesn't work with UUIDv4

The swap flag has knowledge of v1's segment structure — it rearranges bytes based on the positions of the time_low/time_mid/time_hi fields. UUIDv4 has no timestamp, so swapping bytes is meaningless. Passing a v4 through UUID_TO_BIN(v4, 1) doesn't crash, but the output is a structurally-broken UUID that decodes back to something that isn't the original v4.

Doesn't work on client-generated UUIDs (unless v1)

The swap flag is useful only for MySQL-server-generated UUIDv1 values via UUID(). If your application layer generates UUIDv4 client-side, the swap flag can't help — switch to UUIDv7 or UUIDv6 instead (both are natively byte-sortable without byte-level reordering).

Doesn't make UUIDs smaller

The output is still 16 bytes. BINARY(16) costs 128 bits per PK value vs BIGINT's 64 bits — the 2× storage penalty remains. See concepts/uuid-string-representation-mysql.

Swap flag vs UUIDv6 / UUIDv7

MySQL's UUID() + swap-flag combo and UUIDv6 produce semantically the same result — a byte-sortable UUIDv1 variant. UUIDv6 bakes this into the RFC spec; the swap flag does it via a MySQL-specific helper function.

UUIDv7 goes further: Unix-epoch timestamp (not Gregorian) + random bits (not MAC address). For new systems that can pick any version, v7 is the modern choice — no MySQL-specific helper, no MAC-address leak, and byte-sortable natively.

Caveats

  • MySQL 8.0+ only. UUID_TO_BIN / BIN_TO_UUID are new in 8.0. MySQL 5.7 and earlier must implement the byte-reorder in application code or stored procedure.
  • Undocumented bit-manipulation trap. The swap flag also changes the position of the 4-bit version field (the 1 in -11ee- becomes the 1 in the 11EE at the front). Output still decodes as a v1 via BIN_TO_UUID(..., 1) but callers that inspect the second-byte-of-third-segment for the version will see the position has moved.
  • MAC-address field preserved unchanged. The last segment (A5060242AC120002 in the example) is the node ID / MAC address and is not swapped. UUIDv1 traceability persists; use v7 for an opaque origin.
  • Sequentiality only holds within a single generator. Across two hosts generating UUIDv1 concurrently, the timestamps interleave — but the node ID is in the unswapped tail, so the top bytes are still purely timestamp. Cross-host sort order is still correct within the precision of the shared wall clock.
  • Not a standard — no interop with non-MySQL clients. A COMB-UUID generated by UUID_TO_BIN(..., 1) is a MySQL-specific byte layout. Systems that decode the binary as RFC-4122 v1 will misread the fields.

Seen in

Last updated · 470 distilled / 1,213 read