vsql_uuid extension for MySQL

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
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:

INSTALL EXTENSION vsql_uuid;

Confirm it is there:

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.

What it adds

The uuid type

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

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:

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:

SELECT UUID_V5(UUID_NS_DNS(), 'villagesql.com') AS dns_uuid;

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

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.

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.

Getting Started

Server Development

Using Extensions

Creating Extensions

Install

What it adds

The uuid type

Functions

Example

See also

​Install

​What it adds

​The uuid type

​Functions

​Example

​See also

Install

What it adds

The uuid type

Functions

Example

See also