UUID Type¶

A UUID is a universally unique identifier consisting of 32 hexadecimal digits followed by 4 hyphens. UUIDs are globally unique, not unique in the database, and even performing a UUID call on two unconnected, standalone devices is expected to generate two different values. UUID is often used to generate a random value for the corresponding row to ensure the uniqueness of each record. . UUIDs are suitable as unique identifiers in a cluster environment.

Note

Although UUID() values are unique, they are not guessable or unpredictable. If unpredictability is desired, UUID values should be generated in other ways.

UUID() returns a native UUIDv7 value. It is stored as 16 bytes and rendered by clients as the canonical 36-character form aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee. UUIDv7 places a Unix timestamp in milliseconds first, followed by version, variant, and random bits. It is not a secret and does not guarantee generation order within a millisecond or across devices.

SQL statement supported by UUID type:

DDL - CREATE statement: Can create a table with a field of type UUID.
DML - INSERT/UPDATE/DELETE statement: Allows insert, update and delete operations on UUID type data.
DQL - SELECT statement: UUID type fields can be queried, and clients display the native value in canonical 36-character form.

SQL statement clauses supported by UUID type:

ORDER BY clause: UUID type fields can be used as sorting criteria.
WHERE clause: UUID type fields can be used in the WHERE clause, and comparison operations are supported.
HAVING clause: UUID type fields can be used in the HAVING clause, and comparison operations are supported.
GROUP BY clause: UUID type fields can be used as grouping criteria for GROUP BY.

Other supported UUID type functions:

UUID type field can be set as the table’s primary key.
UUID type fields can be used as parameters of aggregation functions (such as max, min, and count).
UUID type data can be converted between string types.

Examples¶

Example 1:

mysql> select uuid();
+--------------------------------------+
| uuid()                               |
+--------------------------------------+
| 018f0000-0000-7000-8000-000000000001 |
+--------------------------------------+
1 row in set (0.00 sec)

Example 2:

drop table if exists t1;
-- Create a new table 't1' with two columns 'a' and 'b', column 'a' is of type INT and column 'b' is of type float
create table t1(a INT, b float);

-- Insert two rows into table 't1'
insert into t1 values(12124, -4213.413), (12124, -42413.409);

-- Query the number of rows in the 't1' table, and generate the length of the uuid() function of each row. Note that the uuid() function generates a new UUID here.
-- does not depend on any data from the 't1' table, the returned length is 36 because the UUID is a 36 character string with 32 characters and 4 dashes
mysql> SELECT length(uuid()) FROM t1;

+----------------+
|length(uuid())|
+----------------+
| 36 |
| 36 |
+----------------+
2 rows in set (0.00 sec)

Example 3:

-- Create a table named 't1' with a column named 'a' of data type VARCHAR with a maximum length of 20
create table t1(a varchar(20));

-- Insert a row of data in the 't1' table, the value of the 'a' column is '123123sdafsdf'
insert into t1 values('123123sdafsdf');

-- Select all rows from table 't1' and return the value of column 'a' for each row, and a newly generated UUID value
mysql> select uuid(), a from t1;
+--------------------------------------+---------------+
| uuid()                               | a             |
+--------------------------------------+---------------+
| 018f0000-0000-7000-8000-000000000002 | 123123sdafsdf |
+--------------------------------------+---------------+
1 row in set (0.01 sec)

Example 4:

-- Create a table named namelists with two fields, id and name
CREATE TABLE namelists (
    id UUID DEFAULT uuid() PRIMARY KEY, -- the id field is of UUID type, and the default value is the UUID value generated by the uuid() function as the primary key
    name VARCHAR NOT NULL -- the name field is of type VARCHAR and cannot be empty
);
INSERT INTO namelists (name) VALUES ('Tom'), ('Jane'), ('Bob');
mysql> select * from namelists;
+--------------------------------------+--------+
| id                                   | name   |
+--------------------------------------+--------+
| 018f0000-0000-7000-8000-000000000003 | Tom    |
| 018f0000-0000-7000-8000-000000000004 | Jane   |
| 018f0000-0000-7000-8000-000000000005 | Bob    |
+--------------------------------------+--------+
3 rows in set (0.00 sec)