Docker Mysql DB 初始化 UTF8 乱码问题的解决

最近在补单元测试，需要 mock 一个 DB，初始化 dump 数据阶段需要 dump 一些带中文的数据，但是发现跑起来都是乱码，非常迷惑，因为我们在链接、建立 Table 阶段都约定了：

1. # 连接串中
2. charset=utf8mb4,utf8

1. # 创建 DB
2. CREATE TABLE `lang` (
3. `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
4. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

在创建完的 DB 里执行插入也是一样，没有乱码。因此问题肯定出在了 dump 阶段，和我们的标准插入是有一定差异的，首先我们来看一下 docker-compose.yaml 的写法：

1. version: "3.7"
2. services:
3. db:
4. image: mysql:5.6
5. ports:
6. - 13306:3306
7. environment:
8. - MYSQL_ROOT_PASSWORD=root
9. - TZ=Asia/Shanghai
10. volumes:
11. - .:/docker-entrypoint-initdb.d
12. command: [
13. '--character-set-server=utf8mb4',
14. '--collation-server=utf8mb4_general_ci'
15. ]
16. healthcheck:
17. test: ["CMD", "mysqladmin" ,"ping", "--protocol=tcp"]
18. timeout: 20s
19. interval: 1s
20. retries: 20

在命令中我们可以看到我们也规定了编码，但结果还是会出现乱码。

回到 dump 中，Docker 中的 DB 初始化是从 docker-entrypoint-initdb.d 出发进行 mysql-dump 的（见 https://hub.docker.com/_/mysql - Initializing a fresh instance 一节）

When a container is started for the first time, a new database with the specified name will be created and initialized with the provided configuration variables. Furthermore, it will execute files with extensions .sh, .sql and .sql.gz that are found in /docker-entrypoint-initdb.d. Files will be executed in alphabetical order. You can easily populate your mysql services by mounting a SQL dump into that directory and provide custom images with contributed data. SQL files will be imported by default to the database specified by the MYSQL_DATABASE variable.

之后我们好不容易查到了对应的 issue：https://github.com/docker-library/mysql/issues/131。

the issue is not MySQL, but the moment the terminal (shell) imports the SQL statements. One simple proof (in this case for MariaDB, but same for MySQL)

因此我们其实应该设置的是 terminal 对应的环境变量 LANG=C.UTF-8，docker compose 变成了：

1. version: "3.7"
2. services:
3. db:
4. image: mysql:5.6
5. ports:
6. - 13306:3306
7. environment:
8. - MYSQL_ROOT_PASSWORD=root
9. - TZ=Asia/Shanghai
10. - LANG=C.UTF-8
11. volumes:
12. - .:/docker-entrypoint-initdb.d
13. command: [
14. '--character-set-server=utf8mb4',
15. '--collation-server=utf8mb4_general_ci'
16. ]
17. healthcheck:
18. test: ["CMD", "mysqladmin" ,"ping", "--protocol=tcp"]
19. timeout: 20s
20. interval: 1s
21. retries: 20

之后 dump 果然完事儿了。