UUID() function in MySQL

This function in MySQL is used to return a Universal Unique Identifier (UUID) generated according to RFC 4122, “A Universally Unique Identifier (UUID) URN Namespace”.It is designed as a number that is universally unique. Two UUID values are expected to be distinct, even they are generated on two independent servers. In MySQL, a UUID value is a 128-bit number represented as a utf8 string, and the format in hexadecimal number will be as follows.

Example –

aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee

Here, the first three numbers are generated from the low, middle, and high parts of a timestamp. As you can see that in the above format fourth place digits code will preserve temporal uniqueness and fifth place code will provide spatial uniqueness and it is an IEEE 802 node number.

Syntax :

UUID()

Parameter :
This method does not any parameter.
Returns :
It returns a Universal Unique Identifier number.

Example-1 :
Generating a Universal Unique Identifier value with the help of UUID Function.

SELECT UUID() 
AS UUID_Value ;

Output :

UUID_VALUE
fbe516f6-3e39-11eb-b897-0862660ccbd4

Example-2 :
Whenever we will use UUID function we will get different Universal Unique Identifier value. Lets check it.

  SELECT UUID() 
AS  
UUID_VALUE1, 
UUID() 
AS UUID_VALUE2, 
UUID() 
AS UUID_VALUE3 ;

Output :

UUID_VALUE1	UUID_VALUE2	UUID_VALUE3
e762634c-3e41-11eb-b897-0862660ccbd4	e7626367-3e41-11eb-b897-0862660ccbd4	e7626368-3e41-11eb-b897-0862660ccbd4

Example-3 :
In this example, we will use UUID as a primary key in a table. To demonstrate create a table named OrderDetails.

CREATE TABLE OrderDetails(
   OrderId BINARY(16) PRIMARY KEY,
   ProductName VARCHAR(100) NOT NULL,
   Price DECIMAL(10, 2) NOT NULL,
   ExpectedDelivery DATE NOT NULL
);

Now, inserting data into OrderDetails table. Here, We will use UUID and UUID_TO_BIN() functions to assign the value in OrderId Column.

INSERT INTO OrderDetails(OrderId, ProductName, Price, ExpectedDelivery)
VALUES(UUID_TO_BIN(UUID()), 'Asus Rog', 90000.00, '2020-12-20'),
      (UUID_TO_BIN(UUID()), 'Acer Predator', 100000.00, '2020-12-18'),
      (UUID_TO_BIN(UUID()), 'Lenovo Legion', 85000.00, '2020-12-19'),
      (UUID_TO_BIN(UUID()), 'Hp Omen', 70000.00, '2020-12-18'),
      (UUID_TO_BIN(UUID()), 'Dell Inspiron', 65000.00, '2020-12-23'),
      (UUID_TO_BIN(UUID()), 'Acer Nitro', 60000.00, '2020-12-22'),
      (UUID_TO_BIN(UUID()), 'Asus Tuf', 80000.00, '2020-12-19');

Next we will use the following command to check the table.

SELECT  * from OrderDetails;

Output :

ORDERID	PRODUCTNAME	PRICE	EXPECTEDDELIVERY
0xE50EF0D93E3E11EBB8970862660CCBD4	Asus Rog	90000.00	2020-12-20
0xE514F3293E3E11EBB8970862660CCBD4	Acer Predator	100000.00	2020-12-18
0xE514F6793E3E11EBB8970862660CCBD4	Lenovo Legion	85000.00	2020-12-19
0xE514F7C83E3E11EBB8970862660CCBD4	Hp Omen	70000.00	2020-12-18
0xE514F9173E3E11EBB8970862660CCBD4	Dell Inspiron	65000.00	2020-12-23
0xE514FA7B3E3E11EBB8970862660CCBD4	Acer Nitro	60000.00	2020-12-22
0xE514FC6C3E3E11EBB8970862660CCBD4	Asus Tuf	80000.00	2020-12-19