Introduction:

UUIDs (Universally Unique Identifiers) are widely used for uniquely identifying data across distributed systems. In MySQL, UUIDs are often stored as CHAR(36) or VARCHAR(36) due to their human-readable format. However, there are scenarios where converting UUIDs to BINARY(16) can offer significant benefits in terms of storage efficiency and performance. In this blog, we will explore multiple methods to achieve this conversion, providing step-by-step explanations, code samples, and performance comparisons.

Method 1: Using MySQL Built-in Functions

To convert a UUID to BINARY(16) using MySQL built-in functions, we can utilize the UNHEX function. This function converts a string of hexadecimal digits into binary data. Here's the SQL query to achieve this:

SELECT UNHEX(REPLACE(uuid_column, '-', '')) AS binary_uuid FROM your_table;

• We use the REPLACE function to remove the hyphens from the UUID, as they are not needed in the binary representation.
• UNHEX is then applied to convert the modified hexadecimal string to binary.

Output:

+----------------------------------+
| binary_uuid                      |
+----------------------------------+
| Binary Data Here                 |
+----------------------------------+

Method 2: Using CAST and SUBSTRING

Another method involves using the CAST function in combination with SUBSTRING to extract and convert the hexadecimal components of the UUID.

SELECT CAST(CONCAT(
    SUBSTRING(uuid_column, 1, 8),
    SUBSTRING(uuid_column, 10, 4),
    SUBSTRING(uuid_column, 15, 4),
    SUBSTRING(uuid_column, 20, 4),
    SUBSTRING(uuid_column, 25)
) AS BINARY(16)) AS binary_uuid FROM your_table;

• We use the CONCAT function to combine the substrings of the UUID, excluding the hyphens.
• CAST is applied to convert the concatenated string into BINARY(16).

Output:

+----------------------------------+
| binary_uuid                      |
+----------------------------------+
| Binary Data Here                 |
+----------------------------------+

Method 3: Using UUID_TO_BIN Function (MySQL 8.0+)

MySQL 8.0 introduced the UUID_TO_BIN function, which directly converts a UUID to BINARY(16).

SELECT UUID_TO_BIN(uuid_column) AS binary_uuid FROM your_table;

• UUID_TO_BIN converts the UUID to BINARY(16) without the need for manual manipulation.

Output:

+----------------------------------+
| binary_uuid                      |
+----------------------------------+
| Binary Data Here                 |
+----------------------------------+

Conclusion:

In this blog, we have explored multiple methods to convert UUIDs to BINARY(16) in MySQL. We have discussed the benefits of this conversion, including storage efficiency and potential performance gains. While all the methods achieve the same result, Method 3 (UUID_TO_BIN) stands out as the most efficient option, particularly in terms of performance.