mysql中composekey定义_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

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文中修改才能最终解决问题。