> ## 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.

# vsql_uuid extension for MySQL

> The vsql_uuid extension adds a native 16-byte uuid column type to MySQL and generates version 1, 3, 4, 5, 6, and 7 UUIDs in SQL.

MySQL stores a UUID as text or as a `BINARY(16)` you convert by hand.
`vsql_uuid` adds a real `uuid` column type that holds 16 bytes, prints as the
familiar 36-character form, and sorts by its own comparison rule. It also
generates versions 1, 3, 4, 5, 6, and 7, where MySQL's own `UUID()` produces
version 1 only.

| | |
| - | - |
| **Maintainer** | VillageSQL |
| **Source and full documentation** | [villagesql/vsql-uuid](https://github.com/villagesql/vsql-uuid) |
| **License** | GPL-2.0 |

## Install

`vsql_uuid.veb` is already in the server's `lib/veb/` directory if you installed
VillageSQL with the install script, the Docker image, or a release tarball.
Install it into the server with one statement:

```sql theme={null}
INSTALL EXTENSION vsql_uuid;
```

Confirm it is there:

```sql theme={null}
SELECT EXTENSION_NAME
FROM INFORMATION_SCHEMA.EXTENSIONS
WHERE EXTENSION_NAME = 'vsql_uuid';
```

```
+----------------+
| EXTENSION_NAME |
+----------------+
| vsql_uuid      |
+----------------+
```

To build it yourself, follow the build instructions in the
[repository](https://github.com/villagesql/vsql-uuid).

## What it adds

### The uuid type

Declare the column as `uuid`. It occupies 16 bytes and works as a primary key.

```sql theme={null}
CREATE TABLE orders (
    id     uuid PRIMARY KEY,
    placed DATETIME,
    total  DECIMAL(8,2)
);
```

### Functions

| Function | Returns | What it does |
| - | - | - |
| `UUID_V1()` | `uuid` | Time-based, using the host MAC address |
| `UUID_V1MC()` | `uuid` | Time-based, using a random multicast address instead of the MAC |
| `UUID_V3(namespace, name)` | `uuid` | Name-based, MD5 |
| `UUID_V4()` | `uuid` | Random |
| `UUID_V5(namespace, name)` | `uuid` | Name-based, SHA-1 |
| `UUID_V6()` | `uuid` | Time-ordered, monotonic within a connection |
| `UUID_V7()` | `uuid` | Time-ordered on a Unix millisecond timestamp |
| `UUID_NIL()`, `UUID_MAX()` | `uuid` | The all-zero and all-ones values |
| `UUID_NS_DNS()`, `UUID_NS_URL()`, `UUID_NS_OID()`, `UUID_NS_X500()` | text | The RFC 9562 namespace constants, for `UUID_V3` and `UUID_V5` |
| `UUID_IS_VALID(s)` | int | 1 when a string would be accepted by a `uuid` column, 0 when it would not |
| `UUID_VERSION(u)` | int | The version nibble |
| `UUID_TIMESTAMP(u)` | text | The embedded time as `'YYYY-MM-DD HH:MM:SS'` in UTC, NULL for versions carrying no time |
| `UUID_EPOCH(u)` | int | The same time in Unix seconds |
| `UUID_COMPARE(a, b)` | int | -1, 0, or 1 |

## Example

Use a version 7 identifier as the primary key, so rows written later sort after
rows written earlier, then read the version back:

```sql theme={null}
INSERT INTO orders VALUES (UUID_V7(), NOW(), 42.50);
DO SLEEP(0.01);
INSERT INTO orders VALUES (UUID_V7(), NOW(), 19.99);

SELECT UUID_VERSION(id) AS version, total FROM orders ORDER BY id;
```

```
+---------+-------+
| version | total |
+---------+-------+
|       7 | 42.50 |
|       7 | 19.99 |
+---------+-------+
```

Derive a stable identifier from a name:

```sql theme={null}
SELECT UUID_V5(UUID_NS_DNS(), 'villagesql.com') AS dns_uuid;
```

```
+--------------------------------------+
| dns_uuid                             |
+--------------------------------------+
| fc51a94b-5261-5a4c-a5ec-48199b58d67b |
+--------------------------------------+
```

> [!NOTE]
>
> The ordering above holds only across milliseconds. Version 7 puts a
> millisecond timestamp at the front and fills the rest randomly, with no
> counter, so two values generated in the same millisecond sort in a random
> order relative to each other. That is why the example inserts the two rows in
> separate statements. Use `UUID_V6()` when you need ordering finer than that.

> [!NOTE]
>
> A `uuid` column accepts a string literal, but not a string expression. So
> `INSERT INTO orders VALUES ('550e8400-e29b-41d4-a716-446655440000', ...)`
> works, while `INSERT INTO orders VALUES (UUID(), ...)` fails with
> `ERROR 3219 (HY000): Incorrect uuid value: cannot implicitly cast string
>     expression. Use explicit conversion for column 'id' at row 1`. Generate the
> value with one of the functions above instead.

## See also

* [UUIDs in MySQL](https://villagesql.com/docs/guides/uuids.md) — what each version is for and which to choose
* [Primary key strategies](https://villagesql.com/docs/guides/primary-key-strategies.md) — where a UUID key helps and where it costs
* [Install extensions](https://villagesql.com/docs/mysql-8.4/0.0.6/install.md) — how `INSTALL EXTENSION` works and where the server looks for a bundle
* [Available extensions](https://villagesql.com/docs/mysql-8.4/0.0.6/extensions.md) — the full catalog
* [villagesql/vsql-uuid](https://github.com/villagesql/vsql-uuid) — source, build instructions, and the known limitations


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.