在 MySQL 中，CHAR() 函数工作起来很奇怪

2022-01-12 00:00:00 char mysql

当我编写 CHAR() 函数时.例如:

When I write CHAR() function. For example:

mysql> select char(97);
+--------------------+
| char(97)           |
+--------------------+
| 0x61               |
+--------------------+

我知道这是不对的.

mysql> select char(97);
+--------------------+
| char(97)           |
+--------------------+
| a                  |
+--------------------+

也许这是正确的情况，对吧?

Maybe this is right situation, right?

你能解释一下我该如何解决这个问题吗?

Can you explain how can I fix this problem?

推荐答案

在 MySQL 8.0.19 之前，您遇到的问题不存在.binary-as-hex 客户端选项在 MySQL 8.0.19 中默认启用.

Prior to MySQL 8.0.19, the issue which you are facing did not exist. binary-as-hex client option is enabled by default from MySQL 8.0.19.

在 MySQL 中运行 status 或 s.您会注意到有关二进制数据的一行.如果被提及为十六进制，则 CHAR() 和 UNHEX() 等函数的输出将显示为十六进制而不是普通文本.

Run status or s in MySQL. You will notice a line about binary data. If it is mentioned as hexadecimal, outputs of functions like CHAR() and UNHEX() will displayed as hexadecimal instead of normal text.

为避免这种情况，您可以使用 CHAR() 的 USING 字符集子句.

To avoid this you can use the USING charset clause of CHAR().

mysql> SELECT CHAR(97);
+--------------------+
| CHAR(97)           |
+--------------------+
| 0x61               |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT CHAR(97 USING utf8mb4);
+------------------------+
| CHAR(97 USING utf8mb4) |
+------------------------+
| a                      |
+------------------------+
1 row in set (0.00 sec)

或者，您可以使用 --skip-binary-as-hex.在 Windows 中要遵循的步骤是:

Or, you can use --skip-binary-as-hex. The steps to follow in Windows are:

  1. 打开命令提示符.

  1. Open Command Prompt.

更改目录.就我而言，命令是:

Change the directory. In my case the command was:

cd "C:Program FilesMySQLMySQL Server 8.0in"

  • 运行

  • Run

    mysql -u root -p --skip-binary-as-hex

  • 输入密码.

  • Enter the password.

    现在，CHAR() 函数的输出将如您所愿.

    Now, the outputs of CHAR() function will be as you expect it to be.

    注意:--skip-binary-as-hex 方法在您退出 MySQL 之前有效.每次打开 MySQL 时都需要使用 --skip-binary-as-hex 方法，这样十六进制值就不会显示了.

    Note: The --skip-binary-as-hex method works until you quit MySQL. You need to use --skip-binary-as-hex method every time you open MySQL such that hexadecimal values will not be shown.

    您可以参考这些链接了解更多信息:

    You can refer these links for more information:

    • https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_char
    • https:///dev.mysql.com/doc/refman/8.0/en/mysql-command-options.html#option_mysql_binary-as-hex

    • 相关文章