MySQL - UUID

Table of content

The MySQL UUID function

The MySQL UUID() function is used to generate "Universal Unique Identifiers" (UUIDs) in accordance with RFC 4122. UUIDs are designed to be universally unique, even when generated on different servers. The UUID is generated using a combination of the current timestamp, the unique identifier of the server, and a random number.

UUID Format

The UUID value is represented as a UTF-8 string and is a 128-bit number. The format of the UUID value is in hexadecimal number, and it consists of five segments which are separated by hyphens.

The general format of the UUID value is: aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee, where each segment represents a hexadecimal value.

Generating a UUID

Following is the basic example to generate a UUID using the UUID() function in MySQL −

SELECT UUID();

Output

It will display a universal unique identifier as shown below −

UUID()
55f7685d-e99c-11ed-adfc-88a4c2bbd1f9

Generating Multiple UUIDs

You can generate multiple UUIDs in a single query, and each UUID will be different as shown below −

SELECT UUID() AS ID1, UUID() AS ID2;

Output

The output will show two different UUIDs, with differences generally in the first segment −

ID1 ID2
78c3fb43-e99c-11ed-adfc-88a4c2bbd1f9 78c3fb4f-e99c-11ed-adfc-88a4c2bbd1f9

UUIDs in a Database Table

You can use UUIDs as unique identifiers in a database table. Following is an example of how to create a table with a UUID column and insert data −

Here, we are first creating a table with the name "ORDERS", with an ORDER_ID column of type VARCHAR using the following query −

CREATE TABLE ORDERS(
   ID int auto_increment primary key,
   NAME varchar(40),
   PRODUCT varchar(40),
   ORDER_ID varchar(100)
);

Now, we are inserting data into the ORDERS table, using the UUID() function to generate unique values for the ORDER_ID column −

INSERT INTO ORDERS (NAME, PRODUCT, ORDER_ID) VALUES ("Varun", "Headphones", UUID());
INSERT INTO ORDERS (NAME, PRODUCT, ORDER_ID) VALUES ("Priya", "Mouse", UUID());
INSERT INTO ORDERS (NAME, PRODUCT, ORDER_ID) VALUES ("Nikhil", "Monitor", UUID());
INSERT INTO ORDERS (NAME, PRODUCT, ORDER_ID) VALUES ("Sarah", "Keyboard", UUID());
INSERT INTO ORDERS (NAME, PRODUCT, ORDER_ID) VALUES ("Vaidhya", "Printer", UUID());

Following is the ORDERS table obtained −

ID NAME PRODUCT ORDER_ID
1 Varun Headphones a45a9632-e99d-11ed-adfc-88a4c2bbd1f9
2 Priya Mouse a45b03a3-e99d-11ed-adfc-88a4c2bbd1f9
3 Nikhil Monitor a45b49cc-e99d-11ed-adfc-88a4c2bbd1f9
4 Sarah Keyboard a45b8d3f-e99d-11ed-adfc-88a4c2bbd1f9
5 Vaidhya Printer a4b003d0-e99d-11ed-adfc-88a4c2bbd1f9

Modifying UUIDs

You can modify UUIDs without losing their uniqueness. For example, you can remove hyphens or convert them to base64 notation using functions like REPLACE() and TO_BASE64().

Example

Here, we are updating the UUID value for the record where ID = 1 using the following query −

UPDATE ORDERS SET ORDER_ID = UUID() WHERE ID=1;

Output

Following is the output of the above code −

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verification

To verify the modified UUID values, we can use the following SELECT query −

SELECT * FROM ORDERS;

As we can see in the output below, every time we execute the UUID() function, we get a different UUID value −

ID NAME PRODUCT ORDER_ID
1 Varun Headphones 38f4d94a-e99d-11ed-adfc-88a4c2bbd1f9
2 Priya Mouse a45b03a3-e99d-11ed-adfc-88a4c2bbd1f9
3 Nikhil Monitor a45b49cc-e99d-11ed-adfc-88a4c2bbd1f9
4 Sarah Keyboard a45b8d3f-e99d-11ed-adfc-88a4c2bbd1f9
5 Vaidhya Printer a4b003d0-e99d-11ed-adfc-88a4c2bbd1f9

Example

Assume the previously created table and let us remove hyphens from the UUID of the row with ID = 2 using the REPLACE() function as shown below −

UPDATE ORDERS 
SET ORDER_ID = REPLACE(UUID(), '-', '')
WHERE ID = 2;

Output

Output of the above code is as follows −

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verification

To verify the modified UUID value, we can use the following SELECT query −

SELECT * FROM ORDERS;

As we can see in the output below, the UUID of row = 2 is modified without disturbing the "unique" part of it −

ID NAME PRODUCT ORDER_ID
1 Varun Headphones a45a9632-e99d-11ed-adfc-88a4c2bbd1f9
2 Priya Mouse 069b0ca-7e99e11ed-adfc-88a4c2bbd1f9
3 Nikhil Monitor a45b49cc-e99d-11ed-adfc-88a4c2bbd1f9
4 Sarah Keyboard a45b8d3f-e99d-11ed-adfc-88a4c2bbd1f9
5 Vaidhya Printer a4b003d0-e99d-11ed-adfc-88a4c2bbd1f9

Example

In the following query, we are converting the UUID of ID = 4 to base64 notation using the TO_BASE64() function −

UPDATE ORDERS 
SET ORDER_ID = TO_BASE64(UNHEX(REPLACE(UUID(),'-',''))) 
WHERE ID=4;

Output

The result produced is as follows −

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verification

Let us verify the modified UUID value using the following SELECT query −

SELECT * FROM ORDERS;

The output produced is as given below −

ID NAME PRODUCT ORDER_ID
1 Varun Headphones a45a9632-e99d-11ed-adfc-88a4c2bbd1f9
2 Priya Mouse 069b0ca7-e99e11ed-adfc-88a4c2bbd1f9
3 Nikhil Monitor a45b49cc-e99d-11ed-adfc-88a4c2bbd1f9
4 Sarah Keyboard ObRYA+mfEe2t/IikwrvR+Q==
5 Vaidhya Printer a4b003d0-e99d-11ed-adfc-88a4c2bbd1f9
Advertisements