Universally unique identifiers (UUIDs) have a common place in computing already. A 128-bit value with a text representational value and the prescription of a number of version types to ensure a uniqueness makes it an ideal data type.
After many years of the UUID datatype existing only as a feature request in MDEV-4958, slowly accumulating votes and watchers, we are pleased to announce a preview of the UUID data type. Like the INET6 data type, this uses the datatype plugin API from MariaDB 10.5.
What does the UUID data type look like?
CREATE TABLE t1 (id UUID)
INSERT INTO TABLE t1 VALUES ('123e4567-e89b-12d3-a456-426655440000')
So for the most part it looks like a string. You can CAST to it:
SELECT CAST('123e4567-e89b-12d3-a456-426655440000' AS UUID)
123e4567-e89b-12d3-a456-426655440000
If you have BINARY(16) data strings those can be cast to the UUID too:
SELECT CAST(x'102030405000000000000000adcefeff' AS UUID) 10203040-5000-0000-0000-0000adcefeff
With these basic conversion rules, you can migrate from your existing CHAR/VARCHAR/TEXT hexadecimal text or BINARY/VARBINARY/BLOB encoded to UUID using:
CREATE TABLE t1 (id BINARY(16)); ALTER TABLE t1 MODIFY COLUMN id UUID;
This also works with replication; the primary/replica can be of these different types and it converts.
Like all data types, you can have default values:
CREATE TABLE t1 ( c1 UUID DEFAULT 0x00000000000000000000000000000000, c2 UUID DEFAULT 0xFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF, c3 UUID DEFAULT '00000000-0000-0000-0000-000000000000', c4 UUID DEFAULT 'ffffffff-ffff-ffff-ffff-ffffffffffff', c5 UUID DEFAULT CAST(X'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF' AS UUID) );
Similarly, you can expect warnings if you haven’t got the right format:
SELECT CAST('longlive-that-bear-a456-426655440000' AS UUID);
NULL
SHOW WARNINGS;
Level Code Message
Warning 1292 Incorrect uuid value: 'longlive-that-bear-a456-426655440000'
The metadata of the datatype is also a string of type uuid.
$ mariadb --column-type-info --execute "SELECT UUID()" Field 1: `UUID()` Catalog: `def` Database: `` Table: `` Org_table: `` Type: STRING (type=uuid) Collation: utf8mb3_general_ci (33) Length: 108 Max_length: 36 Decimals: 39 Flags: +--------------------------------------+ | UUID() | +--------------------------------------+ | 3ca2cd9a-15c9-11ec-9351-3c58c2a5ca80 | +--------------------------------------+
How to try the 10.7 UUID data type preview yourself?
We’ve prepared three ways you can try this feature out.
Tarball
Go to tarball downloads
Container
You can run the container called quay.io/mariadb-foundation/mariadb-dev:10.7-mdev-4958-uuid with the same interface as the Docker Library mariadb image. Here I’ve used podman to run the container and run the mariadb command line in the container:
$ podman run --rm -d -e MARIADB_USER=bob \ -e MARIADB_PASSWORD=entry -e MARIADB_DATABASE=uuidtest \ -e MARIADB_RANDOM_ROOT_PASSWORD=1 \ --name mariadb-uuid \ quay.io/mariadb-foundation/mariadb-devel:10.7-mdev-4958-uuid feab2044def356e03e594e6604fed8f29037c3aafdd10bbcbcf82abca4866f74 $ podman exec -ti mariadb-uuid mariadb -u bob -pentry uuidtest Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 3 Server version: 10.7.0-MariaDB-MDEV-4958-uuid mariadb.org binary distribution Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [uuidtest]> create table uuidtest ( id uuid not null primary key default(uuid())); Query OK, 0 rows affected (0.016 sec) MariaDB [uuidtest]> insert into uuidtest values (),(),(),(),(); Query OK, 5 rows affected (0.020 sec) Records: 5 Duplicates: 0 Warnings: 0 MariaDB [uuidtest]> select * from uuidtest; +--------------------------------------+ | id | +--------------------------------------+ | fd08a402-16c0-11ec-a724-7e502e50dded | | fd08a9f6-16c0-11ec-a724-7e502e50dded | | fd08ab9f-16c0-11ec-a724-7e502e50dded | | fd08abfe-16c0-11ec-a724-7e502e50dded | | fd08ac3d-16c0-11ec-a724-7e502e50dded | +--------------------------------------+ 5 rows in set (0.001 sec)
Binder online MariaDB Jupyter Notebook
A complete running instance is available in your web browser with Binder. Please be patient with the link, it takes a small time to load.
Feedback Welcome
If you come across any problems in this feature preview, with the design, or edge cases that don’t work as expected, please let us know with a JIRA bug/feature request on the MDEV project. You are welcome to chat about it on Zulip.