MySQL字符集

MySQL字符集

1、基本概念

字符(Character)：        是指人类语言中最小的表义符号。例如'A'、'B'等；

编码（Encoding）：    给定一系列字符，对每个字符赋予一个数值，用数值来代表对应的字符，这一数值就是字符的编码

字符集(Character Set):给定一系列字符并赋予对应的编码后，所有这些字符和编码对组成的集合就是字符集

字符序(Collation) ：     指在同一字符集内字符之间的比较规则

一个字符集可以对应多种字符序；

以字符序对应的字符集名称开头：

以_ci(表示大小写不敏感)，例如：在字符序``utf8_general_ci''下，字符``a''和``A''是等价的；

_cs(表示大小写敏感)或_bin(表示按编码值比较)结尾。

有哪些常用的字符集

mysql中字符集 utf8 和utf8mb4 有什么区别?

这个utf8mb4 是mysql5.5才有的字符了，可以简单的理解 utf8mb4 是目前最大的一个字符编码,支持任意文字.

为什么会有UTF8MB4？

MySQL支持的utf8编码，最大字符长度为 3 字节，如果遇到 4 字节的字符就会出现错误了。

三个字节的 UTF-8 最大能编码的 Unicode 字符是 0xFFFF，也就是 Unicode 中的基本多文平面（BMP）。

也就是说，任何不在基本多文平面的 Unicode字符，最常见的就是Emoji 表情（Emoji 是一种特殊的 Unicode 编码，常见于 ios 和 android 手机上），和一些不常用的汉字，以及任何新增的 Unicode 字符等等，都无法使用MySQL原有的 utf8 字符集存储。

2、字符集相关变量

character_set_client：客户端来源数据使用的字符集

character_set_connection：连接层字符集

character_set_results：查询结果字符集

内部字符集：

character_set_server：默认的内部操作字符集

character_set_database：当前选中数据库的默认字符集

character_set_system：系统元数据(字段名等)字符集

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

内部操作字符集优先级关系：

服务器字符集 server < 数据库字符集database < 表字符集 < 列字符集，如果没有设置，默认从上一级继承。

3、转换过程

MySQL Server收到请求时将请求数据从character_set_client转换为character_set_connection，

进行内部操作前将请求数据从character_set_connection转换为内部操作字符集，

使用每个数据字段的CHARACTER SET设定值；

若上述值不存在，则使用对应数据表的DEFAULT CHARACTER SET设定值(MySQL扩展，非SQL标准)；

若上述值不存在，则使用对应数据库的DEFAULT CHARACTER SET设定值；

若上述值不存在，则使用character_set_server设定值。

MySQL字符集综合解惑以及最佳实践

mysql> insert into t20(name) values ('你好');

往t20表的name 列上插入数据‘你好’

客户端‘你好’被进行了编码，假设编成了‘9190,9193’

Mysql接到SQL语句之后，发现有字符

1、Insert

2、T20(name) varchar列有字符，就需要编码

3、‘9190,9193’

客户端通过character_set_client告诉mysql用的什么字符集编码，character_set_client与character_set_connection编码方式不同时，会进行转换。

原则：存数据的时候不发生转换或转换只发生一次，可以发生在1或2

===============================

insert into t1 values ('你'); /select * from t1;

1、正存正取（完美）

2、正存错取（小事）

只是character_set_results的值没有正确显示客户端的字符集，有可能会出现乱码情况，但是数据的存储是正确的

3、错存正取（人命）

IMG_256

错存正取的过程分析：

客户端的字符集是gbk，character_set_client=character_set_connection=character_set_results 是 dec8，假定'你'在gbk中的编码是001，当在客户端插入数据'你'时，客户端会把'你' 编成001，001会传入数据库，当数据库接收信息时会询问001是用什么编的，character_set_client 告诉数据库是用 dec8编的，数据库会把001译成dec8对应的字符，假设为'a'，然后把 a 编成utf8的编码，假定为010，此时存入数据库中的是 'a'，取数据时询问是用什么取，character_set_results告诉数据库用 dec8取，数据库将 utf8的010 --> a -->dec8 的001，然后在客户端会显示gbk中001对应的'你'

===================

校对规则(collation)：是一组规则，负责决定某一字符集内的字符进行比较和排序的结果

每个字符集有一个或多个校对规则，并且每个校对规则只能属于一个字符集

每个字符集有一个默认(default)校对规则。 utf8的默认校对规则是utf8_general_ci

校对规则的命名约定：以字符集名开始，中间包括一个语言名，最后以后缀_ci、_cs或bin结束

*_bin:  binary case sensitive collation 区分大小写,直接比较字符的编码

*_cs: case sensitive collation     区分大小写

*_ci: case insensitive collation  不区分大小写

Utf8

大小写是否敏感的这么一个关于比较和排序的参数。

Mysql默认都是大小写不敏感，这会影响排序和where比较，若要区分大小写，需要在建库，建表，建列的时候特殊指定一下。

mysql> create table t1(id int,name varchar(20));

mysql> insert into t1 values(1,'a');  //插入A、a、B、b四个值

用where取的时候不会区分大小写

mysql> create table t2(id int,name varchar(20)) CHARACTER SET gbk CollATE=gbk_bin;   //指定区分大小写

用where的时候就区分大小写了

======

create table t1(id int,name varchar(5)) character set utf8 collate=utf8_general_ci;

IMG_256

create table t2(id int,name varchar(5)) character set utf8 collate=utf8_bin;

IMG_256

======

查看数据库支持的所有校对规则

show collation;

查看当前字符集和校对规则设置

show variables like 'collation_%';

IMG_256

来源： http://www.111cn.net/database/mysql/114544.htm