一次通过容器管理工具修改数据库的奇特经历

需求场景

要修改mysql数据库中的数据，且只能通过容器管理工具(portainer)

过程记录

**step1: ** 通过portainer工具登录mysql容器的控制台

mysql -uroot -p

mysql> use eladmin
Database changed

mysql>  select * from dw_bzkss_xh;
+-----+-------------+----------+------------+----------+------------+---------------------+
| id  | yljgdm      | ksid     | name       | skksdm   | skksmc     | create_time         |
+-----+-------------+----------+------------+----------+------------+---------------------+
|   1 | 001 | 01000000 | ??         | 03.01    | ??         | NULL                |
|   2 | 001 | 01010100 | ??????     | 03.01    | ??????     | NULL                |
| 119 | 001 | 02011400 | ??????     | 04.01.04 | ?????      | 2021-07-14 14:56:08 |
| 120 | 001 | 0P030000 | ???        | 09.02    | ???        | NULL                |
+-----+-------------+----------+------------+----------+------------+---------------------+
4 rows in set (0.00 sec)

**step2: ** 查询结果显示的是乱码，可以通过修改字符集解决

mysql> show variables like 'character%';
+--------------------------+----------------------------+
| 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.00 sec)

mysql> set character_set_client  = 'utf8' ;
mysql> set character_set_connection  = 'utf8' ;
mysql> set character_set_results = 'utf8' ;

**step3: ** 解决后可以正常查看中文了

mysql> select * from dw_bzkss_xh;
+-----+-------------+----------+--------------------------------+----------+--------------------------------+---------------------+
| id  | yljgdm      | ksid     | name                           | skksdm   | skksmc                         | create_time         |
+-----+-------------+----------+--------------------------------+----------+--------------------------------+---------------------+
|   1 | 001 | 01000000 | 内科                         | 03.01    | 内科                         | NULL                |
|   2 | 001 | 01010100 | 呼吸内科门诊             | 03.01    | 呼吸内科专业             | NULL                |
| 119 | 001 | 02011400 | 肝胆外科门诊             | 04.01.04 | 肝胆胰外科                | 2021-07-14 14:56:08 |
| 120 | 001 | 0P030000 | 营养科                      | 09.02    | 营养科                      | NULL                |
+-----+-------------+----------+--------------------------------+----------+--------------------------------+---------------------+
119 rows in set (0.00 sec)

插入数据，碰到难题了，不支持输入中文，最后(通过locale -a查看)发现mysql这个容器本身不支持中文

**step4: ** 此时通过mysql这个容器已经没有办法了，只能另辟蹊径，业务容器python是支持中文也可以操作mysql，可以直接在python容器中执行python代码呀

import pymysql

conn = pymysql.connect(host="mysql", port=3306, user="root", passwd="!Aa123456", db="eladmin")
cur = conn.cursor()
sql_str= "INSERT INTO `dw_bzkss_xh` VALUES (120, '001', '0P030000', '营养科', '09.02', '营养科', NULL)"
cur.execute(sql_str)
conn.commit()
cur.close()
conn.close()

参考：

• 解决MySQL控制台终端无法输入中文
• python mysql 操作数据代码样例