mysql配置utf8mb4_mysql utf8mb4 conf

utf8和utf8mb4

MySQL在5.5.3之后增加了这个utf8mb4的编码，mb4就是most bytes 4的意思，专门用来兼容四字节的unicode，兼容Emoji 表情(Emoji 是一种特殊的 Unicode 编码，常见于 ios 和 android 手机上)。utf8mb4是utf8的超集，除了将编码改为utf8mb4外不需要做其他转换,只是utf8mb4更消耗空间

数据库配置编码格式

1：配置路径

ubuntu在 /etc/mysql/mysql.conf.d/mysqld.cnf

centos在 /etc/my.cnf

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-server=utf8mb4
character-set-client-handshake = FALSE
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
1
2
3
4
5
6
7
8
9
10
11

配置完成后要重启mysql,执行命令查看字符集

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_unicode_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14

解释：

character_set_database：就是数据库内部存储字符串用的编码

character_set_connection ：就是通过socket与mysql通信时的网络编码

character_set_client：mysql命令终端和navicat都属于客户端

在客户端输入

set character_set_client =utf8mb4;
set character_set_results = utf8mb4;
set character_set_connection = utf8mb4;
1
2
3

或者SET NAMES utf8mb4;就可以修改client编码

参考：
https://dev.mysql.com/doc/workbench/en/wb-mysql-connections-new.html