Docker容器中MySQL 8命令行UTF-8字符粘贴失效问题

问题描述 #

在Docker容器中运行MySQL 8时，将UTF-8字符（例如🤌）粘贴到终端的MySQL命令行中，字符会被自动删除。例如原本执行的语句：

mysql> INSERT INTO your_table (name) VALUES ('🤌');

粘贴后变为：

mysql> INSERT INTO your_table (name) VALUES ('');

尝试过三种容器配置方式，结果均一致：

• 使用默认编码启动容器
• 通过命令行参数设置字符集
• 挂载自定义my.cnf配置文件

查看MySQL的character_set变量已正确设置为utf8mb4，但命令行粘贴UTF-8字符的问题依然存在。不过本地MySQL 5.7的命令行可正常粘贴，且通过IntelliJ DB Browser插件能向这3个容器内的数据库正常添加UTF-8字符。

测试详情 #

1. 未指定编码启动容器 #

% docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=root -p 9306:3306 -d mysql:8

% docker exec -it some-mysql mysql -uroot -proot

MySQL客户端输出及字符集查询结果：

mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.33 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | latin1             |
| character_set_connection | latin1             |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | latin1             |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8mb3            |
| collation_connection     | latin1_swedish_ci  |
| collation_database       | utf8mb4_0900_ai_ci |
| collation_server         | utf8mb4_0900_ai_ci |
+--------------------------+--------------------+
10 rows in set (0.05 sec)

mysql> INSERT INTO your_table (name) VALUES ('');

2. 通过命令行参数设置字符集启动容器 #

% docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=root -p 9306:3306 -d mysql:8 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

% docker exec -it some-mysql mysql -uroot -proot

MySQL客户端输出及字符集查询结果：

mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.33 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | latin1             |
| character_set_connection | latin1             |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | latin1             |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8mb3            |
| collation_connection     | latin1_swedish_ci  |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+
10 rows in set (0.18 sec)

mysql> INSERT INTO your_table (name) VALUES ('');

3. 挂载自定义my.cnf配置文件启动容器 #

自定义配置文件内容：

[client] 
default-character-set = utf8mb4
 
[mysql] 
default-character-set = utf8mb4
 
[mysqld] 
collation_server=utf8mb4_unicode_ci
character_set_server=utf8mb4

启动容器命令：

% docker run --name some-mysql -v /Users/me/Code/xxx/docker/mysql/conf.d:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=root -p 7306:3306 -d mysql:8

% docker exec -it some-mysql mysql -uroot -proot

MySQL客户端输出及字符集查询结果：

mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.33 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8mb4                        |
| character_set_connection | utf8mb4                        |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb4                        |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8mb3                        |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
| collation_connection     | utf8mb4_0900_ai_ci             |
| collation_database       | utf8mb4_unicode_ci             |
| collation_server         | utf8mb4_unicode_ci             |
+--------------------------+--------------------------------+
11 rows in set (0.01 sec)

mysql> INSERT INTO your_table (name) VALUES ('');

解决思路及方案 #

问题根源 #

Docker容器内的终端环境默认编码并非UTF-8，导致MySQL客户端无法正确识别粘贴的UTF-8字符，即使MySQL服务端和客户端的字符集配置正确，终端编码不匹配依然会过滤掉非兼容字符。本地MySQL 5.7正常是因为本地终端环境编码正确，且MySQL 5.7客户端对终端编码兼容性更好。

具体解决方法 #

1. 进入容器时指定UTF-8环境变量
   执行docker exec命令时添加-e LANG=C.UTF-8参数，强制容器内终端使用UTF-8编码：

   docker exec -it -e LANG=C.UTF-8 some-mysql mysql -uroot -proot
   

2. 优化my.cnf配置
   在[mysql]段添加init_connect='SET NAMES utf8mb4;'，确保每次连接时强制设置字符集：

   [mysql] 
   default-character-set = utf8mb4
   init_connect='SET NAMES utf8mb4;'
   

3. 检查本地终端编码
   本地终端编码需为UTF-8，可通过以下命令查看：

   echo $LANG
   

   输出应为zh_CN.UTF-8、en_US.UTF-8等UTF-8编码格式，若不是需调整本地终端的编码设置。

4. 升级MySQL镜像版本
   部分旧版本的MySQL 8镜像可能存在终端编码兼容问题，尝试升级到最新稳定版镜像：

   docker pull mysql:latest
   

内容的提问来源于stack exchange，提问作者Dagmar