MySQL UUID

UUID vs AUTO_INCREMENT

Since both UUID and AUTO_INCREMENT can generate unique values, we can consider UUID as an alternative to AUTO_INCREMENT.  

Let’s discuss the advantages and disadvantages of UUID over AUTO_INCREMENT.

Advantages

Below are the advantages of using UUID over AUTO_INCREMENT.

• UUID values in MySQL are always unique between tables, databases, and servers. It enables us to merge rows from distributed databases across servers.
• UUID values are safe. They do not provide information about our data. As a result, our data is safe from hackers. Moreover, we can use UUID in URLs.
• UUID values can be generated offline, so we do not need to involve the database server every time while generating a UUID.
• It also makes the logic in the application easier. For instance, if we want to insert data into both the parent and child tables, we must first insert data into the parent table, get the generated id, and then fill the record into the child table. We can generate the PRIMARY KEY value of the parent table using UUID and insert rows into both tables simultaneously.

Now, let’s see the disadvantages of using UUID over AUTO_INCREMENT.

Disadvantages

Below are the advantages of using UUID over AUTO_INCREMENT.

• UUID takes more space in memory than integers. UUID takes 8-bytes of memory, whereas integers take 4-bytes.
• UUID makes readability and debugging difficult. For example, it is easier to read and debug the primary key as ‘4’ rather than ‘979257b0-8b69-11ec-9c44-8c16450c827b’.
• Because of the unordered values and their size, it can also cause performance issues.

As we have discussed the disadvantages of using UUID over AUTO_INCREMENT, what if I say, we can overcome the above-discussed disadvantages using some functions. Yes, you read it right. 

Overcoming UUID Disadvantages

We can overcome the disadvantages of UUID using three functions. These are-

1. UUID_TO_BIN
2. BIN_TO_UUID
3. IS_UUID

The above functions allow us to store UUID values in BINARY (0,1) format, and whenever we want to see these values, we can see them in human-readable (VARCHAR) format.

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

Let’s dive into these functions one by one.

UUID_TO_BIN

In MySQL, the UUID_TO_BIN function converts the UUID values into Binary format. This function is used to store UUID values in databases.

Let’s understand this function using an example.

First, we will create a table Employee having columns emp_id and name. We will declare the emp_id column with data type BINARY and size 16 (because UUID contains 16 digits) and name with data type VARCHAR.

Below is the query for creating the Employee table.

mysql> CREATE TABLE Employee (
        emp_id BINARY(16) PRIMARY KEY,
        name VARCHAR(50)
        );

Now, we will insert some records into this table. But remember, the emp_id column can include only binary values. So, first, we will generate a UUID value, and then we will use the UUID_TO_BIN function to convert the value to binary.

Below is the query for inserting records in the Employee table.

mysql> INSERT INTO Employee VALUES
        (UUID_TO_BIN(UUID()), 'Arjun'),
        (UUID_TO_BIN(UUID()), 'Kalpana'),
        (UUID_TO_BIN(UUID()), 'Kiran'),
        (UUID_TO_BIN(UUID()), 'Veer');

We can see our records using the SELECT statements.

mysql> SELECT * FROM Employee;

The output of the above query will be-

Now, we will see the next function BIN_TO_UUID.

BIN_TO_UUID

In MySQL, the BIN_TO_UUID function is used to convert UUID values from Binary format to human-readable format. This function is generally used for displaying UUID values.

Let’s understand this function using an example.

We will see our above-inserted records using the BIN_TO_UUID function using the following query-

mysql> SELECT BIN_TO_UUID(emp_id), name FROM Employee;

The output of the above query will be-

As you can see, the values of the emp_id column are displayed in human-readable (hexadecimal) format.

Next, we will discuss the IS_UUID function.

IS_UUID

In MySQL, the IS_UUID function is used to validate the string format of UUID. 

Below is the format to use the IS_UUID function.

IS_UUID(string_value);

We have to pass the UUID value (string_value), which we want to check as a parameter to this function.

And this function returns 1 if the string_value is valid and 0 if the string value is invalid. If the argument is NULL, then it returns NULL.

The following are valid strings format of UUID in MySQL.

aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee
aaaaaaaabbbbccccddddeeeeeeeeeeee
{aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee}

Let’s take some examples to understand this function.

Example 1: We will check whether the string ‘aaa6c37b-8c17-11ec-9c44-8c16450c827b’ is a valid UUID value or not.

mysql> SELECT IS_UUID('aaa6c37b-8c17-11ec-9c44-8c16450c827b') AS Valid_or_Not;

The output of the above query will be-

Example 2: We will check whether the string ‘31d6f4398c1811ec9c448c16450c827b’ is a valid UUID value or not.

mysql> SELECT IS_UUID('31d6f4398c1811ec9c448c16450c827b') AS Valid_or_Not;

The output of the above query will be-

Example 3: We will check whether the string ‘{722f5a8e-8c18-11ec-9c44-8c16450c827b}’ is a valid UUID value or not.

mysql> SELECT IS_UUID('{722f5a8e-8c18-11ec-9c44-8c16450c827b}') AS Valid_or_Not;

The output of the above query will be-

Example 4: We will check whether the string ‘7acd798c-daba-6731-4123-8b8c7751’ is a valid UUID value or not.

mysql> SELECT IS_UUID('7acd798c-daba-6731-4123-8b8c7751') AS Valid_or_Not;

The output of the above query will be-

As the given string does not match the format of valid UUID, it is invaild.

Next, we will discuss the difference between UUID() and UUID(short).

UUID() vs UUID(short)

In MySQL, the functions UUID() and UUID(short) are distinct. The following table discusses the key differences between them.

S.No	UUID()	UUID(short)
1.	UUID stands for Universal Unique Identifier and is a 128-bit long value.	UUID(short) stands for Short Universal Unique Identifier and is a 64-bit unsigned integer.
2.	UUID is called as UUID().	UUID(short) is called as UUID_SHORT().
3.	The value returned by UUID consists of the bitwise conglomeration of the server ID, the current timestamp, a few bits and utility bits.	The value returned by 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).

Refer to know about :  Update Query in MySQL

FAQs

1. What is the structure of UUID in MySQL?
   UUID in MySQL is represented in ‘aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee’ format where ‘aaaaaaaa-bbbb-cccc’ represents the timestamp format, ‘dddd’  is in charge of preserving temporal uniqueness and ‘eeeeeeeeeeee’  is the IEEE 802 node number, which represents spatial uniqueness.
    
2. How many UUID values can we generate at a time?
   We can generate multiple UUID values at a time. There is no restriction in generating UUID values. We simply need to call the UUID() function as many times as we want.
    
3. Why do we prefer UUID over AUTO_INCREMENT?
   We prefer UUID over AUTO_INCREMENT since UUID values are always unique between tables, databases, and servers. Whenever we use UUID as the primary key, our data becomes safe from hackers. Moreover, we do not need to connect to the database always for generating UUID values.
    
4. What is the difference between UUID_TO_BIN and BIN_TO_UUID functions?
   The UUID_TO_BIN function is used to convert the UUID values into Binary format, whereas the BIN_TO_UUID function is used to convert UUID values from Binary format to human-readable format.
    
5. What is the use of the IS_UUID function?
   In MySQL, the IS_UUID function is used to validate the string format of UUID. We have to pass the UUID value (string_value), which we want to check as a parameter to this function, and this function returns 1 if the string_value is valid and 0 if the string value is invalid. If the argument is NULL, then it returns NULL.

Key Takeaways

In this blog, we talked about UUID in MYSQL. We started with the structure of UUID and how to generate UUID values in MYSQL. Then our discussion went to the difference between UUID and AUTO_INCREMENT, followed by UUID_TO_BIN, BIN_TO_UUID and IS_UUID functions. Finally, we discussed the difference between UUID and UUID(short). 

Attention Ninja!!!!! Don’t stop here. Start your DBMS journey with the DBMS course on Coding Ninjas. To master SQL, solve our Top 100 SQL Problems asked in various company interviews.

Happy Learning!