How the TO_BASE64() function works in Mariadb?

The TO_BASE64() function in MariaDB is used to convert a string or binary value into its Base64 encoded representation. Base64 encoding is a way of representing binary data using only printable ASCII characters, making it useful for transmitting or storing binary data in environments that can handle only text.

Syntax

The syntax of the MariaDB TO_BASE64() function is as follows:

TO_BASE64(expr)

• expr: This is the expression to be encoded. It can be a string or a binary value.

The function returns a string containing the Base64 encoded representation of the input expression.

Examples

Example 1: Encoding a simple string

This example demonstrates how to use the TO_BASE64() function to encode a simple string.

SELECT TO_BASE64('Hello, World!');

The following is the output:

+----------------------------+
| TO_BASE64('Hello, World!') |
+----------------------------+
| SGVsbG8sIFdvcmxkIQ==       |
+----------------------------+

In this example, the TO_BASE64() function encodes the string ‘Hello, World!’ into its Base64 representation ‘SGVsbG8sIFdvcmxkIQ==’.

Example 2: Encoding a binary value

This example shows how to encode a binary value using the TO_BASE64() function.

SELECT TO_BASE64(UNHEX('48656C6C6F'));

The following is the output:

+--------------------------------+
| TO_BASE64(UNHEX('48656C6C6F')) |
+--------------------------------+
| SGVsbG8=                       |
+--------------------------------+

In this example, the UNHEX() function is used to convert the hexadecimal string ‘48656C6C6F’ into its binary representation, which is then encoded into Base64 by the TO_BASE64() function.

Example 3: Encoding a value from a table

This example demonstrates how to encode a value from a table using the TO_BASE64() function.

DROP TABLE IF EXISTS data_table;
CREATE TABLE data_table (id INT, data BLOB);
INSERT INTO data_table VALUES (1, UNHEX('48656C6C6F'));

SELECT id, TO_BASE64(data) AS base64_data
FROM data_table;

The following is the output:

+------+-------------+
| id   | base64_data |
+------+-------------+
|    1 | SGVsbG8=    |
+------+-------------+

In this example, the TO_BASE64() function is used to encode the binary data stored in the data column of the data_table into its Base64 representation.

Example 4: Encoding and decoding a string

This example shows how to use the TO_BASE64() function in combination with the FROM_BASE64() function to encode and decode a string.

SELECT FROM_BASE64(TO_BASE64('Hello, World!')) AS decoded_string;

The following is the output:

+----------------+
| decoded_string |
+----------------+
| Hello, World!  |
+----------------+

In this example, the TO_BASE64() function first encodes the string ‘Hello, World!’ into its Base64 representation, and then the FROM_BASE64() function decodes the Base64 string back into the original string.

Example 5: Encoding and storing binary data

This example demonstrates how to use the TO_BASE64() function to encode and store binary data in a table.

DROP TABLE IF EXISTS binary_data;
CREATE TABLE binary_data (id INT, data VARCHAR(255));
INSERT INTO binary_data VALUES (1, TO_BASE64(UNHEX('48656C6C6F')));

SELECT id, FROM_BASE64(data) AS decoded_data
FROM binary_data;

The following is the output:

+------+--------------+
| id   | decoded_data |
+------+--------------+
|    1 | Hello        |
+------+--------------+

In this example, the TO_BASE64() function is used to encode the binary data UNHEX('48656C6C6F') into its Base64 representation, which is then stored in the data column of the binary_data table. The FROM_BASE64() function is used to decode the stored Base64 data back into its original binary form.

Related Functions

The following are some functions related to the MariaDB TO_BASE64() function:

• MariaDB FROM_BASE64() function is used to decode a Base64 encoded string into its original binary representation.
• MariaDB HEX() function is used to convert a string or binary value into its hexadecimal representation.
• MariaDB UNHEX() function is used to convert a hexadecimal string into its binary representation.

Conclusion

The TO_BASE64() function in MariaDB is a useful tool for encoding strings or binary data into a Base64 representation. It is particularly useful when working with binary data that needs to be transmitted or stored in environments that can only handle text data. By understanding the usage and capabilities of this function, along with related functions like FROM_BASE64(), developers can effectively work with and manipulate binary data in their MariaDB applications.