Mysql 8 UUID support

In MySQL 8 there are three new functions that comfort and improve the provision for working with UUIDs.

Background

A UUID is just a 128-bit value and it is typically denoted in human-readable presentation as an UTF8 string self-possessed of 5 groups of hexadecimal characters parted by dashes. Ex:

UUIDs can have changed original structure reliant on the version. The RFC4122 requires 5 versions. The one that MySQL apparatuses in the UUID() function is version 1 which is collected of the timestamp, UUID version and MAC address.

Advantages:

UUIDs are a good substitute to AUTO_INCREMENT PRIMARY KEY and are used mostly because:

1. the keys are exceptional across tables, databases and servers

2. are tougher to guess

3. can be produced offline

4. make simpler replication

Disadvantages:

But they also come with some drawbacks:

1. bigger storage: 36 characters

2. more tough to debug

3. routine issues: mostly because of the size and not being well-ordered

Solution:

With these difficulties in mind, MySQL added three new functions: UUID_TO_BIN, BIN_TO_UUID, IS_UUID. These will comfort the work with UUIDs and will deliver a solution around the problems declared above.

Let’s jump with the duo: UUID_TO_BIN/BIN_TO_UUID. This function will be used to change from the human-readable presentation (char/varchar) to the compressed format (binary) and back. That means squeezing the 32 characters (36 or more with extractors) to the 16-bit presentation or back to the human-readable presentation.

This is how you would generally use them:

You can perceive in the results directly above that the values most probable to be different on successively generated UUIDs are the ones at the foundation of the string, that is because the lesser time units are the hexadecimals characters at the start of the string, while the greater time units come next, finish with the MAC address. This will have a noteworthy performance effect, since the values will be inserted in random places in the index tree which will need a lot of IO when the index tree will not fit in memory any longer.

The UUID_TO_BIN/BIN_TO_UUID functions have a additional boolean argument, which is non-compulsory, and can be used to evade this problematic. Setting the argument to true while injecting the values: “INSERT INTO t VALUES(UUID_TO_BIN(UUID(), true));” will reorganize the time-related bits so that following created values will be ordered.

For more info about the problem and resolution for earlier versions of MySQL.

The other function IS_UUID does a very basic authentication of the UUID providing as parameter and returns TRUE if the argument is a valid UUID and FALSE then. A UUID is well-thought-out valid if it contains 32 hexadecimal characters with non-compulsory separators:

“{“,”-“,”}” . The function does not crisscross the version bit or the timestamp.

All of these will be measured valid:

Conclusion

Enjoy the new functions. Thank you for using MySQL!