M# MySQL UUID Functions Explained (UUID_TO_BIN, BIN_TO_UUID, is_swapped)

MySQL 8.0 shipped a set of functions specifically to solve the "UUIDs stored as text waste space and fragment indexes" problem — UUID_TO_BIN(), BIN_TO_UUID(), and a lesser-understood third argument called swap_flag. Most usage examples online use these functions without explaining what that flag actually does at the byte level, which is unfortunate, because it's the single most impactful detail for anyone using UUIDs as a clustered primary key in InnoDB. This post explains the mechanics precisely, with byte-level detail.

The Core Problem These Functions Solve

A standard UUID string is 36 characters: 550e8400-e29b-41d4-a716-446655440000. Stored as CHAR(36), that's 36 bytes plus overhead — over twice the 16 bytes the UUID's actual entropy requires. UUID_TO_BIN() and BIN_TO_UUID() convert between the human-readable string form and a compact BINARY(16) representation:

SELECT UUID_TO_BIN('550e8400-e29b-41d4-a716-446655440000');
-- Returns 16 raw bytes

SELECT BIN_TO_UUID(UUID_TO_BIN('550e8400-e29b-41d4-a716-446655440000'));
-- Returns '550e8400-e29b-41d4-a716-446655440000'

So far this is exactly what Postgres's native uuid type gives you automatically — MySQL requires an explicit conversion because BINARY(16) is just a generic fixed-length byte column, not a UUID-aware type. There's no format validation built in; UUID_TO_BIN() will reject a malformed string, but the underlying storage column itself doesn't enforce UUID structure the way Postgres's uuid type does.

The swap_flag Argument: What It Actually Does

This is the part most tutorials skip. UUID_TO_BIN() and BIN_TO_UUID() both accept an optional second argument:

UUID_TO_BIN(uuid_string, swap_flag)
BIN_TO_UUID(binary_uuid, swap_flag)

To understand what swap_flag does, you need the internal layout of a UUID v1 string, which is composed of five hyphen-separated fields:

550e8400 - e29b - 41d4 - a716 - 446655440000
 time-low  time-mid version-and- clock-seq    node
                     time-high

A version 1 UUID encodes a timestamp split across three separate fields (time-low, time-mid, time-high), and critically, those fields are stored least-significant-first in the standard textual representation — time-low (the fastest-changing part) comes first in the string, but it's actually the least significant part of the full 60-bit timestamp.

This matters enormously for index locality. If you store UUID v1 bytes in their natural string order, the most-varying bits (time-low) sit at the front of the binary value — meaning consecutive UUIDs generated moments apart do not sort near each other in a byte-wise comparison, defeating the entire point of using a time-based UUID for insert locality.

swap_flag = 1 reorders the timestamp fields so time-high comes first and time-low comes last — reconstructing a monotonically-increasing-ish byte sequence for UUIDs generated close together in time:

-- Without swap: time-low first, poor insert locality for UUIDv1
SELECT HEX(UUID_TO_BIN(UUID(), 0));

-- With swap: time-high first, sequential-ish byte order for UUIDv1
SELECT HEX(UUID_TO_BIN(UUID(), 1));

This distinction only matters for time-based UUIDs (v1). For UUIDv4, every bit is random regardless of field reordering, so swap_flag has no locality benefit — swapping random bytes produces another random byte sequence. It's purely a v1-specific optimization.

Full Working Example: Table Design

CREATE TABLE orders (
    id BINARY(16) PRIMARY KEY,
    tenant_id INT NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Insert using a v1-style UUID with swap enabled for insert locality
INSERT INTO orders (id, tenant_id, total)
VALUES (UUID_TO_BIN(UUID(), 1), 42, 199.99);

-- Read back in human-readable form
SELECT
    BIN_TO_UUID(id, 1) AS id,
    tenant_id,
    total
FROM orders
WHERE tenant_id = 42;

Note that MySQL's built-in UUID() function generates a version 1 UUID by default (timestamp + node identifier) — not v4. This is a common point of confusion for developers coming from Postgres, where gen_random_uuid() produces v4. If your application logic assumes random, non-sequential, non-identifying UUIDs (e.g., for public-facing IDs), UUID() in MySQL is the wrong function — it embeds the server's MAC address by default.

For a random (v4) UUID in MySQL, generate it application-side or use UUID_TO_BIN(UUID(), 0) only after confirming your MySQL version and configuration produce v4-equivalent randomness — as of MySQL 8.0, there is no built-in UUID_V4() function, so most teams generate v4 UUIDs in the application layer instead.

Application-Layer Generation: C#

using MySqlConnector;

var id = Guid.NewGuid(); // v4, generated client-side

await using var cmd = new MySqlCommand(
    "INSERT INTO orders (id, tenant_id, total) VALUES (@id, @tenant, @total)",
    connection);

// MySqlConnector maps Guid to BINARY(16) automatically when the column is BINARY(16)
// and OldGuids/GuidFormat connection options are configured correctly
cmd.Parameters.AddWithValue("@id", id.ToByteArray());
cmd.Parameters.AddWithValue("@tenant", 42);
cmd.Parameters.AddWithValue("@total", 199.99m);
await cmd.ExecuteNonQueryAsync();

One subtlety worth flagging explicitly: Guid.ToByteArray() in .NET does not use the same byte order as UUID_TO_BIN(). .NET's Guid internally stores its first three fields in little-endian order, while UUID's RFC 4122 textual representation — and MySQL's UUID_TO_BIN() — treats them as big-endian. Inserting raw Guid.ToByteArray() bytes directly into a BINARY(16) column and later reading them back with BIN_TO_UUID() will produce a different-looking string than the original .NET Guid, even though the underlying value is preserved bit-for-bit differently arranged. Either normalize the byte order explicitly in application code, or consistently use UUID_TO_BIN()/BIN_TO_UUID() on the MySQL side for all conversions so the mismatch never surfaces.

// Reorder bytes to match RFC 4122 / UUID_TO_BIN big-endian expectations
static byte[] ToRfc4122Bytes(Guid guid)
{
    var bytes = guid.ToByteArray();
    // Swap the first 3 fields from .NET little-endian to RFC 4122 big-endian
    Array.Reverse(bytes, 0, 4);
    Array.Reverse(bytes, 4, 2);
    Array.Reverse(bytes, 6, 2);
    return bytes;
}

Application-Layer Generation: Node.js

const { randomUUID } = require('crypto');

const id = randomUUID(); // v4, RFC 4122 byte order already

const [result] = await connection.execute(
  'INSERT INTO orders (id, tenant_id, total) VALUES (UUID_TO_BIN(?, 0), ?, ?)',
  [id, 42, 199.99]
);

Passing the string form to UUID_TO_BIN() inside the query, rather than converting to bytes client-side, sidesteps the byte-order mismatch entirely — MySQL handles the conversion using its own well-defined logic, and the driver never has to reason about endianness.

Performance Comparison

The practical takeaway from this table: swap_flag is a v1-specific fix for a v1-specific problem. If new schema design is on the table, generating UUIDv7 in the application layer (or via a library) and storing it directly as BINARY(16) gets the same insert-locality benefit as swap_flag=1 without depending on v1's MAC-address-embedding behavior at all.

Comparison With Alternatives

Conclusion

UUID_TO_BIN() and BIN_TO_UUID() exist to close the storage gap between MySQL's lack of a native UUID type and Postgres's built-in uuid type — using BINARY(16) instead of CHAR(36) is close to a strict win with no real downside beyond needing the conversion functions at the query boundary. The swap_flag argument is the detail worth understanding precisely rather than copying blindly from examples: it reorders a version-1 UUID's timestamp fields to restore insert locality, has zero effect on version-4 UUIDs, and is superseded in new schemas by simply choosing UUIDv7 generation instead of reaching for v1 plus the swap flag.

---

Tools and Versions

Need some UUIDs for testing? Try our tools for generating UUIDv4   UUIDv7   ULID   NanoID and decoding validating.
For a detailed comparison of UUID versions, visit our Complete Guide to UUID Versions.