問題

最近使用docker部署了mysql5.7,然后新建庫表后, 插入數(shù)據(jù)后提示字符集錯誤

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 系統(tǒng)變量關(guān)于字符編碼的值 show variables like '%char%';

• 查看數(shù)據(jù)庫建表 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

• 修改字符集

# 修改數(shù)據(jù)庫字符編碼
alter database test char set utf8;

然后重新 insert 中文數(shù)據(jù), 依舊提示1366錯誤, 然后查看建表 SQL, 可見默認(rèn)使用的還是 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 中文數(shù)據(jù)成功

• 插曲: 在我修改數(shù)據(jù)庫默認(rèn)編碼后新建表user_login_log, 然后插入中文數(shù)據(jù), 可正常insert , 查看其建表語句, 可見默認(rèn)為utf8, 即由于數(shù)據(jù)庫默認(rèn)被設(shè)置成了utf8, 建表也默認(rèn)為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

修改服務(wù)器字符集及服務(wù)器排序規(guī)則

• 修改之前查看: show variables like '%server%'

• 最后, 我們可以看到 character_set_server的字符默認(rèn)也是latin1 , 我們就不通過set方式修改, 需要直接去修改 my.ini 配置文件;

我這里 mysql 5.7 / Ubuntu 部署在 docker 中:

# 查看運行容器
root@VM-155-245-ubuntu:~# docker ps

# 進(jìn)入容器
root@VM-155-245-ubuntu:~# docker exec -it 85a bash

# 修改配置文件 (這里需要先安裝vim : apt install vim)
# 末尾添加服務(wù)器字符集及服務(wù)器排序規(guī)則
root@85aexxx:/# vim /etc/mysql/mysql.conf.d/mysqld.cnf 
...
character_set_server=utf8
collation_server=utf8_general_ci
...

# 重啟服務(wù)
root@85aexxx:/# service mysql restart
[info] Stopping MySQL Community Server 5.7.29.
....root@VM-155-245-ubuntu:~# 

這里遇到一個小插曲: 修改配置文件后結(jié)果無法啟動了, 容器直接關(guān)機(jī)了, 再次開機(jī)也沒用, 查看日志 :

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輸錯了, 應(yīng)該是 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

• 啟動容器, 查看服務(wù)器字符集及服務(wù)器字符排序規(guī)則, 已修改成功

參考

MySQL服務(wù)器維護(hù)許多配置其操作的系統(tǒng)變量。每個系統(tǒng)變量都有一個默認(rèn)值。可以在服務(wù)器啟動時使用命令行或選項文件中的選項設(shè)置系統(tǒng)變量。可以使用以下SET 語句在運行時動態(tài)更改其中的大多數(shù)內(nèi)容，該 語句使您無需停止并重新啟動服務(wù)器即可修改服務(wù)器的操作。您還可以在表達(dá)式中使用系統(tǒng)變量值。

• 這里說明使用 set 系統(tǒng)變量名=值 只能動態(tài)修改, 重啟會恢復(fù)默認(rèn);

默認(rèn)數(shù)據(jù)庫使用的字符集。每當(dāng)默認(rèn)數(shù)據(jù)庫更改時，服務(wù)器都會設(shè)置此變量。如果沒有默認(rèn)數(shù)據(jù)庫，則該變量的值與相同character_set_server。
全局 變量character_set_database和 collation_database系統(tǒng)變量在MySQL 5.7中已棄用，并將在以后的MySQL版本中刪除。
在MySQL 5.7中不建議 為會話character_set_database和 collation_database系統(tǒng)變量分配值， 并且分配會產(chǎn)生警告。會話變量在MySQL的未來版本中將變?yōu)橹蛔x，并且賦值將產(chǎn)生錯誤。仍然可以訪問會話變量，以確定默認(rèn)數(shù)據(jù)庫的數(shù)據(jù)庫字符集和排序規(guī)則。

• 5.1.7服務(wù)器系統(tǒng)變量 # character_set_database

• 13.7.4.1變量分配的SET語法(動態(tài)修改)

• 字符集: Character , 字符編碼: Character Encoding; 注意區(qū)分兩者不同;
  * 參考: 字符集和字符編碼（Charset & Encoding）
  * Latin1是ISO-8859-1的別名.

• 10.10支持的字符集和排序規(guī)則