阅读 348

mysql编排进docker-compose:踩坑记录

1. 容器编码目录结构

test
 └─docker-compose
 │      ├─app                      
 │      │      Dockerfile         
 │      │
 │      └─mysql
 │          └─initdb
 │               erp_db.sql
 └─docker-compose.yml
复制代码

1.1 app/Dockerfile作为应用在本篇略过

1.2 erp_db.sql

create database if not exists erp_db;
use erp_db;

CREATE TABLE `m_code` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `kind` varchar(3) NOT NULL COMMENT '分类',
  `code` varchar(3) NOT NULL COMMENT '编码',
  `name` varchar(30) DEFAULT NULL COMMENT '含义',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='编码主数据'
;
INSERT INTO `m_code` (`id`, `kind`, `code`, `name`) VALUES (1,'PUR','001','标准采购');
INSERT INTO `m_code` (`id`, `kind`, `code`, `name`) VALUES (2,'PUR','002','批量采购');
INSERT INTO `m_code` (`id`, `kind`, `code`, `name`) VALUES (3,'SAL','001','批发');
INSERT INTO `m_code` (`id`, `kind`, `code`, `name`) VALUES (4,'SAL','002','零售');
INSERT INTO `m_code` (`id`, `kind`, `code`, `name`) VALUES (5,'SAL','003','在线');
复制代码

1.3 docker-compose.yml

services:
  erp_server:
    container_name: mysql5.7_erp
    image: mysql:5.7
    environment:
      MYSQL_DATABASE: erp_db
      MYSQL_USER: admin
      MYSQL_PASSWORD: password
      MYSQL_ROOT_PASSWORD: rootpassword
    expose:
      - 3306
    ports:
      - 3306:3306
    # 为防止中文乱码,加以下启动参数
    command: ['mysqld', '--character-set-server=utf8mb4', '--collation-server=utf8mb4_unicode_ci', '--skip-character-set-client-handshake']
    volumes:
      # 起動スクリプト
      - ./docker-compose/mysql/initdb:/docker-entrypoint-initdb.d/
      # DBの永続化
      - mysql_db:/var/lib/mysql
      
volumes:
  mysql_db:
    driver: local
复制代码

2. 踩坑记录

2.1 现象

按照上述的编排后,发现进到数据库的数据是乱码

2.2 查看charset

show variables like '%chara%';
复制代码

2.3 根据charset,就有了如下两个思路

2.3.1 思路一:把红线部分变成utf8,docker-compose.yml追加command

services:
  erp_server:
    container_name: mysql5.7_erp
    image: mysql:5.7
    command: mysqld --character-set-server=utf8 --collation-server=utf8_unicode_ci
    environment:
      MYSQL_DATABASE: erp_db
      MYSQL_USER: admin
      MYSQL_PASSWORD: password
      MYSQL_ROOT_PASSWORD: rootpassword
    expose:
      - 3306
    ports:
      - 3306:3306
    volumes:
      # 起動スクリプト
      - ./docker-compose/mysql/initdb:/docker-entrypoint-initdb.d/
      # DBの永続化
      - mysql_db:/var/lib/mysql
      
volumes:
  mysql_db:
    driver: local
复制代码

再重做一遍

$ docker-compose down
$ docker-compose up -d
复制代码

注意:此时如果没有反应到mysql

$ docker volume ls
$ docker volume rm [volume-name]
复制代码

再确认下表数据是否变好

2.3.2 思路二,直接在erp_db.sql写

docker-compose.yml恢复如初 erp_db.sql

create database if not exists erp_db;
use erp_db;
SET CHARACTER_SET_CLIENT = utf8;
SET CHARACTER_SET_CONNECTION = utf8;

CREATE TABLE `m_code` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `kind` varchar(3) NOT NULL COMMENT '分类',
  `code` varchar(3) NOT NULL COMMENT '编码',
  `name` varchar(30) DEFAULT NULL COMMENT '含义',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='编码主数据'
;
INSERT INTO `m_code` (`id`, `kind`, `code`, `name`) VALUES (1,'PUR','001','标准采购');
INSERT INTO `m_code` (`id`, `kind`, `code`, `name`) VALUES (2,'PUR','002','批量采购');
INSERT INTO `m_code` (`id`, `kind`, `code`, `name`) VALUES (3,'SAL','001','批发');
INSERT INTO `m_code` (`id`, `kind`, `code`, `name`) VALUES (4,'SAL','002','零售');
INSERT INTO `m_code` (`id`, `kind`, `code`, `name`) VALUES (5,'SAL','003','在线');
复制代码

再重做一遍

$ docker-compose down
$ docker-compose up -d
复制代码

注意:此时如果没有反应到mysql

$ docker volume ls
$ docker volume rm [volume-name]
复制代码

和最初现象一样

再确认表数据

3. 结论

修改配置文件或者容器编排文件,都没有解决问题;只有在sql文中修改才能最终解决问题。

文章分类
后端
文章标签