MySQL - TO_BASE64() Function

---

---

The MySQL TO_BASE64() function is used to encode a string to a base-64 encoded string. Base-64 encoding is a way of representing binary data in ASCII text format by converting 3 bytes of binary data into 4 bytes of text data.

Below are the rules followed by MySQL TO_BASE64() function −

• Encoding for alphabet value 62 is '+'.
• The encoding for alphabet value 63 is '/'.
• The encoded output will be a group of 4 characters. If the last group is incomplete, it will be padded up with the "=" character.
• It adds a new line after every 76 characters of encoded output to split the long output value into multiple lines.
• Decoding recognizes and ignores tabs, carriage returns, newlines, and spaces.

Syntax

Following is the syntax of MySQL TO_BASE64() function −

TO_BASE64(str)

Parameters

This function takes a string value as a parameter.

Return Value

This function returns the base64-encoded representation of the given string.

Example

In the following example, the string 'Tutorialspoint' is encoded into its base64 representation −

SELECT TO_BASE64('Tutorialspoint');

Following is the output of the above code −

TO_BASE64('Tutorialspoint')
VHV0b3JpYWxzcG9pbnQ=

Example

Here is another example, where we encode a shorter Base64 string −

SELECT TO_BASE64('test');

The output obtained is as follows −

TO_BASE64('test')
dGVzdA==

Example

You can also use the TO_BASE64() and FROM_BASE64() functions together to first encode and then decode a string back to its original form −

SELECT TO_BASE64('Hello how are you'), FROM_BASE64(TO_BASE64('Hello how are you'));

We get the output as follows −

TO_BASE64('Hello how are you')	FROM_BASE64(TO_BASE64('Hello how are you'))
SGVsbG8gaG93IGFyZSB5b3U=	0x48656C6C6F20686F772061726520796F75

If you disable the --binary-as-hex value, the result of the above query will be as follows &mnus;

SELECT TO_BASE64('Hello how are you'),
FROM_BASE64(TO_BASE64('Hello how are you'));

Following is the output of the above code −

TO_BASE64('Hello how are you')	FROM_BASE64(TO_BASE64('Hello how are you'))
SGVsbG8gaG93IGFyZSB5b3U=	Hello how are you

Example

You can also pass a column name of a table as a parameter to this function and encode the values in it.

Let us create a table named "DATA" and insert records into it using CREATE and INSERT statements as shown below −

CREATE TABLE DATA (
   Name VARCHAR(15), 
   UserID INT, 
   Val VARCHAR(50)
);

Now, let us insert records into it using the INSERT statement −

INSERT INTO DATA VALUES 
('Raju', 1001, 'Key1abc123'),
('Rahman', 1002, 'Key2abc'),
('Robert', 1002, 'key3');

The DATA obtained is as follows −

Name	UserID	Val
Raju	1001	Key1abc123
Rahman	1002	Key2abc
Robert	1002	key3

Following query encodes the values of the column 'Val' into a Base64 string −

SELECT Name, UserID, Val, TO_BASE64(Val) 
FROM DATA;

After executing the above code, we get the following output −

Name	UserID	Val	TO_BASE64(Val)
Raju	1001	Key1abc123	S2V5MWFiYzEyMw==
Rahman	1002	Key2abc	S2V5MWFiYzEyMw==
Robert	1002	key3	a2V5Mw==