82

MySQL UUID

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 first three numbers are generated as a part of timestamp format, such as low, middle, and high. Here, the high part contains the UUID version number.
• The fourth number is responsible for preserving the temporal uniqueness whenever the timestamp value loses monotonicity.
• The fifth number represents the IEEE 802 node number, which indicates spatial uniqueness. If the letter is not available, it will substitute the random number that does not guarantee the spatial uniqueness.

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:

Advantages

The following are the advantages of using UUID for a primary key:

• UUID values in MySQL are unique across tables, databases, and servers. It allows us to merge rows from distributed/different databases across servers.
• UUID values do not provide information about our data, which means it is hard to guess. Thus it is safe to use in URLs.
• UUID values can be generated offline means we can generate it anywhere without exchanging information with the database server.
• It also simplifies replication (logic in the application). For example, if we want to insert data into parent and child tables, we must first insert data into the parent table, get generated id, and then fill record into the child table. Using UUID, we can generate the PRIMARY KEY value of the parent table and insert rows into both tables simultaneously.

Disadvantages

Besides the advantages, the following are the disadvantages of using UUID for a primary key:

• If we store the UUID (16-bytes) values in the databases, it occupies more space/storage than integers (4-bytes) or big integers (8-bytes).
• It makes the debugging more difficult. For example, we can imagine the expression WHERE id = ‘185e6dfd-1cc8-11eb-9a2c-107d1a24f935’ instead of WHERE id = 5.
• It can also cause performance issues because of the unordered values and their size.

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:

• UUID_TO_BIN
• BIN_TO_UUID
• IS_UUID

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:

UUID	UUID(short)
A UUID is a Universal Unique Identifier specified by RFC 4122 and 128-bit long value represented as UTF8 string of five hexadecimal numbers.	This function produces a Short Universal Unique Identifier as a 64-bit unsigned integer, which differs from the string-format 128-bit identifiers produced by the UUID() function.
It produces a value that conforms to a 16 byte version1 UUID. The version 1 UUID is the server ID’s bitwise conglomeration, the current timestamp, a few bytes, and utility bits.	It’s returned value contains a server ID’s bitwise conglomeration, a fairly static time component, and a sequentially increasing 24-bit integer.
In the UUID, the server ID is 6 bytes long that makes the space unique.	In the UUID (short), the server ID is only one byte; that’s why it loses the space uniqueness.

---