Reach for MySQL's built-in UUID() function when you need a unique identifier with zero setup — but know upfront that it doesn't produce a random v4 UUID the way most developers assume. This guide shows you how to generate a UUID in MySQL, convert a UUID string to compact BINARY(16) storage with UUID_TO_BIN(), and get a true random v4 value when your application actually needs one. Getting the storage type right matters because a UUID stored as a bloated 36-character string wastes space and slows down every lookup on that column.
How to Generate a UUID in MySQL
The built-in UUID() function is the zero-setup way to generate a UUID in MySQL, but it doesn't generate a random UUID — it encodes the current timestamp and the server's MAC address, making it a v1-style value rather than a v4 one. The VARCHAR(36) versus UUID column type trade-off is part of the page on uuid in postgresql, including the ::uuid cast.
-- UUID() is timestamp + MAC-address based (v1-style), NOT a random v4 value
SELECT UUID();
-- Output: 550e8400-e29b-41d4-a716-446655440000
CREATE TABLE users (
id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
email VARCHAR(255) NOT NULL
);Explanation
UUID()is built into MySQL — no plugin or extension required to call it directly in aSELECTor a columnDEFAULT.- Despite its name,
UUID()does not produce a cryptographically random value — it's built from the current timestamp and the server's MAC address, the same construction as a v1 UUID. - Setting it as a column
DEFAULTmeans everyINSERTwithout an explicitidgets a fresh value automatically. - Storing it as
CHAR(36)works but uses the full 36-character string representation — see the next section for compact binary storage.
Convert a String to a UUID in MySQL
MySQL 8.0 added UUID_TO_BIN() and BIN_TO_UUID() to convert a 36-character UUID string to and from compact BINARY(16) storage. For anyone working in mongosh, a helper that creates a native binary value is covered in the mongodb uuid guide for beginners.
-- Convert a UUID string to compact 16-byte binary storage
SELECT UUID_TO_BIN('550e8400-e29b-41d4-a716-446655440000');
-- Convert it back to a readable string
SELECT BIN_TO_UUID(UUID_TO_BIN('550e8400-e29b-41d4-a716-446655440000'));
-- Typical usage: store as BINARY(16), display as a string
CREATE TABLE orders (
id BINARY(16) PRIMARY KEY,
total DECIMAL(10,2) NOT NULL
);
INSERT INTO orders (id, total)
VALUES (UUID_TO_BIN('550e8400-e29b-41d4-a716-446655440000'), 49.99);
SELECT BIN_TO_UUID(id) AS uuid, total FROM orders;Explanation
UUID_TO_BIN()andBIN_TO_UUID()were added in MySQL 8.0 specifically to makeBINARY(16)storage practical without manualHEX/UNHEXjuggling.- Storing UUIDs as
BINARY(16)instead ofCHAR(36)uses less than half the space per row and keeps index entries smaller. BIN_TO_UUID()reverses the conversion, returning the standard hyphenated 36-character string for display.- Both functions simply convert between text and binary representations of any valid UUID string — they don't validate or care which version it is.
Other Options for Generating UUIDs in MySQL
Since UUID() isn't a random v4 value, get one by generating it in application code instead, then insert it as a literal string or through UUID_TO_BIN().
-- MySQL has no built-in v4 generator -- generate one in application code
-- (see the python uuid / rust uuid guides) and insert it as a literal string
INSERT INTO sessions (id, user_id)
VALUES ('9b74c989-f3a0-4b1a-9b6a-2f6a9b9a9c1e', 42);
-- Or convert the app-generated string to BINARY(16) on insert
INSERT INTO sessions (id, user_id)
VALUES (UUID_TO_BIN('9b74c989-f3a0-4b1a-9b6a-2f6a9b9a9c1e'), 42);
-- swap_flag=1 reorders UUID()'s own timestamp bytes for better index locality
CREATE TABLE events (
id BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID(), 1)),
payload JSON
);Explanation
- Since
UUID()doesn't produce a random v4 value, generate one in application code (Python'suuid4(), Node'scrypto.randomUUID(), etc.) when you need true randomness. - Insert the app-generated string directly, or pass it through
UUID_TO_BIN()first to keepBINARY(16)storage consistent across the table. - The optional second argument to
UUID_TO_BIN(UUID(), 1)reorders the timestamp bytes inUUID()'s own output, placing sequential inserts closer together in the index. - That reordering trick only helps with MySQL's own
UUID()output — it does nothing for externally generated random UUIDs, which have no timestamp bytes to reorder.