# UUID Data Type

{% hint style="info" %}
`UUID` is available from MariaDB 10.7.
{% endhint %}

## Syntax

```sql
UUID
```

## Description

The `UUID` data type is intended for the storage of 128-bit UUID (Universally Unique Identifier) data. See the [UUID function](https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/miscellaneous-functions/uuid) page for more details on UUIDs themselves.

### Retrieval

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

### Casting

[String literals](https://mariadb.com/docs/server/reference/sql-structure/sql-language-structure/string-literals) of hexadecimal characters and [CHAR](https://mariadb.com/docs/server/reference/data-types/string-data-types/char)/[VARCHAR](https://mariadb.com/docs/server/reference/data-types/string-data-types/varchar)/[TEXT](https://mariadb.com/docs/server/reference/data-types/string-data-types/text) can be cast to the `UUID` data type. Likewise [hexadecimal literals](https://mariadb.com/docs/server/reference/sql-structure/sql-language-structure/hexadecimal-literals), [binary-literals](https://mariadb.com/docs/server/reference/sql-structure/sql-language-structure/binary-literals), and [BINARY](https://mariadb.com/docs/server/reference/data-types/string-data-types/binary)/[VARBINARY](https://mariadb.com/docs/server/reference/data-types/string-data-types/varbinary)/[BLOB](https://mariadb.com/docs/server/reference/data-types/string-data-types/blob) types can also be cast to `UUID`.

The data type will not accept a short `UUID` generated with the [UUID\_SHORT](https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/miscellaneous-functions/uuid_short) function, but will accept a value without the `-` character generated by the [SYS\_GUID](https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/miscellaneous-functions/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 `UUID` values in braces, permitted by some implementations.

### Storage

`UUID` values are stored in an index-friendly manner; the order of a UUID of llllllll-mmmm-Vhhh-vsss-nnnnnnnnnnnn is stored as:

```sql
nnnnnnnnnnnn-vsss-Vhhh-mmmm-llllllll
```

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** [**10.10**](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/10.10/what-is-mariadb-1010)

{% tabs %}
{% tab title="Current" %}
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).
{% endtab %}

{% tab title="< 10.11.5 / 10.10.6" %}
`UUID` values version >= 6 are **not** stored without byte-swapping, and `UUID` values with version >=8 and variant=0 are **not** considered invalid.
{% endtab %}
{% endtabs %}

## Examples

```sql
CREATE TABLE t1 (id UUID);
```

Directly inserting via [string literals](https://mariadb.com/docs/server/reference/sql-structure/sql-language-structure/string-literals):

```sql
INSERT INTO t1 VALUES('123e4567-e89b-12d3-a456-426655440000');
```

Directly Inserting via [hexadecimal literals](https://mariadb.com/docs/server/reference/sql-structure/sql-language-structure/hexadecimal-literals):

```sql
INSERT INTO t1 VALUES (x'fffffffffffffffffffffffffffffffe');
```

Generating and inserting via the [UUID function](https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/miscellaneous-functions/uuid).

```sql
INSERT INTO t1 VALUES (UUID());
```

Retrieval:

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

The [UUID\_SHORT](https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/miscellaneous-functions/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
```

Accepting a value without the `-` character, either directly or generated by the [SYS\_GUID](https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/miscellaneous-functions/sys_guid) function:

```sql
INSERT 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 |
+--------------------------------------+
```

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

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 |
+--------------------------------------+
```

## See Also

* [10.7 preview feature: UUID Data Type](https://mariadb.org/10-7-preview-feature-uuid-data-type/) (mariadb.org blog post)
* [UUID function](https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/miscellaneous-functions/uuid) for generating v1 UUIDs
* [UUID function](https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/miscellaneous-functions/uuid_v4) for generating v4 UUIDs
* [UUID function](https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/miscellaneous-functions/uuid_v7) for generating v7 UUIDs
* [UUID\_SHORT function](https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/miscellaneous-functions/uuid_short)
* [SYS\_GUID](https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/miscellaneous-functions/sys_guid) - UUID without the `-` character for Oracle compatibility

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}