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.

Info

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 version 1 UUID value conforming to the RFC 4122 standard, which is a 128-bit number, which represents a utf8mb3 composed of five A string of hexadecimal numbers, that is, aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee, the format is explained as follows:

  • 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.

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 the query results are displayed as strings on the client side.

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:
  1. mysql> select uuid();
  2. +--------------------------------------+
  3. | uuid()                               |
  4. +--------------------------------------+
  5. | 4aa4f4de-1b00-11ee-b656-5ad2460dea50 |
  6. +--------------------------------------+
  7. 1 row in set (0.00 sec)
  • Example 2:
  1. drop table if exists t1;
  2. -- Create a new table 't1' with two columns 'a' and 'b', column 'a' is of type INT and column 'b' is of type float
  3. create table t1(a INT, b float);
  5. -- Insert two rows into table 't1'
  6. insert into t1 values(12124, -4213.413), (12124, -42413.409);
  8. -- 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.
  9. -- 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
  10. mysql> SELECT length(uuid()) FROM t1;
  12. +----------------+
  13. |length(uuid())|
  14. +----------------+
  15. | 36 |
  16. | 36 |
  17. +----------------+
  18. 2 rows in set (0.00 sec)
  • Example 3:
  1. -- Create a table named 't1' with a column named 'a' of data type VARCHAR with a maximum length of 20
  2. create table t1(a varchar(20));
  4. -- Insert a row of data in the 't1' table, the value of the 'a' column is '123123sdafsdf'
  5. insert into t1 values('123123sdafsdf');
  7. -- Select all rows from table 't1' and return the value of column 'a' for each row, and a newly generated UUID value
  8. mysql> select uuid(), a from t1;
  9. +-------------------------------------+---------- -----+
  10. | uuid() | a |
  11. +-------------------------------------+---------- -----+
  12. | 664f1a96-1981-11ee-a041-5ad2460dea50 | 123123sdafsdf |
  13. +-------------------------------------+---------- -----+
  14. 1 row in set (0.01 sec)
  • Example 4:
  1. -- Create a table named namelists with two fields, id and name
  2. CREATE TABLE namelists (
  3.     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
  4.     name VARCHAR NOT NULL -- the name field is of type VARCHAR and cannot be empty
  5. );
  6. INSERT INTO namelists (name) VALUES ('Tom'), ('Jane'), ('Bob');
  7. mysql> select * from namelists;
  8. +--------------------------------------+--------+
  9. | id                                   | name   |
  10. +--------------------------------------+--------+
  11. | 61400e9c-1bbc-11ee-b512-5ad2460dea50 | Tom    |
  12. | 61400ea6-1bbc-11ee-b512-5ad2460dea50 | Jane   |
  13. | 61400ea6-1bbc-11ee-b513-5ad2460dea50 | Bob    |
  14. +--------------------------------------+--------+
  15. 3 rows in set (0.00 sec)