UUID Data Type - Source

Revision: 135891
User: Daniel Black
Date: 2023-10-07 12:10

<<product mariadb from=10.7>>
The UUID data type was added in MariaDB 10.7.
<</product>>

== Syntax

<<code>>
UUID
<</code>>
<<toc>>

== Description
The ##UUID## data type is intended for the storage of 128-bit UUID (Universally Unique Identifier) data. See the [[uuid|UUID function]] page for more details on UUIDs themselves.

=== Retrieval
Data retrieved by this data type is in the string representation defined in [[https://datatracker.ietf.org/doc/html/rfc4122|RFC4122]].

=== Casting
[[string-literals|String literals]] of hexadecimal characters and [[CHAR]]/[[VARCHAR]]/[[TEXT]] can be cast to the UUID data type. Likewise [[hexadecimal-literals|hexadecimal literals]], [[binary-literals]], and [[BINARY]]/[[VARBINARY]]/[[BLOB]] types can also be cast to UUID.

The data type will not accept a short UUID generated with the [[uuid_short|UUID_SHORT]] function, but will accept a value without the ##-## character generated by the [[sys_guid|SYS_GUID]] function (or inserted directly). Hyphens can be partially omitted as well, or included after any group of two digits.

The type does not accept UUIDs in braces, permitted by some implementations.

=== Storage
UUID are stored in an index friendly manner, the order of a UUID of llllllll-mmmm-Vhhh-vsss-nnnnnnnnnnnn is stored as:

<<code>>
nnnnnnnnnnnn-vsss-Vhhh-mmmm-llllllll
<</code>>

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.

<<product mariadb from=10.10>>
Starting from MariaDB 10.10.6 and MariaDB 10.11.5, taking into account that UUIDv7 and other versions are designed around time ordering, UUIDs version >= 6 are now stored without byte-swapping, and UUIDs with version >=8 and variant=0 are now considered invalid (as the standard expects)
<</product>>

== Examples
<<sql>>
CREATE TABLE t1 (id UUID);
<</sql>>

Directly Inserting via [[string-literals|string literals]]:
<<sql>>
INSERT INTO t1 VALUES('123e4567-e89b-12d3-a456-426655440000');
<</sql>>

Directly Inserting via [[hexadecimal-literals|hexadecimal literals]]:
<<sql>>
INSERT INTO t1 VALUES (x'fffffffffffffffffffffffffffffffe');
<</sql>>

Generating and inserting via the [[uuid|UUID function]].
<<sql>>
INSERT INTO t1 VALUES (UUID());

<</sql>>

Retrieval:
<<sql>>
SELECT * FROM t1;
+--------------------------------------+
| id                                   |
+--------------------------------------+
| 123e4567-e89b-12d3-a456-426655440000 |
| ffffffff-ffff-ffff-ffff-fffffffffffe |
| 93aac041-1a14-11ec-ab4e-f859713e4be4 |
+--------------------------------------+
<</sql>>

The [[uuid_short|UUID_SHORT]] function does not generate valid full-length UUID:

<<sql>>
INSERT INTO t1 VALUES (UUID_SHORT());
ERROR 1292 (22007): Incorrect uuid value: '99440417627439104' 
  for column `test`.`t1`.`id` at row 1
<</sql>>

Accepting a value without the ##-## character, either directly or generated by the [[sys_guid|SYS_GUID]] function:
<<sql>>
INSERT INTO t1 VALUES (SYS_GUID());

SELECT SYS_GUID();
+----------------------------------+
| SYS_GUID()                       |
+----------------------------------+
| ff5b6bcc1a1411ecab4ef859713e4be4 |
+----------------------------------+

INSERT INTO t1 VALUES ('ff5b6bcc1a1411ecab4ef859713e4be4');

Valid and invalid hyphen and brace usage:
<<sql>>
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

== See Also
* [[https://mariadb.org/10-7-preview-feature-uuid-data-type/|10.7 preview feature: UUID Data Type]] (mariadb.org blog post)
* [[uuid|UUID function]]
* [[uuid_short|UUID_SHORT function]]
* [[sys_guid|SYS_GUID]] - UUID without the ##-## character for Oracle compatibility

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

UUID Data Type - Source

Products

Services

Pricing

Resources

About Us

Download MariaDB