MySQL UUID() – Generate Unique Identifiers in MySQL

By /
UUID Function

In this tutorial, we will study the MySQL UUID() function. You must have heard of unique identification numbers or identifiers. Applications extend from your exam seat numbers to your Aadhar Card numbers/SSN Numbers and so on.

Suppose you have a MySQL table of people and you have been tasked with assigning each one of them a unique identifier. There are many ways you can do this – manually using random numbers taking care that the value doesn’t repeat and so on. However, MySQL makes it easy for us. It provides us with the MySQL UUID() function.

The MySQL UUID() function is used to return a Universal Unique Identifier (UUID) generated according to RFC 4122, “A Universally Unique IDentifier (UUID) URN Namespace”.

A key point to note about UUIDs is that they are designed such that they are globally unique in space and time. Two UUIDs can never be the same even if the function is run on two different devices.

A UUID value in MySQL is a 128-bit number represented as a utf8 string of five hexadecimal numbers separated by a ‘-’. The format is as follows –

aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee

Let us take a look at the syntax and a few examples of this function.

Syntax of MySQL UUID()

UUID()Code language: SQL (Structured Query Language) (sql)

Example of MySQL UUID()

Let us kick things off with a basic example. Using the SELECT statement and the UUID() function, we will display a universal unique identifier.

SELECT UUID();Code language: SQL (Structured Query Language) (sql)

And the output is –

Uuid Basic Example

Displaying Two Different UUIDs

Let us display two UUIDs in a single query now.

SELECT UUID() AS ID1, UUID() as ID2;Code language: SQL (Structured Query Language) (sql)

And the output is –

Uuid Twice

As you can see, we get two different UUIDs. The difference is in the first part of the UUID.

Working With Tables

Consider the below ‘Persons’ table.

Persons Table MySQL UUID
Persons Table

Let us create a column called UID which stores a unique identifier that we get from the UUID() function for each record. We will use ALTER and UPDATE statements for this.

ALTER TABLE Persons ADD UID text; 
UPDATE Persons SET UID=UUID(); 
SELECT * FROM Persons;Code language: SQL (Structured Query Language) (sql)

We create a column UID of type text using the ALTER statement. Then we use the UPDATE statement to set a UUID value for each row and finally, we use the SELECT statement to display the updated table. The output is –

Persons Table Example Wrong

Now let us update the UID value for the record where ID is 2. The query is –

UPDATE Persons SET UID=UUID() WHERE ID=2; SELECT * FROM Persons;Code language: SQL (Structured Query Language) (sql)

And the output is –

Persons Table Example Right MySQL UUID

As you can see, every time we run the UUID() function, we get a different UUID.

Customizing UUIDs

We saw the different UUIDs returned above which are readable characters. However, the four dashes in the UUID are essentially superfluous. We have the ability to modify UUIDs without disturbing the “unique” aspect of it.

Here is one way in which we can make our UUID more compact using the REPLACE() function. We will update the UUID of the row with ID = 3. The query is –

UPDATE Persons SET UID=REPLACE(UUID(), '-', '') WHERE ID=3; 
SELECT * FROM Persons;Code language: SQL (Structured Query Language) (sql)

And the output is –

Persons Table Example Right2 MySQL UUID

As you can see, we made our UUID more compact, and customized it without hurting the “unique” part of it.

There is another way to customize your UUID without changing the “unique” bit.

I mentioned in the beginning that UUID is a 128-bit number represented as a utf8 string of five hexadecimal numbers.

We can convert it to other notations. In the below query, we convert it to base64 notation using the TO_BASE64 function. We will also stick with the previous logic of the REPLACE() function and remove the four dashes. The query is –

UPDATE Persons SET UID=TO_BASE64(UNHEX(REPLACE(UUID(),'-',''))) WHERE ID=4; S
ELECT * FROM Persons;Code language: SQL (Structured Query Language) (sql)

And the output is –

Persons Table Example Right3 MySQL UUID

Conclusion

Generating unique identifiers or UUIDs has an array of applications. The MySQL UUID() function is very important and I encourage you to check the below references for more detailed information.

References