MariaDB&MySQL数据库及操作系统字符集设置及关系

1         LINUX操作系统

1.1       系统LANG变量

可以使用locale -a查看系统支持的字符集。
使用export LANG=zh_CN.utf8或zh_CN.gbk进行设置。
如使用ssh客户端连接服务器,如SecrueCRT等,需要设置与服务器相同的字符集才能正常显示。

2         数据库

2.1       数据库字符集变量

查看数据库系统字符集信息:
这些变量都可以通过set 及set global进行修改,重启后失效。

以下为每个变量介绍:

  • character_set_client :客户端字符集,可以通过修改my.cnf中[client]段中的default-character-set,重启客户端生效。
  • character_set_connection:连接字符集,可以通过修改my.cnf中[client]段中的default-character-set,重启客户端生效。
  • character_set_database:数据库默认字符集,创建数据库时指定,不指定则默认使用服务器字符集。建表时如不指定字符集则继承数据库默认字符集。Load data时数据文件字符集需与该值一致。
  • character_set_filesystem :文件系统字符集,linux系统默认为binary。该值不会影响乱码。
  • character_set_results:结果字符集,可以通过修改my.cnf中[client]段中的default-character-set,重启客户端生效。
  • character_set_server:服务器默认字符集,可以通过修改my.cnf中[mysqld]段中的character-set-server,重启服务端生效。如果不配置,则以编译代码时的-DDEFAULT_CHARSET选择为默认值。默认为latin1。
  • character_set_system:MariaDB系统自用字符集,恒为utf8。该值不会影响乱码。
SET NAMES ‘utf8’;  它相当于下面的三句命令:
SET character_set_client = utf8;
SET character_set_results = utf8;
SET character_set_connection = utf8;

2.1.1    表以及列字符集

创建表时指定表和列的字符集,如果不指定,则列从表继承,表从character_set_database继承。后期也可通过alter table命令修改。

2.2       SQL脚本

一个用户请求字符集转换完整流程:

1、Mysql客户端以character_set_client解析SQL语句

2、转化为character_set_connection发送到服务端(character_set_connection字符集必须大于等于character_set_client,否则会丢失数据。如utf8>gbk>latin1)

3、服务端转化为内部字符集

(同理,内部字符集必须大于等于character_set_connection,否则会丢失数据。
按照如下规则:
    A. 转化为每个数据字段的CHARACTER SET设定值;
    B. 若上述值不存在,则继承对应数据表的CHARACTER SET设定值
    C. 若上述值不存在,则继承对应数据库的 character_set_database设定值;
    D. 若上述值不存在,则继承character_set_server设定值。)

4、最后服务端将操作结果从内部操作字符集转换为character_set_results返回客户端。

sql脚本编码必须与character_set_client一致,否则会出现乱码。

2.3       LOAD导入数据文件

LOAD命令字符集转换完整流程:

1、以LOAD命令里指定的CHARACTER SET解析数据文件(如没有指定,则以character_set_database设定值解析)

2、服务端转化为内部字符集(规则同2.2)

如不在LOAD命令中指定,则数据文件编码必须与character_set_database一致,否则会出现编码错误无法导入。

2.4       导出数据文件

如不在导出命令中指定字符集,则不进行转换,以数据列的字符集直接导出。(如果表的不同字段设置的字符集不同,会导致同一数据文件存在多种编码格式,如:字段a以utf8编码,b以gbk编码。)
如在命令中指定字符集,则按照指定的字符集导出到文件中。