MySQL字符集概述

一、字符集和校验规则
字符集 是一组符号和编码,校验规则(collation)是在字符集内用于比较字符的一套规则,即字符集的排序规则。MySQL可以使用各种字符集和检验规则来组织字符。

MySQL服务器可以支持多种字符集,在同一台服务器,同一个数据库,甚至同一个表的不同字段都可以指定使用不同的字符集,相比oracle等其他数据库管理系统,在同一个数据库只能使用相同的字符集,MySQL明显存在更大的灵活性。

MySQL能够为我们做以下事情:

  • 利用各种字符集存储字符串。
  • 通过比较各种排序规则的字符串。
  • 混合使用不同的字符集和归类在同一服务器,同一数据库,甚至在同一个表的字符串。
  • 在任何级别启用字符集和归类的规范。

为了有效地使用这些功能,我们应该知道什么字符集和归类可用,如何更改默认设置,以及它们如何影响字符串操作符和函数的行为。

二、MySQL中的字符集和校对
1、查看mysql服务器支持的字符集

mysql> show character set;
mysql> select * from information_schema.character_sets;

2、查看字符集的校对规则

mysql> show collation;
mysql> show collation like 'utf8';
mysql> select * from information_schema.collations where collation_name like 'utf8%';

3、查看当前数据库的字符集

mysql> show variables like 'character%';

4、查看当前数据库的校对规则

mysql> show variables like 'collation%';

chara
名词解释1:

character_set_client:客户端请求数据的字符集
character_set_connection:客户机/服务器连接的字符集
character_set_database:默认数据库的字符集,无论默认数据库如何改变,都是这个字符集;如果没有默认数据库,
                       那就使用 character_set_server指定的字符集,这个变量建议由系统自己管理,不要人为定义。
character_set_filesystem:把os上文件名转化成此字符集,即把 character_set_client转换character_set_filesystem, 默认binary是不做任何转换的
character_set_results:结果集,返回给客户端的字符集
character_set_server:数据库服务器的默认字符集
character_set_system:系统字符集,这个值总是utf8,不需要设置。这个字符集用于数据库对象(如表和列)的名字,也用于存储在目录表中的函数的名字。

名词解释2:

collation_connection:当前连接的字符集。
collation_database:当前日期的默认校对。每次用USE语句来“跳转”到另一个数据库的时候,这个变量的值就会改变。如果没有当前数据库,这个变量的值就是collation_server变量的值。
collation_server:服务器的默认校对。

排序方式的命名规则为:字符集名字语言后缀,其中各个典型后缀的含义如下:

  • _ci:不区分大小写的排序方式
  • _cs:区分大小写的排序方式
  • _bin:二进制排序方式,大小比较将根据字符编码

参考:归类命名约定

三、MySQL字符集和校对规则的设置
1、概述
MySQL字符集设置分为两类:

  • 创建对象的默认值。
  • 控制server和client端交互通信的配置。

1.1、控制server和client端交互通信的配置。
字符集和校对规则有4个级别的默认设置:

  • 服务器级别;
  • 数据库级别;
  • 表级别、列级别;
  • 连接级别。

较低级别的设置会集成较高级别的设置。

一个通用的规则:先为服务器或者数据库选择一个合理的字符集,然后根据不同的实际情况,让某个列选择自己的字符集。

1.2、控制server和client端交互通信的配置
大部分MySQL客户端都不具备同时支持多种字符集的能力,每次都只能使用一种字符集。

客户和服务器之间的字符集转换工作是由如下几个MySQL系统变量控制:

  • character_set_server:mysql server默认字符集。
  • character_set_database:数据库默认字符集。
  • character_set_client:MySQL server假定客户端发送的查询使用的字符集。
  • character_set_connection:MySQL Server接收客户端发布的查询请求后,将其转换为character_set_connection变量指定的字符集。
  • character_set_results:mysql server把结果集和错误信息转换为character_set_results指定的字符集,并发送给客户端。
  • character_set_system:系统元数据(字段名等)字符集

还有以collation_开头的同上面对应的变量,用来描述字符序。

注意事项:
- my.cnf中的default_character_set设置只影响mysql命令连接服务器时的连接字符集,不会对使用libmysqlclient库的应用程序产生任何作用!
- 对字段进行的SQL函数操作通常都是以内部操作字符集进行的,不受连接字符集设置的影响。
- SQL语句中的裸字符串会受到连接字符集或introducer设置的影响,对于比较之类的操作可能产生完全不同的结果,需要小心!

1.3、默认情况下字符集选择规则
(1).编译MySQL 时,指定了一个默认的字符集,这个字符集是 latin1;
(2).安装MySQL 时,可以在配置文件 (my.cnf) 中指定一个默认的的字符集,如果没指定,这个值继承自编译时指定的;
(3).启动mysqld 时,可以在命令行参数中指定一个默认的的字符集,如果没指定,这个值继承自配置文件中的配置,此时character_set_server被设定为这个默认的字符集;
(4).当创建一个新的数据库时,除非明确指定,这个数据库的字符集被缺省设定为character_set_server;
(5).当选定了一个数据库时,character_set_database被设定为这个数据库默认的字符集;
(6).在这个数据库里创建一张表时,表默认的字符集被设定为character_set_database,也就是这个数据库默认的字符集;
(7).当在表内设置一栏时,除非明确指定,否则此栏缺省的字符集就是表默认的字符集;

2、字符集设置
2.1、为列分配字符集
属于同一个表的不同列可以有不同的字符集,如果没有为一个列显示的定义字符集就使用默认字符集。创建一个表的时候,若显示的为列指定字符集,则字符集作为数据类型选项包含在其中,要放在数据类型后面及空指定和主键前面。
例如:

create table column_charset (
  c1 char(10) character set utf8 not null,
  c2 char(10) char set utf8,
  c3 varchar (10) charset utf8,
  c4 varchar (10)
) engine = innodb ;

注:character set可以简写为char set和charset
查看:

mysql> show create table column_charset\G;

2.2、为表分配字符集

create table table_charset(
    c1 varchar(10),
    c2 varchar(10)
)engine=innodb default charset=utf8;

2.3、为数据库指定字符集
创建的每个数据库都有一个默认字符集,如果没有指定,就用latin1。

create database hellodb charset=utf8;

2.4、为列分配校对规则
每个列都应该有一个校对,如果没有显示指定,MySQL就使用属于该字符集的默认校对。如果指定了一个字符集和一个校对,字符集应该放在前面。

create table column_collate(
    c1 varchar(10) charset utf8 collate utf8_romanian_ci not null,
    c2 varchar(10) charset utf8 collate utf8_spanish_ci
)engine=innodb;

查看表的校验规则信息:

mysql> select table_name,column_name,collation_name
       from information_schema.columns
       where table_name='column_collate';

注:字符集和校对在处理字符表达式的过程中扮演着重要角色。我们不能比较两个属于不同校对的不同字符值。

2.4、为表指定校对规则

create table table_collate(
    c1 varchar(10),
    c2 varchar(10)
)engine=innodb default charset utf8 collate utf8_romanian_ci;

检查表的校对规则:

mysql> select table_name,column_name,collation_name 
       from information_schema.columns where table_name='table_collate';

2.6、为数据库指定校对规则

create database hellodb default charset utf8 collate utf8_romanian_ci;

查看数据库定义语句:

mysql> show create database hellodb\G;

三、MySQL数据库中字符集转换流程
1、MySQL Server收到请求时将请求数据从character_set_client转换为character_set_connection;
2、进行内部操作前将请求数据从character_set_connection转换为内部操作字符集,其确定方法如下:

使用每个数据字段的CHARACTER SET设定值;
若上述值不存在,则使用对应数据表的DEFAULT CHARACTER SET设定值(MySQL扩展,非SQL标准);
若上述值不存在,则使用对应数据库的DEFAULT CHARACTER SET设定值;
若上述值不存在,则使用character_set_server设定值。

3、将操作结果从内部操作字符集转换为character_set_results。
下图源自于《高性能MySQL》中关于字符集转换的图解:
高性能MySQL

四、MySQL数据库乱码原因解析及案例
1、产生乱码的根本原因

  • 客户端没有正确地设置client字符集,导致原先的SQL语句被转换成connection所指字符集,而这种转换,是会丢失信息的,如果client是utf8格式,那么如果转换成gb2312格式,这其中必定会丢失信息,反之则不会丢失。一定要保证connection的字符集大于client字符集才能保证转换不丢失信息。
  • 数据库字体没有设置正确,如果数据库字体设置不正确,那么connection字符集转换成database字符集照样丢失编码,原因跟上面一样。

2、乱码或数据丢失
character_set_client:我们要告诉服务器,我给你发送的数据是什么编码?
character_set_connection:告诉字符集转换器,转换成什么编码?
character_set_results:查询的结果用什么编码?

3、 乱码终极解决方案

  • 首先要明确你的客户端时候何种编码格式,这是最重要的(IE6一般用utf8,命令行一般是gbk,一般程序是gb2312)
  • 确保你的数据库使用utf8格式,很简单,所有编码通吃。
  • 一定要保证connection字符集大于等于client字符集,不然就会信息丢失,比如: latin1 < gb2312 < gbk < utf8,若设置set character_set_client = gb2312,那么至少connection的字符集要大于等于gb2312,否则就会丢失信息。

以上三步做正确的话,那么所有中文都被正确地转换成utf8格式存储进了数据库,为了适应不同的浏览器,不同的客户端,你可以修改character_set_results来以不同的编码显示中文字体,由于utf8是大方向,因此web应用是我还是倾向于使用utf8格式显示中文的。

参考:
字符集,归类,统一
MySQL字符集详解

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值