MySQL 设置全局数据库编码为 UTF8

一般 mysql 安装完毕后，数据库信息如下：

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.7.26, for Linux (x86_64) using  EditLine wrapper

Connection id:		6917
Current database:
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.7.26 MySQL Community Server (GPL)
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/var/lib/mysql/mysql.sock
Uptime:			80 days 22 hours 29 sec

Threads: 41  Questions: 13193212  Slow queries: 1149493  Opens: 30923  Flush tables: 161  Open tables: 476  Queries per second avg: 1.887

--------------
​

默认字符集为 lantin1,下面介绍修改全局字符编码的方法:

1.修改 mysql 的配置文件，添加如下内容:

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
skip-character-set-client-handshake

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysql.server]
default-character-set=utf8

[mysqld_safe]
default-character-set=utf8
​

docker 容器中的 mysql 我采用挂载配置文件的方式让配置生效，路径为:/etc/mysql/mysql.conf.d/mysqld.cnf
centos 下 mysql 配置文件路径：/etc/my.cnf
ubuntu 下 mysql 配置文件路径：/etc/mysql/mysql.conf.d/mysqld.cnf

2.重启 mysql 服务或 docker 容器
效果：

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.7.27, for Linux (x86_64) using  EditLine wrapper

Connection id:		39
Current database:
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.7.27 MySQL Community Server (GPL)
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8
Db     characterset:	utf8
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/var/run/mysqld/mysqld.sock
Uptime:			3 hours 10 min 24 sec

Threads: 6  Questions: 5520  Slow queries: 0  Opens: 172  Flush tables: 1  Open tables: 165  Queries per second avg: 0.483
--------------
​

mysql> show variables like 'collation_%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.01 sec)
​

mysql> show variables like 'character_set_%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
​