Understanding base64 encoding in MySQL?

To encode Base64 you can use two functions -

• TO_BASE64()
• FROM_BASE64()

SELECT TO_BASE64(anyValue) as AnyVariableName;

The syntax for base64 decoding is as follows-

SELECT FROM_BASE64(encodeValue) as anyVariableName

To understand the above concept, let us use the above syntax-

Case 1 - Encoding

To encode a value, use to_base64(). The query is as follows -

mysql> select TO_BASE64('Password') as Base64EncodeValue;

Output

+-------------------+
| Base64EncodeValue |
+-------------------+
| UGFzc3dvcmQ=      |
+-------------------+
1 row in set (0.00 sec)

Case 2 - Decoding

To decode the value, use from_base64(). The query is as follows -

mysql> select FROM_BASE64('UGFzc3dvcmQ=') as Base64DecodeValue;

Output

+-------------------+
| Base64DecodeValue |
+-------------------+
| Password          |
+-------------------+
1 row in set (0.00 sec)

You can understand this by creating a table. The query to create the table is as follows -

mysql> create table Base64Demo
-> (
-> ProductId varchar(100)
-> );
Query OK, 0 rows affected (0.54 sec)

Use the insert command to insert some records in the table. The query is as follows -

mysql> insert into Base64Demo values('321Product');
Query OK, 1 row affected (0.16 sec)

mysql> insert into Base64Demo values('Product456');
Query OK, 1 row affected (0.14 sec)

mysql> insert into Base64Demo values('654Product');
Query OK, 1 row affected (0.15 sec)

Use the select statement to display all records in the table. The query is as follows -

mysql> select *from Base64Demo;

Output

+------------+
| ProductId  |
+------------+
| 321Product |
| Product456 |
| 654Product |
+------------+
3 rows in set (0.00 sec)

Here is the query encoded using Base64. The query is as follows -

mysql> select TO_BASE64(ProductId) as Base64Encode from Base64Demo;

Output

+------------------+
| Base64Encode     |
+------------------+
| MzIxUHJvZHVjdA== |
| UHJvZHVjdDQ1Ng== |
| NjU0UHJvZHVjdA== |
+------------------+
3 rows in set (0.00 sec)

Now let us cross check whether we get the original value after decoding the first value in the column. The query is as follows -

mysql> select FROM_BASE64('MzIxUHJvZHVjdA==') as DecodeValue;

Output

+-------------+
| DecodeValue |
+-------------+
| 321Product  |
+-------------+
1 row in set (0.00 sec)

Yes, we are getting the initial value.

The above is the detailed content of Understanding base64 encoding in MySQL?. For more information, please follow other related articles on the PHP Chinese website!