How to format uuid string from binary column in MySQL/MariaDB

In MySQL or MariaDB, UUIDs are typically stored as binary columns to save space and improve efficiency. Typically, UUIDs are stored as a 16-byte binary column (BINARY(16) or VARBINARY(16)) rather than as a 36-character string (including 4 hyphens). This approach conserves space and optimizes index performance. However, when displaying or processing these UUIDs, it is often preferable to format them into the standard 36-character string format.

Formatting Binary UUIDs

To convert binary-formatted UUIDs to readable string format, utilize SQL built-in functions based on your database version and configuration. The following are common methods in MySQL or MariaDB:

1. Using BIN_TO_UUID()

MySQL 8.0+ and MariaDB 10.4+ provide the BIN_TO_UUID() function, which directly converts binary-formatted UUIDs to string format.

Example:

sql
SELECT BIN_TO_UUID(binary_uuid_column) AS formatted_uuid FROM your_table;

This converts the binary UUID in binary_uuid_column to the standard UUID string format.

2. Using HEX() and string functions

For older database versions or more complex formatting requirements, use the HEX() function to convert binary data to a hexadecimal string, then format it using string functions.

Example:

sql
SELECT CONCAT(
    SUBSTR(HEX(binary_uuid_column), 1, 8), '-
    SUBSTR(HEX(binary_uuid_column), 9, 4), '-
    SUBSTR(HEX(binary_uuid_column), 13, 4), '-
    SUBSTR(HEX(binary_uuid_column), 17, 4), '-
    SUBSTR(HEX(binary_uuid_column), 21)
) AS formatted_uuid
FROM your_table;

This method first converts the binary data into a long hexadecimal string, then uses SUBSTR() and CONCAT() functions to split and insert hyphens, constructing the standard UUID format.

Notes

• Ensure you select the appropriate method (such as UUID_TO_BIN()) to correctly convert and store UUID data before insertion into the database.
• Considering performance implications, if you frequently format UUIDs at the application level, it is often more efficient to handle them in application code rather than in database queries.

By employing these methods, you can choose the most suitable approach to format UUIDs stored in binary columns based on your specific database version and requirements.