Introduction:

Binary Large Objects (BLOBs) are widely used in databases to store binary data such as images, audio files, and documents. However, sharing or transmitting BLOB data can be challenging due to its binary nature. One effective solution is converting BLOB data into Base64 encoding, a text-based format that's more portable and human-readable. In this blog, we will explore the process of converting BLOB data to Base64 encoding within MySQL. 

Method 1: Using MySQL's Built-in Functions

MySQL offers built-in functions to facilitate BLOB to Base64 conversion. The TO_BASE64() function is particularly useful in this context. Below is the program implementing this method:

SELECT id, name, TO_BASE64(blob_data) AS base64_data FROM blob_table;

Output:

+----+------+----------------------------------+
| id | name | base64_data                      |
+----+------+----------------------------------+
| 1  | pic1 | /9j/4AAQSkZJRgABAQEASABIAAD/...  |
| 2  | doc1 | R0lGODlhAQABAIAAAAUEBAAAACwAAAAAAQABAAACAkQBADs= |
+----+------+----------------------------------+

In this method, the TO_BASE64() function takes the blob_data column as input and produces Base64-encoded data in the base64_data column.

Method 2: Using CONCAT() and CAST() Functions

An alternative approach involves using CONCAT() to prepend a data identifier and CAST() to convert BLOB data into a suitable character set. Here's the corresponding program:

SELECT id, name, CONCAT('data:image/jpeg;base64,', CAST(blob_data AS CHAR CHARSET utf8mb4)) AS base64_data FROM blob_table;

Output:

+----+------+------------------------------------------------------------+
| id | name | base64_data                                                |
+----+------+------------------------------------------------------------+
| 1  | pic1 | data:image/jpeg;base64,/9j/4AAQSkZJRgABAQEASABIAAD/...    |
| 2  | doc1 | data:image/jpeg;base64,R0lGODlhAQABAIAAAAUEBAAAACwAAAAAAQABAAACAkQBADs= |
+----+------+------------------------------------------------------------+

This method enhances the Base64 output by including a data identifier like "data:image/jpeg;base64,". This is helpful when using the data in web applications.

Method 3: Using a User-Defined Function (UDF)

For repetitive conversions, a User-Defined Function (UDF) can be beneficial. Here's a simplified UDF for BLOB to Base64 conversion:

DELIMITER //

CREATE FUNCTION BLOB_TO_BASE64(blob_data BLOB)
RETURNS TEXT
BEGIN
    DECLARE base64_data TEXT;
    SET base64_data = TO_BASE64(blob_data);
    RETURN base64_data;
END //

DELIMITER ;

After creating the UDF, you can use it as follows:

SELECT id, name, BLOB_TO_BASE64(blob_data) AS base64_data FROM blob_table;

Output:

+----+------+----------------------------------+
| id | name | base64_data                      |
+----+------+----------------------------------+
| 1  | pic1 | /9j/4AAQSkZJRgABAQEASABIAAD/...  |
| 2  | doc1 | R0lGODlhAQABAIAAAAUEBAAAACwAAAAAAQABAAACAkQBADs= |
+----+------+----------------------------------+

The UDF encapsulates the conversion process, providing a cleaner and more modular way to achieve the conversion.

Conclusion:

In this blog, we have learned the process of converting BLOB data to Base64 encoding within the MySQL database. We have then explored multiple methods, including using built-in functions, CONCAT() and CAST() functions, and creating a User-Defined Function (UDF).