UUID()

Returns a Universal Unique Identifier (UUID) generated according to RFC 4122, “A Universally Unique IDentifier (UUID) URN Namespace”.

Description

Returns a Universal Unique Identifier (UUID) generated according to RFC 4122, “A Universally Unique IDentifier (UUID) URN Namespace”.

A UUID is designed as a number that is globally unique in space and time. Two calls to UUID() are expected to generate two different values, even if these calls are performed on two separate devices not connected to each other.

Note

Although UUID() values are intended to be unique, they are not necessarily unguessable or unpredictable. If unpredictability is required, UUID values should be generated some other way.

UUID() returns a value that conforms to UUID version 1 as described in RFC 4122. The value is a 128-bit number represented as a utf8mb3 string of five hexadecimal numbers in aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee format:

  • The first three numbers are generated from the low, middle, and high parts of a timestamp. The high part also includes the UUID version number.

  • The fourth number preserves temporal uniqueness in case the timestamp value loses monotonicity (for example, due to daylight saving time).

  • The fifth number is an IEEE 802 node number that provides spatial uniqueness. A random number is substituted if the latter is not available (for example, because the host device has no Ethernet card, or it is unknown how to find the hardware address of an interface on the host operating system). In this case, spatial uniqueness cannot be guaranteed. Nevertheless, a collision should have very low probability.

UUID is both a data type and a function. For more information on UUID data types, see UUID Type.

Syntax

> UUID()

Examples

drop table if exists t1;
create table t1(a INT,  b float);
insert into t1 values(12124, -4213.413), (12124, -42413.409);

mysql> SELECT length(uuid()) FROM t1;
+----------------+
| length(uuid()) |
+----------------+
|             36 |
|             36 |
+----------------+
2 rows in set (0.00 sec)

mysql> SELECT UUID();
+--------------------------------------+
| uuid()                               |
+--------------------------------------+
| b293b688-70a7-11ed-a25a-5ad2460dea50 |
+--------------------------------------+
1 row in set (0.00 sec)

IS_UUID()

Description

IS_UUID(str) tests whether a string is a valid UUID. Returns 1 if the string is a valid UUID, 0 otherwise, and NULL if the argument is NULL.

The following UUID formats are recognized:

  • Standard dashed format: 6ccd780c-baba-1026-9564-5b8c656024db

  • No-dash format (32 hex digits): 6ccd780cbaba102695645b8c656024db

  • Braced format: {6ccd780c-baba-1026-9564-5b8c656024db}

  • Uppercase format: 6CCD780C-BABA-1026-9564-5B8C656024DB

The function validates the length and hex character composition. Strings with extra whitespace, invalid characters, or incorrect byte lengths return 0.

Syntax

> IS_UUID(str)

Arguments

Arguments

Description

str

Required. The string to test.

Examples

DROP DATABASE IF EXISTS uuid_test;
CREATE DATABASE uuid_test;
USE uuid_test;

-- Standard UUID format
SELECT IS_UUID('6ccd780c-baba-1026-9564-5b8c656024db');

-- No-dash format
SELECT IS_UUID('6ccd780cbaba102695645b8c656024db');

-- Braced format
SELECT IS_UUID('{6ccd780c-baba-1026-9564-5b8c656024db}');

-- Uppercase variant
SELECT IS_UUID('6CCD780C-BABA-1026-9564-5B8C656024DB');

-- URN format (not recognized as valid)
SELECT IS_UUID('urn:uuid:6ccd780c-baba-1026-9564-5b8c656024db');

-- Invalid: too short
SELECT IS_UUID('6ccd780c-baba-1026-9564-5b8c656024d');

-- Invalid: non-hex character
SELECT IS_UUID('6ccd780c-baba-1026-9564-5b8c656024dz');

-- Invalid: wrong byte count
SELECT IS_UUID('6ccd780c-baba-1026-95645b8c656024db');

-- Empty string
SELECT IS_UUID('');

-- Non-UUID string
SELECT IS_UUID('not-a-uuid');

-- NULL argument
SELECT IS_UUID(NULL);

DROP DATABASE uuid_test;

UUID_TO_BIN()

Description

UUID_TO_BIN(str [, swap_flag]) converts a UUID string to a 16-byte binary representation. The optional swap_flag controls byte ordering:

  • swap_flag = 0 (default): Preserves the original byte order as in the UUID string.

  • swap_flag = 1: Moves the time-high field before time-mid and time-low. For time-based UUIDs, placing the most significant time bits first can improve B-tree index locality.

The swap_flag can be a numeric value or a string that evaluates to a number. Zero disables swapping; any nonzero value, including fractional and negative values, enables it. An invalid numeric string causes an error. If either str or swap_flag is NULL, the function returns NULL.

Syntax

> UUID_TO_BIN(str [, swap_flag])

Arguments

Arguments

Description

str

Required. A valid UUID string in dashed, no-dash, braced, or uppercase format.

swap_flag

Optional. 0 to preserve byte order (default); any nonzero value moves time-high before time-mid and time-low for indexing.

Examples

DROP DATABASE IF EXISTS uuid_test;
CREATE DATABASE uuid_test;
USE uuid_test;

-- Default byte order
SELECT HEX(UUID_TO_BIN('6ccd780c-baba-1026-9564-5b8c656024db'));

-- No-dash UUID
SELECT HEX(UUID_TO_BIN('6ccd780cbaba102695645b8c656024db'));

-- Braced UUID
SELECT HEX(UUID_TO_BIN('{6ccd780c-baba-1026-9564-5b8c656024db}'));

-- Uppercase UUID
SELECT HEX(UUID_TO_BIN('6CCD780C-BABA-1026-9564-5B8C656024DB'));

-- Swapped byte order (swap_flag = 1)
SELECT HEX(UUID_TO_BIN('6ccd780c-baba-1026-9564-5b8c656024db', 1));

-- NULL UUID string
SELECT HEX(UUID_TO_BIN(NULL));

-- NULL swap_flag (returns NULL)
SELECT HEX(UUID_TO_BIN('6ccd780c-baba-1026-9564-5b8c656024db', NULL));

DROP DATABASE uuid_test;

BIN_TO_UUID()

Description

BIN_TO_UUID(bin [, swap_flag]) converts a 16-byte binary representation back to a UUID string in dashed format (aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee). The optional swap_flag must match the flag used in the corresponding UUID_TO_BIN() call:

  • swap_flag = 0 (default): Assumes the binary value was produced with the original byte order.

  • swap_flag = 1: Reverses the time-field reordering performed by UUID_TO_BIN(..., 1), restoring the original UUID string order.

The input bin must be exactly 16 bytes. Shorter or longer binary values produce an error. If either bin or swap_flag is NULL, the function returns NULL.

Syntax

> BIN_TO_UUID(bin [, swap_flag])

Arguments

Arguments

Description

bin

Required. A 16-byte VARBINARY value produced by UUID_TO_BIN().

swap_flag

Optional. Must match the flag used in UUID_TO_BIN(). 0 selects original order (default); any nonzero value selects swapped order.

Examples

DROP DATABASE IF EXISTS uuid_test;
CREATE DATABASE uuid_test;
USE uuid_test;

-- Round-trip with default byte order
SELECT BIN_TO_UUID(UUID_TO_BIN('6ccd780c-baba-1026-9564-5b8c656024db'));

-- Round-trip with swap_flag = 1
SELECT BIN_TO_UUID(UUID_TO_BIN('6ccd780c-baba-1026-9564-5b8c656024db', 1), 1);

-- Cross-mismatched swap_flag (produces incorrect result)
SELECT BIN_TO_UUID(UUID_TO_BIN('6ccd780c-baba-1026-9564-5b8c656024db', 1), 0);

-- NULL binary
SELECT BIN_TO_UUID(NULL);

DROP DATABASE uuid_test;

Constraints

UUID() takes no arguments in both MatrixOne and MySQL 8.0.