A UUID is a Universal Unique Identifier specified by RFC 4122 (It is a Universally Unique Identifier URN Namespace) and 128-bit long value. It is designed in such a way that it generates a number which is unique globally according to space and time. If we call two UUID successively, we will get two different values, and even they were performed on two separate devices that are not connected.
NOTE: Although UUID() always generates unique values, they are not guessable or predictable. It means it always returns a random number.
Structure of UUID
UUID in MySQL returns a value which is 128-bit long. It is represented in a human-readable format as a UTF8 string of five hexadecimal numbers in the below format:
The following are the valid string format of the UUID values in MySQL which is an arrangement of 32 digit of the hexadecimal format along with four hyphens (-):
We can generate UUID values in MySQL using the function as follows:
The above function returns a UUID value in agreement with UUID version 1 described in RFC 4122. After successful execution of the above statement, it will generate the UUID value as follows:
MySQL UUID vs. AUTO_INCREMENT PRIMARY KEY
UUIDs in MySQL are a good alternative to AUTO_INCREMENT PRIMARY KEY. The following are advantages of UUID over AUTO_INCREMENT PRIMARY KEY:
The following are the advantages of using UUID for a primary key:
Besides the advantages, the following are the disadvantages of using UUID for a primary key:
MySQL UUID solution
We can overcome these issues in MySQL by using the functions given below. These functions allow us to store UUID values in a compact format (BINARY) and display them in a human-readable format (VARCHAR). The name of the functions are:
NOTE: It is to note that these functions are only available in the MySQL version 8.0 or later.
The UUID_TO_BIN() function is used to convert the UUID values from a human-readable format into a compact format for storing it in the databases.
The BIN_TO_UUID() function is used to convert the UUID from the compact format to a human-readable format for displaying.
The IS_UUID() function is used to validate the string format of UUID. It returns 1 when the argument is valid and returns 0 for an invalid argument. If the argument is NULL, it will return NULL.
MySQL UUID Example
Let us understand how to use the UUID with the help of an example. First, we will create a new table named employee using the below statement:
Next, we need to insert the values into the table. Also, if we want to add the UUID values into the emp_id column, we must use the UUID() and UUID_TO_BIN() functions as follows:
Now, execute the SELECT statement to verify the inserted record.
Finally, we will query data from a UUID column using the BIN_TO_UUID() function that converts binary format to a human-readable format. See the below statement:
We will get the output as follows:
MySQL UUID vs UUID(short)
UUID() and UUID(short) both are different functions in MySQL. The basic differences between them are discussed in the below comparison chart: