> ## Documentation Index > Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt > Use this file to discover all available pages before exploring further. # UUIDs in MySQL > How to use UUIDs as primary keys in MySQL — storage strategies, performance tradeoffs, and UUID versions explained. > [card] [VillageSQL is a drop-in replacement for MySQL with extensions.](https://villagesql.com/docs/en/8.4/stable/install-server.md) > > All examples on this page work on VillageSQL. Install Now → Auto-increment integers are simple until they're not: they leak row counts, fail across distributed systems, and break when you merge databases or shard. UUIDs solve all of that, but MySQL's default handling makes them slow to insert at scale. Here's why that happens and how to fix it. ## The MySQL Default: UUID() as CHAR(36) MySQL's built-in `UUID()` function returns a v1 UUID as a 36-character string like `550e8400-e29b-41d4-a716-446655440000`. Storing that in a `CHAR(36)` column works, but it's inefficient: * 36 bytes instead of 16 for the same data * String comparison is slower than binary comparison * Random UUIDs cause B-tree page splits on every insert (more on this below) ```sql theme={null} -- This works but has problems at scale CREATE TABLE orders ( order_id CHAR(36) PRIMARY KEY DEFAULT (UUID()), user_id INT NOT NULL ); ``` ## The Standard Workaround: BINARY(16) MySQL provides `UUID_TO_BIN()` and `BIN_TO_UUID()` to convert between string and compact binary form. Using `BINARY(16)` cuts storage in half and speeds up comparisons: ```sql theme={null} CREATE TABLE orders ( order_id BINARY(16) PRIMARY KEY, user_id INT NOT NULL ); -- Store: convert string UUID to binary INSERT INTO orders (order_id, user_id) VALUES (UUID_TO_BIN(UUID()), 101); -- Retrieve: convert back to readable format SELECT BIN_TO_UUID(order_id), user_id FROM orders; ``` `UUID_TO_BIN()` takes an optional second argument (`1`) that reorders the timestamp bits so v1 UUIDs sort chronologically — a partial fix for the insert performance problem. ## The Remaining Problem: Random Inserts Fragment Indexes MySQL's InnoDB uses a clustered index, meaning rows are physically stored in primary key order. When you insert a row with a random UUID, InnoDB has to find where it goes in the middle of the index and potentially split a page to make room. Under sustained load, this causes write amplification and index fragmentation. UUID v7 fixes this by embedding a Unix timestamp in the high bits of the identifier. New UUIDs are always larger than existing ones, so inserts always append to the rightmost leaf of the index — no splits. ## With VillageSQL: Native UUID Type and All Versions VillageSQL's `vsql_uuid` extension adds a native `UUID` column type with 16-byte binary storage and no manual conversion, plus generation functions for UUID v1 through v7. ```sql theme={null} INSTALL EXTENSION vsql_uuid; ``` ```sql theme={null} -- Native UUID type — no UUID_TO_BIN/BIN_TO_UUID needed CREATE TABLE orders ( order_id UUID PRIMARY KEY, user_id INT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- UUID_V7 inserts always append to the index — no page splits INSERT INTO orders (order_id, user_id) VALUES (UUID_V7(), 101); INSERT INTO orders (order_id, user_id) VALUES (UUID_V7(), 102); -- Sorts chronologically by creation order SELECT user_id, created_at FROM orders ORDER BY order_id; -- Inspect the timestamp embedded in a v7 UUID (as datetime or Unix epoch) SELECT UUID_TIMESTAMP(order_id), UUID_EPOCH(order_id) FROM orders LIMIT 1; ``` ### Choosing a UUID version | Version | Use when | | :- | :- | | `UUID_V4()` | You need random IDs and don't care about insert performance | | `UUID_V7()` | New rows, primary keys, high insert volume — recommended default | | `UUID_V6()` | Legacy v1 systems that need sortable output | | `UUID_V1()` | Compatibility with systems that expect v1 format | | `UUID_V3()` / `UUID_V5()` | Deterministic IDs derived from a name (v3 = MD5, v5 = SHA-1) | `UUID_VERSION(uuid)` returns the version number of a stored UUID — useful for routing IDs by version. It accepts a `UUID` value or a string literal, which is converted implicitly — but an invalid string raises `ERROR 1525` instead of returning 0 or NULL, so it is not the tool for screening untrusted text; use `UUID_IS_VALID()` below for that. `UUID_COMPARE(uuid1, uuid2)` returns -1, 0, or 1 for explicit ordering logic. For a broader look at when to use UUIDs versus other primary key approaches, see [Choosing a Primary Key Strategy](https://villagesql.com/docs/en/guides/primary-key-strategies.md). ### Name-based UUIDs and the RFC namespaces `UUID_V3()` and `UUID_V5()` derive a UUID from a namespace plus a name, so the same pair always produces the same UUID. `UUID_NS_DNS()`, `UUID_NS_URL()`, `UUID_NS_OID()`, and `UUID_NS_X500()` return the four namespaces RFC 9562 defines. They return text, which is what the namespace parameter takes, so they compose directly: ```sql theme={null} SELECT UUID_V5(UUID_NS_DNS(), 'example.com'); -- cfbff0d1-9375-5685-968c-48ce8b15ae17 ``` `UUID_NIL()` and `UUID_MAX()` return the all-zeros and all-ones UUIDs the same RFC reserves, for use as sentinel values. ### Validating UUIDs from outside the database `UUID_IS_VALID(string)` returns 1 when a string would be accepted by a `UUID` column and 0 when it would not. It never raises, whatever it is handed, so it can screen values before they reach a typed column: ```sql theme={null} SELECT UUID_IS_VALID('550e8400-e29b-41d4-a716-446655440000'); -- 1 SELECT UUID_IS_VALID('nope'); -- 0 -- Reject malformed IDs at the schema level CREATE TABLE staging ( raw VARCHAR(40), CHECK (UUID_IS_VALID(raw) = 1) ); ``` ### Using UUID functions in generated columns and constraints The introspection, validation, and constant functions are deterministic, as are the name-based generators `UUID_V3()` and `UUID_V5()`. All of them can appear in generated columns and CHECK constraints. The random and time-based generators — `UUID_V4()`, `UUID_V7()`, and friends — cannot, because their output changes on every call. That lets you derive or validate stored values from a UUID at the schema level: ```sql theme={null} CREATE TABLE audit_log ( id uuid NOT NULL, ver INT AS (UUID_VERSION(id)) STORED, -- derive the version into a column CHECK (UUID_VERSION(id) IN (4, 7)) -- accept only v4 or v7 IDs ); ``` ## UUID vs. Auto-Increment | | AUTO\_INCREMENT | UUID (BINARY/native) | | :- | :- | :- | | **Distributed safe** | No — requires coordination | Yes | | **Leaks row count** | Yes | No | | **Storage** | 4–8 bytes | 16 bytes | | **Insert performance** | Fast (always appends) | Fast with v7, slow with v4 | | **Human readable** | Yes | No (binary) | | **Merge-safe** | No | Yes | For most single-database applications, `AUTO_INCREMENT` is fine. Reach for UUIDs when you need globally unique IDs across systems, when merging data from multiple sources, or when you don't want to expose sequential row counts. ## Frequently Asked Questions #### Can I use UUID\_V7 as a DEFAULT? Not currently. Non-deterministic functions can't be used in DEFAULT expressions in MySQL. Generate the UUID in your INSERT statement. #### Can I backfill existing rows with UUIDs? Yes. Add a `UUID` column, populate it with `UUID_V7()` in an UPDATE, then make it the primary key. #### Is UUID\_V7 a standard? Yes — defined in RFC 9562, which supersedes RFC 4122. ## Troubleshooting | Problem | Solution | | :- | :- | | `FUNCTION .UUID_V7 does not exist` | Run `INSTALL EXTENSION vsql_uuid` | | `DEFAULT (UUID_V7())` rejected | Remove `DEFAULT`; call `UUID_V7()` in each INSERT | | `Extension 'vsql_uuid' is already installed` | Already loaded — no action needed | ## See also * [Choosing a Primary Key Strategy in MySQL](https://villagesql.com/docs/en/guides/primary-key-strategies.md) — comparing UUIDs against AUTO\_INCREMENT and other strategies * [Generating Random Data in MySQL](https://villagesql.com/docs/en/guides/random-data-mysql.md) — the randomness behind UUID v4 * [How InnoDB Stores Data](https://villagesql.com/docs/en/guides/innodb-storage.md) — why random UUID primary keys cause page fragmentation This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.