What is UUID in MySQL?

MySQL is a popular open-source relational database management system used by many developers to store and manage data. In this article, we will look at an important feature of MySQL which is known as UUID.

UUID stands for Universally Unique Identifier and is specified by the Request for Comments 4122 or RFC 4122. It is a 128-bit long value that is unique across the globe and is generated using an algorithm. It is a unique identifier that is used to identify objects, without relying on any external reference.

UUIDs are commonly used in distributed systems, where data is stored in multiple locations and needs to be uniquely identified. These values can't be guessed or predicted and are always unique when you generate them. The maximum length of a MySQL UUID can be 36 bytes.

In this article, we are going to look at the syntax for UUID MySQL, its structure, and various other details.

Syntax of UUID

The syntax for generating a UUID in MySQL is as follows:

This function generates a random UUID value.

Structure of UUID Return value

A 128-bit long unique value is generated whenever you call the UUID() function and this is shown to us in a human-readable format. It is represented by a UTF-8 string which contains 5 different sets of integers separated by a hyphen (-)

You can find the format for the UUID return value below:

Let us learn what each integer group in this format means:

The first three integer groups (11111111-2222-3333) stand for the low, middle, and high timestamp formats, respectively. The high part of this format holds the UUID version number.
When the timestamp loses monotonicity, the fourth integer group (4444) is responsible for maintaining temporal uniqueness.
The IEEE 802 node number, which stands for spatial uniqueness, is represented by the last integer group (555555555555). In cases where this node number is unavailable, it is replaced by a random number but that does not guarantee spatial uniqueness.

Let us look at a sample UUID value:

MySQL UUID vs. AUTO_INCREMENT PRIMARY KEY

We can generate unique values in a table using the AUTO_INCREMENT PRIMARY KEY and the same can be achieved using the UUID() function. So, one can be seen as a replacement or alternative to the other.

In this section, we are going to see the advantages as well as disadvantages of using MySQL UUID() over AUTO_INCREMENT PRIMARY KEY.

Advantages

MySQL UUID not only creates unique values across the table but also across different databases and servers. This feature of MySQL UUID allows us to merge tables and datasets from different servers without any worry of repeating values.
Security is another great feature of MySQL UUID, as it does not provide any info about our data which in turn protects it from hackers and intruders looking to misuse it.
We can use MySQL UUID values inside the URL too.
MySQL UUID's ability to be generated offline makes it much more convenient to use as it eliminates the need to involve the server every time you want to generate a unique value.
Using MySQL UUID also makes certain tasks easier. Let us assume you want to add a row of data in both the parent and child tables. In that case, you'll add the data in the parent table, get the generated ID and then insert the data in the child table corresponding to that ID. With MySQL UUID, you can generate the ID for the parent table and add data in both the parent and child table simultaneously.

Now, we will move ahead and see some of the disadvantages of the MySQL UUID() function.

Disadvantages

One of the biggest disadvantages of using MySQL UUID is that it occupies more space in the memory. It uses 8 bytes of space as compared to 4 bytes occupied by an integer.
Another downside of using MySQL UUID is that it makes it difficult to read or debug your code. For example, it is much easier to see '7' as an ID value than to see 739bcb2d-8b63-12ec-9c44-8c19450c827b.
Using MySQL UUID might cause unwanted performance issues because of its size and complexity.

We have gone through some of the disadvantages of using MySQL UUID over AUTO_INCREMENT PRIMARY KEY. Now in the next section, we are going to look at different ways we can counter or eliminate some of the disadvantages of using MySQL UUID.

MySQL UUID Solutions for Some Issues

There are three functions available in MySQL that we can use to eliminate the disadvantages of using UUID.

UUID_TO_BIN
BIN_TO_UUID
IS_UUID

The main objective of all of these functions is to store the UUID values in a 0,1 or binary format and whenever we want to view these values, we can view them in the VARCHAR format.

NOTE: The above functions can only be used in MySQL 8.0 version or later.

Let us look at these functions one by one.

UUID_TO_BIN

In MySQL, UUID values are converted into binary values by using the UUID_TO_BIN function. This function is used to store UUID values in your database.

We can understand this function with the help of an example, Let us create a Customers table with 2 columns, cus_id (BINARY with size 16) and cus_name with VARCHAR data type. Below is the query for the same.

Now we will insert some data into the table, generate UUID values, and then use the UUID_TO_BIN function to convert them into binary values. Below is the query for the same.

If we look at our table with the SELECT statement now, it will look something like this

OUTPUT:

cus_id	cus_name
0xE79679428C1311EC9C448C16950C837B	Lewis
0xE79699428C1311EC9C448C16950C837B	Alonso
0xE796F9428C1311EC9C448C16950C837B	Vettel
0xE7969928D1311EC9C448C16950C837B	Verstappen

BIN_TO_UUID

BIN_TO_UUID performs the reverse process of the UUID_TO_BIN function. It converts the binary values back to the UUID values. This function is used for displaying the real UUID values.

We will continue working with the Customers table to understand this function. We can use the BIN_TO_UUID function along with the SELECT statement to view the real UUID values.

OUTPUT:

cus_id	cus_name
739bcb2d-8b63-12ec-9c44-8c19450c827b	Lewis
939bcb2d-8b63-12ec-9c44-8c19450c827b	Alonso
939cb2d-8b63-13ec-9c44-8c19450c827b	Vettel
739bcb2d-8b73-12ec-9c44-8c19410c827b	Verstappen

As you can see, all the IDs have been converted back to the UUID.

IS_UUID

IS_UUID is a MySQL function and it is used for validating the string format of the UUID.

Format of the IS_UUID function:

We pass the UUID value we want to check as a parameter to this function. If the passed UUID value is valid, this function will return 1. If not, then it will return 0. If we pass a NULL argument, the IS_UUID function will also return NULL.

Following are the acceptable UUID formats.

11111111-2222-3333-4444-555555555555
11111111222233334444555555555555
{11111111-2222-3333-4444-555555555555}

MySQL UUID Examples

Let us look at some examples to understand the concepts a little better.

Example 1

Let us look at a basic example calling the MySQL UUID function that will generate a unique 128-bit long value.

OUTPUT:

UUID()
939cb2d-8b63-13ec-9c44-8c19450c827b

Example 2

Let us check out the IS_UUID function and check whether 'aaa7c39b-8c17-11ec-9c44-8c16550c827b' is a valid UUID or not.

OUTPUT:

IsValid
1

Example 3

We will check whether the string ‘{aaa7c39b-8c17-11ec-9c44-8c16550c827b}’ is a valid UUID value or not.

OUTPUT:

IsValid
1

Example 4

We will check whether the string ‘aaa7c39b-8c17-11ec-9c44-8c16455’ is a valid UUID value or not.

OUTPUT:

IsValid
0

You can see that the UUID value is not correct and hence the output of the IS_UUID method is 0.

MySQL UUID vs UUID(short)

MySQL UUID() and UUID(short) are two different functions of MySQL and are distinct. You can observe the main differences between the two here.

	UUID()	UUID(short)
1	UUID is short for Universally Unique Identifier and is a 128-bit long value.	UUID(short) is short for Short Universally Unique Identifier and is a 64-bit unsigned integer.
2	UUID is called by UUID().	UUID(short) is called by UUID_SHORT().
3	The returned value of the UUID consists of the bitwise conglomeration of the server ID, the current timestamp, a few bits, and utility bits.	The returned value of the UUID(short) consists of the bitwise conglomeration of the server ID, a fairly static time component, and a sequentially increasing 24-bit integer.
4	The server ID is 6 bytes long in UUID.	The server ID is 1 byte long in UUID(short).

Conclusion

MySQL UUID or Universally Unique Identifier is a 128-bit value that is unique across the globe and is generated using an algorithm.
The UUID value is a hexadecimal string of 32 characters, separated by hyphens in a specific format 8-4-4-4-12.
The first group of 8 hexadecimal characters represents the time stamp, the next three groups of 4 hexadecimal characters represent the version number and the variant, and the final group of 12 hexadecimal characters represents the randomly generated value.
MySQL UUID provides global uniqueness, provides security as it doesn't share any data, can be generated offline and makes it easier to perform certain tasks
Some disadvantages of MySQL UUID are that it takes up more space in the memory than a normal integer, can make your code difficult to read and debug, and can cause some unwanted performance issues.
UUID_TO_BIN, BIN_TO_UUID, and IS_UUID are three functions that can be used to eliminate the disadvantages of using MySQL UUID over AUTO_INCREMENT.
UUID() and UUID(short) are two distinct methods in MySQL and have distinct properties.

MCQs

Which of the following is true regarding MySQL UUID?

A: MySQL UUID is a 64-bit unique identifier.

B: MySQL UUID is generated using a random number generator.

C: MySQL UUID can be stored in a CHAR(36) column.

D: MySQL UUID is deprecated in MySQL 8.0.

Correct Answer: B

What is the maximum length of a MySQL UUID?

A: 16 bytes

B: 32 bytes

C: 36 bytes

D: 64 bytes

Correct Answer: C

Which of the following functions can be used to generate a UUID in MySQL?

A: UUID_SHORT()

B: UUID()

C: RANDOM_UUID()

D: UNIQUE_ID()

Correct Answer: B