In MySQL 8.x the string function FROM_BASE64(str) returns result in hexadecimal form instead of a st-CodePudding

I have the following queries -

SELECT "Abc" AS result;
 -------- 
| result |
 -------- 
| Abc    |
 -------- 

SELECT TO_BASE64("Abc") AS result;
 -------- 
| result |
 -------- 
| QWJj   |
 -------- 

SELECT FROM_BASE64(TO_BASE64("Abc")) AS result;
 ---------------- 
| result         |
 ---------------- 
| 0x416263       |
 ----------------

--binary-as-hex page in mysql dev site says -

To disable hexadecimal notation, use --skip-binary-as-hex

I tried the following but got error -

mysql> SELECT FROM_BASE64(TO_BASE64("Abc")) --skip-binary-as-hex AS result;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as-hex AS result' at line 1
mysql> SELECT FROM_BASE64(TO_BASE64("Abc") --skip-binary-as-hex)  AS result;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as-hex)  AS result' at line 1
mysql> SELECT FROM_BASE64(TO_BASE64("Abc" --skip-binary-as-hex))  AS result;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as-hex))  AS result' at line 1

In the same page they have said how can I use the USING utf8mb4 clause to get the result in case of CHAR() and CONVERT() functions but they have not stated anything about FROM_BASE64() function. Nevertheless, I tried it and got error -

SELECT FROM_BASE64(TO_BASE64("Abc") USING utf8mb4) AS result;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING utf8mb4) AS result' at line 1

I tried the UNHEX() function -

ELECT UNHEX(FROM_BASE64(TO_BASE64("Abc"))) AS result;
 ---------------- 
| result         |
 ---------------- 
| 0x0ABC         |
 ----------------

Clearly this is not the desired result as well as all are in capital. Here LCASE() wouldn't work as well as it will turn every word to lower case.

Even, this result is not in string as is evident from -

SELECT SUBSTRING(UNHEX(FROM_BASE64(TO_BASE64("Abc"))) FROM 4) AS result;
 ---------------- 
| result         |
 ---------------- 
| 0x             |
 ----------------

So the only option seems to be to disable -binary-as-hex

But I can't find out a way to do this.

Here as similar question is there in stackoverflow -

'FROM_BASE64' Function Returns Hex Value

But it is on MySQL Version 5.6.14. I am using MySQL Version 8.0.27 -

mysql --version
mysql  Ver 8.0.27 for Linux on x86_64 (MySQL Community Server - GPL)

So my case is different.

CodePudding user response：

The --skip-binary-as-hex option is to be used as an option to the mysql command when you open that from a shell prompt. It's not an option to be used within SQL syntax. See https://dev.mysql.com/doc/refman/8.0/en/mysql-command-options.html#option_mysql_binary-as-hex

That said, you can convert binary to strings even if binary-as-hex is enabled:

mysql> SELECT FROM_BASE64(TO_BASE64("Abc")) AS result;
 ---------------- 
| result         |
 ---------------- 
| 0x416263       |
 ---------------- 
1 row in set (0.00 sec)

mysql> SELECT CONVERT(FROM_BASE64(TO_BASE64("Abc")) USING utf8mb4) AS result;
 -------- 
| result |
 -------- 
| Abc    |
 -------- 
1 row in set (0.00 sec)

You may need to use a different character encoding. Mine is utf8mb4, but yours may be different.