The Mysterious Case of BIN_TO_UUID and UUID_TO_BIN in MySQL: A Guide to Conversion

If you’re reading this article, chances are you’ve encountered the frustration of dealing with UUIDs in MySQL. You’re not alone! Many developers have scratched their heads trying to figure out why BIN_TO_UUID and UUID_TO_BIN functions don’t exist in MySQL. Today, we’ll embark on a journey to demystify this enigma and provide a comprehensive guide to converting between binary and UUID formats.

What are BIN_TO_UUID and UUID_TO_BIN functions?

In a perfect world, MySQL would have built-in functions for converting between binary and UUID formats. Unfortunately, that’s not the case. BIN_TO_UUID and UUID_TO_BIN functions are essential in many applications, especially when working with unique identifiers. These functions would allow you to convert binary data (e.g., 16-byte binary strings) to human-readable UUIDs (e.g., 36-character strings) and vice versa.

Why are they missing in MySQL?

The reason behind the absence of these functions is rooted in MySQL’s design and architecture. MySQL is a relational database management system that focuses on storing and retrieving data efficiently. While it supports various data types, including binary and string, it doesn’t natively support UUIDs.

UUIDs are an ISO standard (ISO/IEC 9834-8:2005) for generating unique identifiers. They’re widely used in various applications, including Linux, Windows, and many programming languages. However, MySQL hasn’t implemented built-in support for UUIDs, leaving developers to find workarounds.

Converting Binary to UUID in MySQL

Since BIN_TO_UUID doesn’t exist, we’ll create a custom function to achieve this conversion. We’ll use a combination of MySQL’s built-in functions to get the job done.

DELIMITER //
CREATE FUNCTION BIN_TO_UUID(b binary(16))
RETURNS char(36) DETERMINISTIC
BEGIN
    DECLARE uuid char(36);
    SET uuid = CONCAT_WS('-',
                         LEFT(HEX(b), 8),
                         MID(HEX(b), 9, 4),
                         MID(HEX(b), 13, 4),
                         RIGHT(HEX(b), 12));
    RETURN uuid;
END//
DELIMITER ;

This function takes a 16-byte binary string as input and returns a human-readable UUID. Let’s break it down:

• DELIMITER //: We’re telling MySQL to use the // delimiter instead of the default ;.
• CREATE FUNCTION BIN_TO_UUID(b binary(16)): We’re defining a new function BIN_TO_UUID that takes a 16-byte binary string as input.
• RETURNS char(36) DETERMINISTIC: Our function returns a 36-character string (the UUID format).
• BEGIN ... END: The function body starts here.
• DECLARE uuid char(36): We declare a variable uuid to store the resulting UUID.
• SET uuid = CONCAT_WS('-', ...): We use the CONCAT_WS function to concatenate the different parts of the UUID. The WS stands for “with separator.”
• LEFT(HEX(b), 8), MID(HEX(b), 9, 4), and MID(HEX(b), 13, 4): We extract the corresponding parts of the UUID using LEFT, MID, and RIGHT functions.
• RETURN uuid: We return the resulting UUID.

Converting UUID to Binary in MySQL

Now that we have a function to convert binary to UUID, let’s create a function to do the reverse – convert a UUID to binary.

DELIMITER //
CREATE FUNCTION UUID_TO_BIN(uuid char(36))
RETURNS binary(16) DETERMINISTIC
BEGIN
    DECLARE b binary(16);
    SET b = UNHEX(REPLACE(uuid, '-', ''));
    RETURN b;
END//
DELIMITER ;

This function takes a 36-character UUID as input and returns a 16-byte binary string. Here’s what’s happening:

• DELIMITER //: Same as before, we’re using the // delimiter.
• CREATE FUNCTION UUID_TO_BIN(uuid char(36)): We’re defining a new function UUID_TO_BIN that takes a 36-character UUID as input.
• RETURNS binary(16) DETERMINISTIC: Our function returns a 16-byte binary string.
• BEGIN ... END: The function body starts here.
• DECLARE b binary(16): We declare a variable b to store the resulting binary string.
• SET b = UNHEX(REPLACE(uuid, '-', '')): We use the UNHEX function to convert the UUID to binary. We first remove the hyphens from the UUID using the REPLACE function.
• RETURN b: We return the resulting binary string.

Using the Custom Functions

Now that we have our custom functions, let’s demonstrate how to use them.

-- Create a binary string
SET @bin = UNHEX('6ba7b726-9dad-11d1-80b4-00c04fb925fc');

-- Convert binary to UUID
SELECT BIN_TO_UUID(@bin) AS uuid;

-- Output: 6ba7b726-9dad-11d1-80b4-00c04fb925fc

-- Create a UUID string
SET @uuid = '6ba7b726-9dad-11d1-80b4-00c04fb925fc';

-- Convert UUID to binary
SELECT UUID_TO_BIN(@uuid) AS b;

-- Output: 6ba7b7269dad11d180b400c04fb925fc

In this example, we create a binary string and convert it to a UUID using the BIN_TO_UUID function. Then, we create a UUID string and convert it back to binary using the UUID_TO_BIN function.

Conclusion

In this article, we explored the mysterious case of BIN_TO_UUID and UUID_TO_BIN functions in MySQL. We learned why they don’t exist and created custom functions to bridge the gap. By using these functions, you can seamlessly convert between binary and UUID formats in your MySQL applications.

Remember, these custom functions are specific to MySQL and might not be compatible with other databases. Always test and adapt your code according to your specific use case.

If you have any questions or need further assistance, please don’t hesitate to ask. Happy coding!

Function	Description
BIN_TO_UUID	Converts a 16-byte binary string to a human-readable UUID.
UUID_TO_BIN	Converts a 36-character UUID to a 16-byte binary string.

1. Use the BIN_TO_UUID function to convert binary data to UUIDs.
2. Use the UUID_TO_BIN function to convert UUIDs to binary data.
3. Test your code thoroughly to ensure compatibility with your specific use case.

Frequently Asked Question

Got stuck with BIN_TO_UUID and UUID_TO_BIN functions in MySQL? Don’t worry, we’ve got you covered!