How to Store a UUID in MySQL: BINARY(16) vs CHAR(36)

A UUID is 128 bits, which is exactly 16 bytes raw or 36 characters as the canonical hyphenated string (32 hex digits plus 4 hyphens). MySQL has no UUID column type before MariaDB 10.7, so on MySQL you pick how to represent those 16 bytes: CHAR(36) (the readable aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee string, 36 bytes in a single-byte charset) or BINARY(16) (the raw bytes, less than half the size and the one to reach for). Below is the comparison table plus working DDL and insert/select for each, the UUID_TO_BIN() / BIN_TO_UUID() functions, the swap_flag trick that fixes random-insert performance, and what to do on older MySQL and on MariaDB.

Short answer: for a new table, store the raw 16 bytes in BINARY(16) and convert with UUID_TO_BIN() on insert and BIN_TO_UUID() on read (both added in MySQL 8.0). If the UUID is your primary key, pass swap_flag = 1 so the values sort time-sequentially and InnoDB appends instead of inserting into random pages. This is the same BINARY(16) versus hex-string trade-off as storing an MD5 hash in MySQL, with UUID_TO_BIN()/BIN_TO_UUID() playing the role of UNHEX()/HEX().

What is a UUID and how is it stored in MySQL?

A UUID (Universally Unique Identifier) is a fixed-size 128-bit value designed to be unique without a central coordinator. Its canonical text form is 36 characters: 32 hexadecimal digits split into five groups by four hyphens, like 6ccd780c-baba-1026-9564-5b8c656024db. Strip the hyphens and you have 16 raw bytes. MySQL stores those 16 bytes however you tell it to: as the 36-character ASCII string in CHAR(36) (readable, 36 bytes per row in latin1/ascii), or as the packed 16 bytes in BINARY(16) (less than half the storage and index size, needs a function to read).

MySQL's built-in UUID() function generates a version-1 (time-based) UUID as a 36-character string. As of MySQL 8.0 (GA April 2018), two helper functions convert between that string and the 16-byte form:

• UUID_TO_BIN(uuid) packs the 36-char string to its 16-byte VARBINARY(16) form, the UUID equivalent of UNHEX().
• BIN_TO_UUID(binary) expands it back to the hyphenated string, the equivalent of HEX().

You can equally generate the UUID in your application and hand MySQL the finished value; the storage choices are identical either way.

MariaDB differs here in a way worth knowing: MariaDB 10.7+ ships a native UUID column type that stores the value in 16 bytes and, like the swap_flag trick below, reorders bytes internally so values sort in an index-friendly order (with version 6 and 7 UUIDs stored without byte-swapping from 10.10 on). On older MariaDB, or older MySQL before 8.0, do the conversion by hand with UNHEX(REPLACE(uuid, '-', '')) on insert and HEX() on read.

Jump to:

• Storage comparison table
• Method 1: BINARY(16)
• Method 2: CHAR(36)
• The swap_flag trick for primary keys
• Worked schema: an orders table
• Older MySQL and MariaDB
• FAQ

Storage comparison table

The BINARY(16) row stores the value in 16 bytes against CHAR(36)'s 36, a 2.25x difference per row that carries straight through to every index on the column. On a billion-row primary key that is roughly 16 GB of stored key values against 36 GB, and the gap widens to 144 GB if the CHAR(36) column is sitting in a utf8mb4 table (4 bytes reserved per character). The swap-flag distinction is not about size, it is about insert order, covered below.

Method 1: BINARY(16), the recommended default

Less than half the storage of CHAR(36), less than half the index size, no charset issues.

CREATE TABLE uuid_binary (
  id BINARY(16) NOT NULL,
  INDEX idx_id (id)
) ENGINE=InnoDB;

INSERT INTO uuid_binary (id)
VALUES (UUID_TO_BIN('6ccd780c-baba-1026-9564-5b8c656024db'));

SELECT BIN_TO_UUID(id) FROM uuid_binary;
-- 6ccd780c-baba-1026-9564-5b8c656024db

Two helpers do all the conversion:

• UUID_TO_BIN('uuid-string') packs the 36-char string to its 16-byte binary form on insert.
• BIN_TO_UUID(binary_col) expands it back to the hyphenated string on read.

For lookups by id, wrap the parameter in UUID_TO_BIN() so you compare bytes to bytes:

SELECT * FROM uuid_binary WHERE id = UUID_TO_BIN(?);

Or let MySQL generate and pack a fresh UUID in one step:

INSERT INTO uuid_binary (id) VALUES (UUID_TO_BIN(UUID()));

In application code, work with the 16 bytes directly: uuid.bytes in Python, Buffer.from(hex, 'hex') in Node.js (after stripping hyphens), hex2bin(str_replace('-', '', $uuid)) in PHP. For 99% of new tables, BINARY(16) is the right answer.

Method 2: CHAR(36)

Store the canonical hyphenated string as fixed-length ASCII. Readable at the SQL prompt, more than double the bytes.

CREATE TABLE uuid_char (
  id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  INDEX idx_id (id)
) ENGINE=InnoDB;

INSERT INTO uuid_char (id) VALUES ('6ccd780c-baba-1026-9564-5b8c656024db');

SELECT id FROM uuid_char;
-- 6ccd780c-baba-1026-9564-5b8c656024db

CHAR(36) reserves exactly 36 bytes per row in latin1 or ascii. Don't use a multibyte charset for a UUID column: utf8mb4 reserves 144 bytes per row for the same 36-character string, four times the binary form. Add CHARACTER SET ascii COLLATE ascii_bin if your table default is utf8mb4. The binary collation also makes comparison case-sensitive and slightly faster, though for a UUID stored lowercase that mostly buys you predictability. CHAR(36) earns its keep only when you read and debug at the SQL prompt often enough that not typing BIN_TO_UUID() is worth the storage.

The swap_flag trick for primary keys

This is the detail that bites people who make a UUID their primary key. InnoDB stores every table as a clustered index keyed on the primary key, so rows are physically ordered by PK value on disk. A standard UUID (version 4 entirely random, or version 1 with its low timestamp bits leading) produces a new value that lands in a random spot in that B-tree on every insert. The result is constant page splits, write amplification, and a buffer pool full of half-empty pages. On a high-write table this is slow inserts and bloat, the exact problem random hash primary keys have.

The cost is not confined to the clustered index either. In InnoDB every secondary index stores a copy of the primary key as its row pointer, so a fat 16-byte (or worse, 36-byte) PK is duplicated into every secondary index on the table. A CHAR(36) PK does not just bloat one index, it bloats all of them, which is another reason the BINARY(16) form earns its place before you even get to insert order.

MySQL 8.0's UUID_TO_BIN() takes an optional second argument, swap_flag, that fixes this for version-1 UUIDs:

• UUID_TO_BIN(uuid, 0) (the default) packs the bytes in the same order as the string.
• UUID_TO_BIN(uuid, 1) swaps the time-low field (first group) with the time-high field (third group), moving the slowly-changing high bits of the timestamp to the front. Successive UUIDs generated by UUID() then sort almost in creation order, so InnoDB inserts become near append-only.

-- same UUID, two byte orders
SELECT HEX(UUID_TO_BIN('6ccd780c-baba-1026-9564-5b8c656024db', 0));
-- 6CCD780CBABA102695645B8C656024DB   (string order: random-ish prefix)
SELECT HEX(UUID_TO_BIN('6ccd780c-baba-1026-9564-5b8c656024db', 1));
-- 1026BABA6CCD780C95645B8C656024DB   (swapped: timestamp leads, sorts sequentially)

The one rule: the swap flag has to match on the way out. Pack with UUID_TO_BIN(uuid, 1), and you must read with BIN_TO_UUID(id, 1) or you get a scrambled string back. Pick the convention once per table and never mix it.

The swap trick only helps a version-1 (time-based) UUID, the kind UUID() produces, because only v1 has a timestamp to float to the front. If you generate identifiers in your application, the cleaner modern answer is a time-ordered UUID (UUIDv7) or a ULID: both are designed so their natural byte order is chronological, so they sort sequentially in BINARY(16) with no swap flag at all. For a new system I reach for UUIDv7 generated in the app and stored raw; for an existing MySQL 8.0 system standardizing on UUID(), the swap_flag = 1 convention is the pragmatic fix.

Worked schema: an orders table

Putting it together: an orders table with a BINARY(16) primary key that defaults to a swapped, time-sortable UUID, so the application can insert without supplying an id and inserts still stay near append-only.

CREATE TABLE orders (
  id          BINARY(16) NOT NULL DEFAULT (UUID_TO_BIN(UUID(), 1)) PRIMARY KEY,
  customer_id BIGINT UNSIGNED NOT NULL,
  total_cents INT UNSIGNED NOT NULL,
  created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- insert without naming the id; the DEFAULT generates a swapped UUID
INSERT INTO orders (customer_id, total_cents) VALUES (42, 1999);

-- read the id back as a canonical string (note the matching swap flag)
SELECT BIN_TO_UUID(id, 1) AS id, customer_id, total_cents
FROM orders;

-- look one up by its string id
SELECT customer_id, total_cents
FROM orders
WHERE id = UUID_TO_BIN('6ccd780c-baba-1026-9564-5b8c656024db', 1);

Two things to know about that DEFAULT. A column default that is a function expression (anything beyond a literal or CURRENT_TIMESTAMP) requires the parenthesised-expression syntax and MySQL 8.0.13 or newer. Before 8.0.13 you can't default a column to UUID_TO_BIN(UUID(), 1); generate the value in the application and pass it in, or use a BEFORE INSERT trigger. And again, the 1 in the DEFAULT and the 1 in every BIN_TO_UUID() / UUID_TO_BIN() that touches this column have to agree, forever.

Older MySQL and MariaDB

UUID_TO_BIN() and BIN_TO_UUID() only exist from MySQL 8.0. On MySQL 5.7 and earlier, or on MariaDB before the native type, do the conversion with the general-purpose hex helpers:

-- pack a UUID string into BINARY(16) (no swap)
INSERT INTO t (id) VALUES (UNHEX(REPLACE('6ccd780c-baba-1026-9564-5b8c656024db', '-', '')));

-- read it back as a (non-hyphenated) hex string
SELECT LOWER(HEX(id)) FROM t;

REPLACE(uuid, '-', '') drops the four hyphens to leave 32 hex digits, UNHEX() packs those to 16 bytes, and HEX() reverses it. You lose the hyphen formatting on read (re-insert it in the application, or with a nested CONCAT/SUBSTRING if you must do it in SQL), and you lose the built-in swap, so to get sequential inserts on pre-8.0 you reorder the timestamp fields yourself or, better, generate a time-ordered id in the app.

On MariaDB 10.7+ the cleanest option is the native UUID column type: declare id UUID, insert the string form directly, and MariaDB stores it in 16 bytes while keeping it human-readable on SELECT and reordering bytes internally so it sorts index-friendly. That is a genuine advantage over MySQL, which still has no UUID type and leaves you choosing between BINARY(16) and CHAR(36) by hand. If you are on MariaDB 10.7 or later and starting fresh, use the native type; if you are on MySQL, BINARY(16) with the swap convention is the closest equivalent.

What to do next

• For the 16-byte hash equivalent and the same BINARY vs hex-string trade-off, see How to Store an MD5 Hash in MySQL.
• For the 32-byte case and indexed generated columns, see How to Store a SHA-256 Hash in MySQL.
• For every MySQL data type with exact byte sizes (including a UUID section), see MySQL Data Types and Sizes.
• The other databases have their own answer to the same 16-byte question: a native uuid column in PostgreSQL, and the BinData subtype-4 representation in MongoDB.

FAQ

See also

• Storing a UUID in PostgreSQL uses the native uuid type and gen_random_uuid(), so there is no BINARY(16) versus CHAR(36) choice to make.
• Storing a UUID in MongoDB covers the BinData subtype-4 representation and when a string _id is the better trade.
• Storing an MD5 hash in MySQL is the same BINARY(16) versus hex-string decision, with UNHEX()/HEX() in place of the UUID helpers.
• Every MySQL data type with exact byte sizes puts BINARY, CHAR, and the rest side by side for sizing decisions.
• The MySQL cheat sheet collects the DDL, conversion, and indexing syntax used here in one reference.
• Running MySQL in Docker gives you a throwaway 8.0 instance to test UUID_TO_BIN() and the swap_flag byte order yourself.