MySQL UUID_TO_BIN and BIN_TO_UUID Functions

In this tutorial, we will study two essential MySQL functions, UUID_TO_BIN() and BIN_TO_UUID(). These functions are related to the UUID() function and are only available in MySQL version above 8.0, so upgrade your MySQL if you do not have the latest version.

Also read: MySQL UUID_SHORT() Function – A Complete Guide

UUID_TO_BIN() and BIN_TO_UUID() Functions

The MySQL function UUID_TO_BIN() is used to convert the valid string UUID of human-readable format to the binary UUID in a compact format.

In contrast, BIN_TO_UUID() converts the binary UUID to the string UUID. The data type of the result generated by the UUID_TO_BIN() function is VARBINARY(16).

Now, you might be thinking about why we need such conversion if we can directly store the UUID in the table? The answer is straightforward. With lots of advantages of UUID, it also comes with disadvantages like space.

A valid UUID consists of 36 characters (32, excluding dashes) which take a lot of storage space in your database. MySQL provides us with these two functions to use in our program to overcome this concern.

Let’s see how we can use these functions with syntax and some easy-to-understand examples!

Syntax of MySQL UUID_TO_BIN and BIN_TO_UUID Functions

The syntax of these functions is simple and identical. Let’s see the syntax of UUID_TO_BIN() first.

UUID_TO_BIN(string_uuid);
UUID_TO_BIN(string_uuid, swap_flag);Code language: SQL (Structured Query Language) (sql)

The UUID_TO_BIN() accepts the second optional parameter as well.

• The first parameter is a valid UUID. It can be of any out of 3 valid formats(link to is_uuid()) of a UUID value.

• The second parameter – swap_flag, is either 0 or 1.
  • When set to 0, the result will be same as the one argument form of function.
  • When set to 1, result is different. The first(time-low part) and third (time-high part)group of the hex codes in the UUID string are swapped with each other. If the result produced by UUID() function is used as a indexed column in your database table, it improves the indexing effeciency. If UUID is produced manually or any other method, no benefit is provided.

Now we will see the syntax of the BIN_TO_UUID() function.

BIN_TO_UUID(binary_uuid);
BIN_TO_UUID(binary_uuid,swap_flag);Code language: SQL (Structured Query Language) (sql)

swap_flag is the same as the above, which will bring back the time-low and time-high parts to the original position in result value by swapping them.

Note- If you have used swap_flag for UUID_TO_BIN() function, you must use it for BIN_TO_UUID() to interchange the time-part values to bring back the original hex string UUID. You won’t get the correct results if you do not use the swap_flag bidirectionally.

Now, let’s see some examples of these functions.

Examples of UUID_TO_BIN() Function

Converting UUID values from string to binary is easy in the MySQL version greater than 8.0. Before introducing the UUID_TO_BIN() function, you were supposed to deal with the conversion in another way.

To convert the string UUID to compact format, programmers were using UNHEX() function with the replace() function to get rid of dashes. Have a look below –

The string UUID must be inside of a single quote (‘) or a double quote (“).

SELECT UNHEX(REPLACE("70c2be67-6a41-11ec-8a00-d8d0905b3cc8","-","")) AS unhex;Code language: SQL (Structured Query Language) (sql)

But now, you can simply use the UUID_TO_BIN() function, which will take care of replacing the dashes in the hex UUID value.

SELECT UUID_TO_BIN("70c2be67-6a41-11ec-8a00-d8d0905b3cc8") AS uuidTObin;Code language: SQL (Structured Query Language) (sql)

Let’s check the result with the swap_flag now –

SELECT UUID_TO_BIN("70c2be67-6a41-11ec-8a00-d8d0905b3cc8",1) AS uuidTObin;Code language: SQL (Structured Query Language) (sql)

As you can see, the group of hex values is swapped.

When converting back the UUID of compact form to string UUID, the same example will be considered below with and without swap_flag to understand the result.

Examples of BIN_TO_UUID() Function

Without BIN_TO_UUID() function, you can convert the binary UUID to string UUID with the HEX() function. Although it does not consist of the dashes, still it’s a valid UUID format.

A binary UUID must be without quotation. Else it wll be considerd as a string value.

SELECT HEX(0x70C2BE676A4111EC8A00D8D0905B3CC8) AS hex;Code language: SQL (Structured Query Language) (sql)

Let’s see whether the obtained output is a valid UUID or not using the IS_UUID() function. If the given UUID is valid, we should get the result as 1, else 0.

SELECT IS_UUID("70C2BE676A4111EC8A00D8D0905B3CC8") AS is_uuid;Code language: SQL (Structured Query Language) (sql)

As you can see, we have received 1 in the output, which means the given UUID is valid.

Now, using the BIN_TO_UUID() function, the result will be the same, but it will consist of lower case letters and dashes. Check the result below-

SELECT BIN_TO_UUID(0x70C2BE676A4111EC8A00D8D0905B3CC8) AS binTOuuid;Code language: SQL (Structured Query Language) (sql)

As stated above, we will convert the binary UUID produced with the swap_flag but without swap_flag in the BIN_TO_UUID() function to check if the generated result is the same as the previous one.

SELECT BIN_TO_UUID(0x11EC6A4170C2BE678A00D8D0905B3CC8) AS binTOuuid;Code language: SQL (Structured Query Language) (sql)

As you can see, the generated result is not the same as the previous one because we haven’t provided the swap_flag.

Now we will provide the second argument- swap_flag and check the result.

SELECT BIN_TO_UUID(0x11EC6A4170C2BE678A00D8D0905B3CC8,1) AS binTOuuid;Code language: SQL (Structured Query Language) (sql)

Yes, now we have obtained the expected output.

Conclusion

The MySQL functions UUID_TO_BIN() and BIN_TO_UUID() are very useful and important. You can convert the values with just a single function which is far easier than using a combination of multiple functions in older versions of MySQL. If your hosting provider provides MySQL version >8.0, this is a straightforward solution. Else, you can use the other method given above.

References

MySQL official documentation for UUID_TO_BIN() and BIN_TO_UUID() functions.

Stackoverflow thread on UUID conversions.