我有以下查询-
SELECT "Abc" AS result;
+--------+
| result |
+--------+
| Abc |
+--------+
SELECT TO_BASE64("Abc") AS result;
+--------+
| result |
+--------+
| QWJj |
+--------+
SELECT FROM_BASE64(TO_BASE64("Abc")) AS result;
+----------------+
| result |
+----------------+
| 0x416263 |
+----------------+
--二进制作为mysql开发站点中的十六进制页面说-
要禁用十六进制表示法,请使用
--skip-binary-as-hex
我尝试了以下操作,但出现错误-
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
在同一页中,他们说了在CHAR()
和CONVERT()
函数的情况下如何使用USING utf8mb4
子句来获得结果,但他们没有说明任何关于FROM_BASE64()
函数的内容。尽管如此,我还是尝试了一下,结果出现了错误-
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
我尝试了UNHEX()
功能-
ELECT UNHEX(FROM_BASE64(TO_BASE64("Abc"))) AS result;
+----------------+
| result |
+----------------+
| 0x0ABC |
+----------------+
显然,这并不是理想的结果,因为所有这些都是资本。在这里,LCASE()
不能很好地工作,因为它会把每个单词都变成小写。
即使如此,这个结果也不在字符串中,从-中可以明显看出
SELECT SUBSTRING(UNHEX(FROM_BASE64(TO_BASE64("Abc"))) FROM 4) AS result;
+----------------+
| result |
+----------------+
| 0x |
+----------------+
因此,唯一的选择似乎是禁用-binary-as-hex
但我找不到办法。
这里有类似的问题在stackoverflow-
"FROM_BASE64"函数返回十六进制值
但它是在MySQL的版本5.6.14上。我使用的是MySQL版本8.0.27-
mysql --version
mysql Ver 8.0.27 for Linux on x86_64 (MySQL Community Server - GPL)
所以我的情况不同。
当您从shell提示符打开mysql命令时,--skip binary as hex选项将用作该命令的选项。它不是在SQL语法中使用的选项。看见https://dev.mysql.com/doc/refman/8.0/en/mysql-command-options.html#option_mysql_binary-作为十六进制
也就是说,即使启用了二进制十六进制,也可以将二进制转换为字符串:
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)
您可能需要使用不同的字符编码。我的是utf8mb4,但你的可能不同。