[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的未来版本中将变为只读，并且赋值将产生错误。仍然可以访问会话变量，以确定默认数据库的数据库字符集和排序规则。
-
字符集:
Character, 字符编码:Character Encoding; 注意区分两者不同;
* 参考: 字符集和字符编码（Charset & Encoding）
* Latin1是ISO-8859-1的别名.
