Skip to content

UUID Type

A UUID is a universally unique identifier consisting of 32-bit hexadecimal numbers and 4-bit hyphens. UUIDs have global uniqueness, not uniqueness in the database, and even if a UUID call is executed on two unconnected, independently running devices, two different values ​​are expected to be generated. UUID is often used to generate a random value for the corresponding row to ensure the uniqueness of each record. UUID is suitable as a unique identifier in a cluster environment.

Info

Although the UUID() values ​​are unique, they are not unguessable or unpredictable. If unpredictability is required, the UUID value should be generated in other ways.

UUID() returns a value of version 1 UUID that conforms to the RFC 4122 standard, which is a 128-bit number. It indicates that it is a utf8mb3 string composed of five hexadecimal digits, namely aaaaaaa-bbbb-cccc-ddd-eeeeeeeeeeeeee. The format is explained as follows:

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

  • The fourth number retains time uniqueness in case the timestamp value loses singularity (e.g., daylight saving time).

  • The fifth number is the IEEE 802 node number of spatial uniqueness. If the latter is not available (e.g., because the host device does not have an Ethernet card, or does not know how to find the hardware address of the interface on the host operating system), then a random number is replaced. In this case, the uniqueness of the space cannot be guaranteed. However, the probability of the fifth digit overlap is very low.

Supported SQL statements by UUID type:

-DDL - CREATE statement: You can create tables with UUID type fields. -DML - INSERT/UPDATE/DELETE statement: Allows insertion, update and delete data of UUID type. -DQL - SELECT statement: You can query the UUID type field, and the query results are displayed in string form on the client.

Supported SQL statement clauses by UUID type:

-ORDER BY clause: The UUID type field can be used as sorting conditions. -WHERE clause: The UUID type field can be used in the WHERE clause and supports comparison operations. -HAVING clause: The UUID type field can be used in the HAVING clause and supports comparison operations. -GROUP BY clause: The UUID type field can be used as a grouping condition for GROUP BY.

Other supported UUID type features:

  • The UUID type field can be set as the primary key of the table.
  • UUID type fields can be used as parameters for aggregate functions (such as max, min, count).
  • UUID type data can be converted between string types.

Example explanation

  • Example 1
mysql> select uuid();
+---------------------------------------------+
| uuid() |
+---------------------------------------------+
| 4aa4f4de-1b00-11ee-b656-5ad2460dea50 |
+---------------------------------------------+
1 row in set (0.00 sec)
  • Example 2
drop table if exists t1;
-- Create a new table 't1', containing two columns 'a' and 'b', the column 'a' is of type INT, and the column 'b' is of type float
create table t1(a INT, b float);

-- Insert two rows of data 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.
-- No dependency on any data in the 't1' table, the returned length is 36, because UUID is a 36-character string containing 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' with a data type VARCHAR and 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 the 't1' table and return the value of the 'a' column for each row, as well as a newly generated UUID value
mysql> select uuid(),a from t1;
+---------------------------------------------------------------------+
| uuid() | a |
+---------------------------------------------------------------------+
| 664f1a96-1981-11ee-a041-5ad2460dea50 | 123123sdafsdf |
+---------------------------------------------------------------------+
1 row in set (0.01 sec)
  • Example 4:
-- Create a table named namelists, containing two fields: id and name
CREATE TABLE names (
    id UUID DEFAULT uuid() PRIMARY KEY, -- The id field is the 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 nameslists (name) VALUES ('Tom'), ('Jane'), ('Bob');
mysql> select * from nameslists;
+------------------------------------------+
| id | name |
+------------------------------------------+
| 61400e9c-1bbc-11ee-b512-5ad2460dea50 | Tom |
| 61400ea6-1bbc-11ee-b512-5ad2460dea50 | Jane |
| 61400ea6-1bbc-11ee-b513-5ad2460dea50 | Bob |
+------------------------------------------+
3 rows in set (0.00 sec)