MySQL FROM_BASE64() String Function

MySQL FROM_BASE64() String Function

The MySQL FROM_BASE64() string function decodes a base-64 encoded string and returns the result as a binary string. This function is essential for decoding data that has been encoded in base-64 format in SQL queries.

Syntax

SELECT FROM_BASE64(base64_string) AS result
FROM table_name;

The FROM_BASE64() function has the following components:

  • base64_string: The base-64 encoded string to be decoded.
  • result: An alias for the resulting decoded binary string.
  • table_name: The name of the table from which to retrieve the data.

Example MySQL FROM_BASE64() String Function

Let's look at some examples of the MySQL FROM_BASE64() string function:

Step 1: Using the Database

USE mydatabase;

This query sets the context to the database named mydatabase.

MySQL USE DATABASE

Step 2: Creating a Table

Create a table to work with:

CREATE TABLE encoded_strings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    base64_string TEXT NOT NULL
);

This query creates a table named encoded_strings with columns for id and base64_string.

MySQL CREATE TABLE

Step 3: Inserting Initial Rows

Insert some initial rows into the table:

INSERT INTO encoded_strings (base64_string)
VALUES ('SGVsbG8gd29ybGQ='),
       ('TXlTUUw='),
       ('V29ybGQ='),
       ('U3RyaW5nIEVuY29kaW5n'),
       ('RnJvbSBCYXNlNjQ=');

This query inserts five rows into the encoded_strings table.

MySQL INSERT INTO TABLE

Step 4: Using FROM_BASE64() with WHERE Clause

Use the FROM_BASE64() function to decode a base-64 encoded string:

SELECT base64_string, FROM_BASE64(base64_string) AS decoded_string
FROM encoded_strings;

This query retrieves the base64_string column from the encoded_strings table and returns the decoded binary string.

MySQL FROM_BASE64() WITH WHERE CLAUSE

Step 5: Using FROM_BASE64() with Multiple Columns

Use the FROM_BASE64() function with multiple columns:

SELECT id, base64_string, FROM_BASE64(base64_string) AS decoded_string
FROM encoded_strings;

This query retrieves the id and base64_string columns from the encoded_strings table and returns the decoded binary string.

MySQL FROM_BASE64() WITH MULTIPLE COLUMNS

Step 6: Using FROM_BASE64() with Constants

Use the FROM_BASE64() function with constants:

SELECT FROM_BASE64('U29tZSBFbmNvZGVkIFN0cmluZw==') AS decoded_constant;

This query decodes the constant base-64 encoded string 'U29tZSBFbmNvZGVkIFN0cmluZw==' and returns the decoded string.

MySQL FROM_BASE64() WITH CONSTANTS

Conclusion

The MySQL FROM_BASE64() function is a powerful tool for decoding base-64 encoded strings in SQL queries. Understanding how to use the FROM_BASE64() function is essential for effective data querying and analysis in MySQL.