2021-07-19

[HY000][1366] Incorrect string value: ‘\xE5\xB0\x8F\xE9\xB8\xA3’ for column ‘name’ at row 1
问题
最近使用docker部署了mysql5.7,然后新建库表后, 插入数据后提示字符集错误

create database test;
use test;

create table test_user
(
id int auto_increment,
name varchar(50) null,
constraint test_user_pk primary key (id)
);

insert into test.test_user (name)
values (‘小鸣’);
处理过程
首先查看 MySQL 系统变量关于字符编码的值 show variables like ‘%char%’;
Veriable_name Value
character_set_database latin1
character_set_server latin1
查看数据库建表 sql, 可以看到模式字符类型为 latin1;
show create database test_user;

output

CREATE TABLE test_user (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(50) CHARACTER SET latin1 DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1
修改字符集

修改数据库字符编码

alter database test char set utf8;
然后重新 insert 中文数据, 依旧提示1366错误, 然后查看建表 SQL, 可见默认使用的还是 latin1 字符编码

show create table test_user;

output

CREATE TABLE test_user (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(50) CHARACTER SET latin1 DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1

修改表的字符集

alter table test_user default character set utf8;

修改表字段字符集

alter table test_user change name name varchar(250) character set utf8;

或 直接修改表及表字段字符集
alter table test_user convert to character set utf8;
insert 中文数据成功

插曲: 在我修改数据库默认编码后新建表user_login_log, 然后插入中文数据, 可正常insert , 查看其建表语句, 可见默认为utf8, 即由于数据库默认被设置成了utf8, 建表也默认为utf8;

create table user_login_log(
id int auto_increment,
user_id int null ,
detail varchar(250) null ,
login_time datetime default now() null ,
constraint user_login_log_pk primary key(id)
) ;

show create table user_login_log;

output

CREATE TABLE user_login_log (
id int(11) NOT NULL AUTO_INCREMENT,
user_id int(11) DEFAULT NULL,
detail varchar(250) DEFAULT NULL,
login_time datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
修改服务器字符集及服务器排序规则

修改之前查看: show variables like ‘%server%’
Veriable_name Value
character_set_server latin1
collation_server latin1_swedish_ci
最后, 我们可以看到 character_set_server的字符默认也是latin1 , 我们就不通过set方式修改, 需要直接去修改 my.ini 配置文件;
我这里 mysql 5.7 / Ubuntu 部署在 docker 中:

查看运行容器

root@VM-155-245-ubuntu:~# docker ps

进入容器

root@VM-155-245-ubuntu:~# docker exec -it 85a bash

修改配置文件 (这里需要先安装vim : apt install vim)

末尾添加服务器字符集及服务器排序规则

root@85aexxx:/# vim /etc/mysql/mysql.conf.d/mysqld.cnf
…
character_set_server=utf8
collation_server=utf8_general_ci
…

重启服务

root@85aexxx:/# service mysql restart
[info] Stopping MySQL Community Server 5.7.29.
…root@VM-155-245-ubuntu:~#
这里遇到一个小插曲: 修改配置文件后结果无法启动了, 容器直接关机了, 再次开机也没用, 查看日志 :

root@VM-155-245-ubuntu:~# docker logs mysql57

…

2020-09-05 06:07:58+00:00 [ERROR] [Entrypoint]: mysqld failed while attempting to check config
command was: mysqld --verbose --help
2020-09-05T06:07:58.303495Z 0 [ERROR] unknown variable ‘conllation_server=utf8_general_ci’
原来这里conllation_server=utf8_general_ci输错了, 应该是 collation_server=utf8_general_ci

从容器中拷贝出配置文件

root@VM-155-245-ubuntu:~# docker cp mysql57:/etc/mysql/mysql.conf.d/mysqld.cnf .

修改后拷贝覆盖容器中配置文件

root@VM-155-245-ubuntu:~# docker cp mysqld.cnf mysql57:/etc/mysql/mysql.conf.d/mysqld.cnf
启动容器, 查看服务器字符集及服务器字符排序规则, 已修改成功
参考
MySQL服务器维护许多配置其操作的系统变量。每个系统变量都有一个默认值。可以在服务器启动时使用命令行或选项文件中的选项设置系统变量。可以使用以下SET 语句在运行时动态更改其中的大多数内容，该 语句使您无需停止并重新启动服务器即可修改服务器的操作。您还可以在表达式中使用系统变量值。

这里说明使用 set 系统变量名=值 只能动态修改, 重启会恢复默认;
默认数据库使用的字符集。每当默认数据库更改时，服务器都会设置此变量。如果没有默认数据库，则该变量的值与相同character_set_server。
全局 变量character_set_database和 collation_database系统变量在MySQL 5.7中已弃用，并将在以后的MySQL版本中删除。
在MySQL 5.7中不建议 为会话character_set_database和 collation_database系统变量分配值， 并且分配会产生警告。会话变量在MySQL的未来版本中将变为只读，并且赋值将产生错误。仍然可以访问会话变量，以确定默认数据库的数据库字符集和排序规则。

5.1.7服务器系统变量 # character_set_database

13.7.4.1变量分配的SET语法(动态修改)

字符集: Character , 字符编码: Character Encoding; 注意区分两者不同;

• 参考: 字符集和字符编码（Charset & Encoding）
• Latin1是ISO-8859-1的别名.

10.10支持的字符集和排序规则