docker mysql 5.7以上支持中文编码问题

 

问题在部署时一直报错：

django.db.utils.OperationalError: (1067, "Invalid default value for 'project_name'")

查了下就是不支持中文输入和读取

[root@hadoop mysql]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
ccc1776cb6b5 nginx:alpine "/docker-entrypoint.…" 56 minutes ago Up 7 minutes 0.0.0.0:80->80/tcp drf-backend-platform_nginx_1
8ccf680a14b9 drf-backend-platform_rookie "/bin/bash -c 'pytho…" 56 minutes ago Restarting (1) 8 seconds ago rookie
48a92c5c2bff mysql:5.7 "docker-entrypoint.s…" 3 hours ago Up 7 minutes 3306/tcp, 33060/tcp mysql
ad2e3411542f redis:alpine "docker-entrypoint.s…" 17 hours ago Up 7 minutes 6379/tcp drf-backend-platform_redis_1
[root@hadoop mysql]# docker exec -it 48a92c5c2bff /bin/bash
root@mysql:/# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 114
Server version: 5.7.34 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, 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 LIKE 'character_set_%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.03 sec)

mysql> SHOW VARIABLES LIKE 'collation_%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+

图上的第一个 connection 就是我们通过workbench等客户端连接的时候指定的编码。 
外部访问数据乱码的问题就出在这个connection连接层上

SET NAMES 'utf8';

解决办法：


修改docker-compose  mysql服务的command参数：

command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
如下：

services:
  mysql:
    image: mysql:5.7
    container_name: mysql   # settings 的HOST 填写的是容器的名称
    hostname: mysql
    restart: always
    volumes:
      - ./mysql:/var/lib/mysql  # 把当前文件夹下的 ./mysql文件夹挂载到docker容器 /var/lib/mysql 路径下
    ports:
      - "3306:3306"
    environment:
      - MYSQL_HOST=db
      - MYSQL_ROOT_PASSWORD=App@123456
      - MYSQL_DATABASE=drf_backend
      - MYSQL_USER=App
      - MYSQL_PASSWORD=App@123456
    command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

　　

如果使用dockerfile启动 则直接在命令行后加：

--character-set-server=utf8 --collation-server=utf8_general_ci

 

 

 

具体信息见：
https://www.cnblogs.com/Again/articles/9487887.html