mysql数据库编码详解

mysql数据库编码详解

参考: 《MySQL字符集与排序规则总结》
建议先阅读：《细说ASCII、GB2312/GBK/GB18030、Unicode、UTF-8/UTF-16/UTF-32编码》

一、mysql中字符集编码设置的级别

Mysql编码级别分为：system、server、database、table、column，还有3个和客户端通讯的client、connection、result。
进入数据库查看安装的MySQL各个级别的编码变量：

MariaDB [(none)]> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.001 sec)
 
MariaDB [(none)]>
————————————————

character_set_client、character_set_connection、character_set_results是一个mysql连接后mysql客户端和mysql服务器协商的编码规则。character_set_client表名客户端使用何种字符集编码序列化文本字符，character_set_connection一般和character_set_client保持一致即可（受SELECT _utf8'abc'形式语句的影响，参见： 《10.3.8 Character Set Introducers》 ），character_set_results表示服务端应该以何种编码返回文本。

character_set_database表示当前连接操作的数据库的字符集编码。

character_set_server表示mysql服务端的默认字符集编码。

character_set_system表示mysql存储元数据时使用的编码，固定为：utf8。

character_set_filesystem表示涉及到文件操作的时的编码，如：LOAD DATA INFILE和SELECT ... INTO OUTFILE语句和LOAD_FILE()函数中

Mysql编码层级

1. system

系统编码级别，没啥介绍的，就是当前操作系统使用的编码，应该是上面的character_set_system，一般都是utf8格式的，也不会去修改这个东西，不需要过多关注。

2. server

服务编码级别，即当前MySQL服务所使用的编码，对应上面的character_set_server，可以看到，我现在的编码是latin1，需要修改成UTF-8格式的

3. database

数据库级别，对应上面的character_set_database。如果新建数据库时未指定字符集编码则使用Mysql Server级别的设置。

-- 创建数据库时指定字符集编码和排序规则
CREATE DATABASE  test2
 	CHARACTER SET utf8mb4
 	COLLATE utf8mb4_general_ci;

4. table

表级别。在建表时指定编码，只对当前表所有未指定编码的列生效（因为下面还可以指定列编码，指定后表级别的编码对该列就不生效），这个指定是mysql独有的，而且只能通过sql在建表或修改表时指定， 在标准sql中，没有可指定表编码的sql语法

-- 创建表时指定字符集编码和排序规则
create table tt2(
	addr varchar(50)
) character set utf8mb4 collate utf8mb4_general_ci;

5. column

列级别，设置列的编码。建表或修改列时设置，标准sql语法。

-- 创建表时指定某个列的字符集编码和排序规则
create table tt4(
	name varchar(50) character set utf16 collate utf16_general_ci,
	addr varchar(50)
)character set utf8mb4 collate utf8mb4_general_ci;

Mysql与客户端通讯编码
通讯流程如下：

1. 服务端使用系统变量character_set_client来处理客户端发来的语句

2. 服务端会把客户端发来的语句（以character_set_client 编码）转换为character_set_connection编码。

3. 系统变量character_set_results用来把数据以该编码方式返回给客户端。

---

二、修改默认编码

[root@VM-0-16-centos etc]# mysql --help|grep -A1 -i 'default option' #查看默认安装加载的配置文件
Default options are read from the following files in the given order:
/etc/my.cnf ~/.my.cnf 
--
--no-defaults             Don't read default options from any option file.
The following specify which files/extra groups are read (specified before remaining options):
--defaults-file=#         Only read default options from the given file #.
--defaults-extra-file=#   Read this file after the global files are read.
[root@VM-0-16-centos etc]# vi /etc/my.cnf #修改默认编码
[mysqld]
character-set-server = utf8mb4
collation_server =utf8mb4_general_ci
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4

修改后重启MySQL服务,配置修改生效。

更改数据库级别的编码规则：

-- 数据库级别
ALTER DATABASE testdb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci

-- 更改表的编码级别
ALTER TABLE testtable CONVERT TO CHARACTER SET utf8mb4 collate utf8mb4_general_ci; 

-- 列级别
ALTER TABLE testtable MODIFY `colname` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

---

三、修改默认通信编码

通信编码在连接数据库时指定：

---

四、 可以存储emoji表情吗？

emoji表情属于Unicode后面的字符集，使用标准utf8编码是可以存储的（在mysql中是utf8mb4）。
只要我们保证，数据库（表、列）使用utf8mb4字符集编码，且客户端可Mysql服务端的连接使用的也是utf8mb4字符集编码，那么就可以愉快的存储emoji表情了。