How the TO_BASE64() Function Works in MySQL

In MySQL, the TO_BASE64() function converts a string to a base-64 encoded string and returns the result.

Syntax

The syntax goes like this:

TO_BASE64(str)

Where str is the string that you want encoded.

Example 1 – Basic Usage

Here’s an example to demonstrate the basic usage:

SELECT TO_BASE64('Dog');

Result:

+------------------+
| TO_BASE64('Dog') |
+------------------+
| RG9n             |
+------------------+

So in this example, our argument is Dog, which becomes RG9n once converted to base-64.

We can use the FROM_BASE64() function to decode the base-64 string:

SELECT FROM_BASE64('RG9n');

Result:

+---------------------+
| FROM_BASE64('RG9n') |
+---------------------+
| Dog                 |
+---------------------+

Example 2 – A Longer String

Here’s an example using a longer string:

SELECT TO_BASE64('My cat chases dogs!');

Result:

+----------------------------------+
| TO_BASE64('My cat chases dogs!') |
+----------------------------------+
| TXkgY2F0IGNoYXNlcyBkb2dzIQ==     |
+----------------------------------+

Example 3 – Non-String Arguments

If the argument is not a string, it will be converted to a string first:

SELECT TO_BASE64(123);

Result:

+----------------+
| TO_BASE64(123) |
+----------------+
| MTIz           |
+----------------+

Example 4 – NULL Argument

You’ll get NULL if you pass in NULL:

SELECT TO_BASE64(NULL);

Result:

+-----------------+
| TO_BASE64(NULL) |
+-----------------+
| NULL            |
+-----------------+

Example 5 – Missing Argument

You’ll get an error if you don’t pass in an argument:

SELECT TO_BASE64();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'TO_BASE64'

Example 6 – Too Many Arguments

You’ll also get an error if you pass in too many arguments:

SELECT TO_BASE64('Cat', 'Dog');

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'TO_BASE64'

MySQL TO_BASE64(str) function

TO_BASE64 function in mysql is used for encode any string with the string encode rule BASE64. This function returns a encoded string. This function is added with the mysql version 5.6.1

In mysql there is different base-64 encoding schemes exist. These are the encoding and decoding rules used by TO_BASE64() and FROM_BASE64():

-> The encoding for alphabet value 62 is '+'.

-> The encoding for alphabet value 63 is '/'.

-> Encoded output consists of groups of 4 printable characters. Each 3 bytes of the input data are encoded using 4 characters. If the last group is incomplete, it is padded with '=' characters to a length of 4.

-> A newline is added after each 76 characters of encoded output to divide long output into multiple lines.

-> Decoding recognizes and ignores newline, carriage return, tab, and space.

Here is a example in which I encode a sring by BASE64 encoding rule-

mysql> SELECT TO_BASE64('base64 encoded string');
-> YmFzZTY0IGVuY29kZWQgc3RyaW5n

MySQL to_base64()

Encode an input String using Base64.

Function

TO_BASE64(str)

Parameters

str - The String to be encoded

Return value

Returns the Base64-encoded version of str.

Example

#note: This function was added in MySQL 5.6.1.
mysql> SELECT TO_BASE64('base64 encoded string');
-> YmFzZTY0IGVuY29kZWQgc3RyaW5n