Universally Unique Identifier data type. This type is optimized for storing 128-bit UUID values efficiently.
The UUID data type is intended for the storage of 128-bit UUID (Universally Unique Identifier) data. See the page for more details on UUIDs themselves.
Data retrieved by this data type is in the string representation defined in .
of hexadecimal characters and // can be cast to the UUID data type. Likewise , , and // types can also be cast to UUID.
The data type will not accept a short UUID generated with the function, but will accept a value without the - character generated by the function (or inserted directly). Hyphens can be partially omitted as well, or included after any group of two digits.
The type does not accept UUID values in braces, permitted by some implementations.
UUID values are stored in an index-friendly manner; the order of a UUID of llllllll-mmmm-Vhhh-vsss-nnnnnnnnnnnn is stored as:
This provides a sorting order, assuming a UUIDv1 (node and timestamp) is used, of the node, followed by the timestamp. The key aspect is the timestamps are sequential.
MariaDB starting with
Taking into account that UUIDv7 and other versions are designed around time ordering, UUID values version >= 6 are stored without byte-swapping, and UUID values with version >=8 and variant=0 are now considered invalid (as the SQL standard suggests).
UUID values version >= 6 are not stored without byte-swapping, and UUID values with version >=8 and variant=0 are not considered invalid.
Directly inserting via :
Directly Inserting via :
Generating and inserting via the .
Retrieval:
The function does not generate valid full-length UUID:
Accepting a value without the - character, either directly or generated by the function:
Valid and invalid hyphen and brace usage:
(mariadb.org blog post)
for generating v1 UUIDs
for generating v4 UUIDs
for generating v7 UUIDs
This page is licensed: CC BY-SA / Gnu FDL
UUIDSYS_GUID - UUID without the - character for Oracle compatibility
nnnnnnnnnnnn-vsss-Vhhh-mmmm-llllllllCREATE TABLE t1 (id UUID);INSERT INTO t1 VALUES('123e4567-e89b-12d3-a456-426655440000');INSERT INTO t1 VALUES (x'fffffffffffffffffffffffffffffffe');INSERT INTO t1 VALUES (UUID());SELECT * FROM t1;
+--------------------------------------+
| id |
+--------------------------------------+
| 123e4567-e89b-12d3-a456-426655440000 |
| ffffffff-ffff-ffff-ffff-fffffffffffe |
| 93aac041-1a14-11ec-ab4e-f859713e4be4 |
+--------------------------------------+INSERT INTO t1 VALUES (UUID_SHORT());
ERROR 1292 (22007): Incorrect uuid value: '99440417627439104'
for column `test`.`t1`.`id` at row 1INSERT INTO t1 VALUES (SYS_GUID());
SELECT * FROM t1;
+--------------------------------------+
| id |
+--------------------------------------+
| 123e4567-e89b-12d3-a456-426655440000 |
| ffffffff-ffff-ffff-ffff-fffffffffffe |
| 93aac041-1a14-11ec-ab4e-f859713e4be4 |
| ea0368d3-1a14-11ec-ab4e-f859713e4be4 |
+--------------------------------------+
SELECT SYS_GUID();
+----------------------------------+
| SYS_GUID() |
+----------------------------------+
| ff5b6bcc1a1411ecab4ef859713e4be4 |
+----------------------------------+
INSERT INTO t1 VALUES ('ff5b6bcc1a1411ecab4ef859713e4be4');
SELECT * FROM t1;
+--------------------------------------+
| id |
+--------------------------------------+
| 123e4567-e89b-12d3-a456-426655440000 |
| ffffffff-ffff-ffff-ffff-fffffffffffe |
| 93aac041-1a14-11ec-ab4e-f859713e4be4 |
| ea0368d3-1a14-11ec-ab4e-f859713e4be4 |
| ff5b6bcc-1a14-11ec-ab4e-f859713e4be4 |
+--------------------------------------+TRUNCATE t1;
INSERT INTO t1 VALUES ('f8aa-ed66-1a1b-11ec-ab4e-f859-713e-4be4');
INSERT INTO t1 VALUES ('1b80667f1a1c-11ecab4ef859713e4be4');
INSERT INTO t1 VALUES ('2fd6c945-1a-1c-11ec-ab4e-f859713e4be4');
INSERT INTO t1 VALUES ('49-c9-f9-59-1a-1c-11ec-ab4e-f859713e4be4');
INSERT INTO t1 VALUES ('57-96-da-c1-1a-1c-11-ec-ab-4e-f8-59-71-3e-4b-e4');
INSERT INTO t1 VALUES ('6-eb74f8f-1a1c-11ec-ab4e-f859713e4be4');
INSERT INTO t1 VALUES ('{29bad136-1a1d-11ec-ab4e-f859713e4be4}');
ERROR 1292 (22007): Incorrect uuid value: '{29bad136-1a1d-11ec-ab4e-f859713e4be4}'
for column `test`.`t1`.`id` at row 1
SELECT * FROM t1;
+--------------------------------------+
| id |
+--------------------------------------+
| f8aaed66-1a1b-11ec-ab4e-f859713e4be4 |
| 1b80667f-1a1c-11ec-ab4e-f859713e4be4 |
| 2fd6c945-1a1c-11ec-ab4e-f859713e4be4 |
| 49c9f959-1a1c-11ec-ab4e-f859713e4be4 |
| 5796dac1-1a1c-11ec-ab4e-f859713e4be4 |
| 6eb74f8f-1a1c-11ec-ab4e-f859713e4be4 |
+--------------------------------------+