MySQL字符集详解
一、MySQL字符集编码简单介绍
在使用MySQL时要注意6个需要编码的地方：系统的编码、客户端、服务端、库、表、列。字符集编码不仅影响数据存储,还影响client程序和数据库之间的交互.在mysql中输入命令show session variables like '%char%'能够看到例如以下一些字符集（下面是以win10为例，生产中多数时linux，在linux里面除了latin1之外都是utf8的字符集）:
mysql> show variables like "%char%";
+--------------------------+-------------------------------------------+
| Variable_name | Value |
+--------------------------+-------------------------------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | F:\jj\mysql-5.6.42-winx64\share\charsets\ |
+--------------------------+-------------------------------------------+
8 rows in set (0.00 sec)
mysql中的字符集都相应着一个默认的校对规则(COLLATION),当然一个字符集也可能相应多个校对规则,可是两个不同的字符集不能相应同一个规则。使用默认的就可以了
以下来看看上面命令列出的字符集相关变量的含义：
- character_set_client:server解析客户端sql语句的字符集.(The character set for statements that arrive from the client. The session value of this variable is set using the character set requested by the client when the client connects to the server).
- character_set_connection:字符串字面值（literal strings）的字符集.
- character_set_results:server返回给客户端的查询结果或者错误提示的字符集编码.(The character set used for returning query results such as result sets or error messages to the client)
- character_set_system:这是mysqlserver用来存储元数据的编码,通常就是utf8,不要去改动它.
- character_sets_dir:这是mysql字符集编码存储文件夹.
- character_set_filesystem:这是文件系统字符集编码,主要用于解析用于文件名称的字符串字面值,如LOAD DATA INFILE和SELECT ...INTO OUTFILE等语句以及LOAD_FILE()函数.在打开文件之前,文件名称会从character_set_client转换为character_set_filesystem指定的编码.默认值为binary,也就是说不会进行转换.比如我们设置的character_set_client=GBK,而character_set_filesystem为默认值的话,则采用SELECT...INTO OUTFILE "文件名称",文件名称为GBK编码.反之,假设我们设置了character_set_filesystem=UTF8,则导出的文件名称为UTF8编码. 比如:linux系统的终端编码是UTF8,系统默认语言和编码为zh_CN.UTF8.我们有一个数据库名为test,test中有个表名为t1,编码为latin1,另外,我们在mysqlclient运行了SET NAMES GBK,假设我们不改动character_set_filesystem的值,运行SELECT * FROM t1 INTO OUTFILE '文件1', 能够发现相应的文件夹以下生成了一个名为"文件1"的文件,那文件名称编码是什么呢?事实上这里有几个地方须要注意,首先,我们的sql语句里面的"文件1"原生编码就是终端编码UTF8,也就是'\xe6\x96\x87\xe4\xbb\xb61',而导出数据的语句SELECT * FROM t1 INTO OUTFILE '文件1',依照前面的说法,由于character_set_filesystem为binary,因此'\xe6\x96\x87\xe4\xbb\xb61'不会转换,这样终于还是'\xe6\x96\x87\xe4\xbb\xb61',这样在zh_CN.UTF8的系统中文件名称不会乱码.而假设我们设置了character_set_filesystem=UTF8,则原生的'\xe6\x96\x87\xe4\xbb\xb61'会先依照GBK解码,然后用UTF8编码,最后的结果是"\xe9\x8f\x82\xe5\x9b\xa6\xe6\xac\xa21",这样文件名称就会乱码了.所以这个变量也最好不要改动,用默认值就OK.
- character_set_server:服务器默认字符集编码,假设创建数据库的时候没有指定编码,则采用character_set_server指定编码.
- character_set_database:默认数据库的字符集编码.假设没有默认数据库,则该变量值与character_set_server同样.事实上这个值代表的就是你当前数据库的编码而已,比方使用"use test",而test数据库的编码为latin1的话,这个值就是latin1.而你切换的时候"use test2",则character_set_database的值就是数据库test2的编码.
二、MySQL字符集编码层次
第一部分主要是归纳了MySQL文档中关于字符集编码的说明。这部分主要说明下MySQL中字符集编码层次:服务端-->数据库-->表-->字段。
关于系统的编码主要针对的是我们将来在存储文件的时候，有可能会将文件直接存贮在mysql的服务器上，那么，我们在数据库里面存的就是这些文件的路径，实际文件是存在系统里面的，那么文件名称就会受到你系统编码的影响，比如我们mysql设置的utf8编码的格式存储的文件路径，但是系统默认是gbk编码的，那么文件在保存到系统里的时候，文件的名称和你存在mysql里面的文件名称就对应不上了，出现乱码显示的问题，所以也要注意系统的编码。客户端的编码问题，我们在第一节的时候说了一下，大家应该比较了解啦。所以我们下面之说mysql内部设置的这些编码问题。
简单来说,服务器编码就是character_set_server来指定的.当我们创建数据库的时候能够指定编码,假设没有指定,采用的就是character_set_server指定的编码.比如:我们使用"create database t1 character set gbk",这里我们指定了数据库t1的编码为gbk,所以不会采用character_set_server指定的编码.而假设我们使用"create database t2",则通过"show create database t2"能够看到t2的编码为character_set_server定的编码.
同理,mysql表也能够有自己独立的编码,在创建表的时候能够指定,假设没有指定,则默认采用数据库的编码.比方我们再之前的数据库t1创建表t11,"create table t11(i int) character set utf8",则表t11的编码为utf8,假设不指定编码则编码为数据库t1的编码gbk.
此外,mysql表中的字段也能够有自己的编码,假设不指定字段编码,则字段编码与表的编码一致.
三、MySQL连接字符集
前面谈到的编码内容基本都不会产生乱码问题,mysql中容易产生乱码的地方在character_set_client, character_set_connection, character_set_results这三个变量的设定.能够简单的通过set names utf8或者charset utf8命令来一次设置这三个參数.
从文档中的解释来看,mysql连接字符集转换主要包含以下三个步骤:
- 1.character_set_client是client发送过来的sql语句的编码,由于服务端本身并不知道client的sql语句的编码是什么,所以是以这个变量作为clientsql语句的初始编码.而服务端接收到sql语句后,则会将sql语句转换为character_set_connection指定的编码(注意,对于字面值字符串,假设前面有introducer标记如latin1或utf8,则不会进行这一步转换).转换完毕,才会真正运行sql语句.
- 2.进行内部操作前将sql语句中的数据从character_set_connection转换为数据表中对应字段的编码.
- 3.将操作结果从内部字符集编码转换为character_set_results编码.
