How does mysql generate uuid?

According to space and time, Universal Unique Identifier, known as UUID, is a 128-bit long value that is unique across the globe. The UUID is specified by the RFC 4122.

UUID values are incredibly fascinating because even if the values are generated from the same device, they can never be the same. However, I will not get into details about the technologies used to implement UUIDs.

In this tutorial, we shall focus on the advantages of using UUIDs instead of INT for primary keys, the disadvantages of UUIDs in a database, and how to implement UUIDs in MySQL.

Let us get started:

To generate a UUID in MySQL, we use the UUID() function. This function returns a utf8 string with a 5-hexadecimal group in the form of:

aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee

The first three segments are generated as part of timestamp format in the low, middle, and high format.

The fourth segment of the UUID value is reserved for ensuring temporary uniqueness, where the timestamp value drops monotonicity.

The final segment represents the IEEE 802 node value, signifying uniqueness across space.

When to Use UUID in MySQL

I know what you are thinking:

If UUIDs are unique globally, why don’t we use them as the default primary keys in database tables? The answer is both simple and not simple.

To begin, UUIDs are not native data types such as an INT, which you can set as primary key and auto-increment as more data gets added to the database.

Second, UUIDs have their drawbacks which might not be applicable in all cases.

Allow me to share a few cases where using UUIDs as primary keys might be applicable.

1. One common scenario is where complete uniqueness is required. Since UUIDs are unique globally, they offer a perfect option for merging rows in databases while preserving the uniqueness.
2. Security – UUIDs do not expose any information related to your data and are therefore useful when security is a factor. Second, they are generated offline without revealing any information about the system.

The following are some of the drawbacks of implementing UUIDs in your database.

1. UUIDs are 6-bytes compared to integers which are 4-bytes. That means they will occupy more storage for the same amount of data compared to integers.
2. If UUIDs are indexed, they might cause significant performance costs and slow down the database.
3. Since UUIDs are random and unique, they can make the debugging process unnecessarily cumbersome.

UUID Functions

In MySQL 8.0 and later, you can use various functions to counter some of the drawbacks presented by UUIDs.

These functions are:

1. UUID_TO_BIN – Converts UUID from VARCHAR to Binary which is more efficient for storing in databases
2. BIN_TO_UUID – From Binary to VARCHAR
3. IS_UUID – Returns Boolean true if arg is valid VARCHAR UUID. The reverse is true.

Basic MySQL UUID Types Usage

As mentioned earlier, to implement UUIDs in MySQL, we use the UUID() function. For example, to generate UUID, we do:

mysql> SELECT UUID();
+--------------------------------------+
| UUID()                               |
+--------------------------------------+
| f9eb97f2-a94b-11eb-ad80-089798bcc301 |
+--------------------------------------+
1 row in set (0.01 sec)

Table with UUID

Let us create a table with UUID values and see how we can implement such functionality. Consider the query below:

DROP SCHEMA IF EXISTS uuids;
CREATE SCHEMA uuids;
USE uuids;
CREATE TABLE validation
(
    id BINARY(16) PRIMARY KEY
);
INSERT INTO validation(id)
VALUES (UUID_TO_BIN(UUID())),
       (UUID_TO_BIN(UUID())),
       (UUID_TO_BIN(UUID())),
       (UUID_TO_BIN(UUID())),
       (UUID_TO_BIN(UUID()));

Once all the UUIDs are generated, we can select them and convert them from Binary to string UUID values as shown in the query below:

SELECT BIN_TO_UUID(id) id FROM validation;

Here is the output:

Conclusion

There is not much to cover about UUIDs in MySQL, but if you would like to learn more about them, consider checking out the MySQL source:

https://dev.mysql.com/doc/