1、mysql字符集
（1）mysql字符集
字符集就是一套文字符号及编码，比较规则的集合。mysql的字符及包括字符集（CHARACTER）和校对规则（COLLATION）
MySQL字符集支持在基于InnoDB, MyISAM, Memory三个存储引擎的MySQL实例、数据库、表、列四个级别上设置，同时MySQL提供了多种字符集和排序规则，其中字符集设置、数据存储以及客户端与MySQL实例的交互相关，排序规则和字符串的对比规则相关。查看当前MySQL支持的字符集的方式有两种，一种是通过查看information_schema.character_set系统表，一种是通过命令show character set查看。
（2）mysql常用字符集
1）Latin字符集
又称Latin-1或“西欧语言”，是国际标准化组织内ISO/IEC 8859的第一个8位字符集。它以ASCII为基础，此字符集支持部分于欧洲使用的语言。也是mysql5.7中默认字符集。
# 创建基于latin字符集的表并插入中文
mysql> CREATE TABLE test01(name varchar(100) DEFAULT NULL,name2 varchar(100) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;
# 将客户端的字符集也设置为latin1,否则不能插入中文
mysql> set names 'latin1';
mysql> insert into test01 values('china','中国');
mysql> select * from test01;
+-------+-------+
| name | name2 |
+-------+-------+
| china | 中国 |
+-------+-------+Latin1是一种很常见的字符集，这种字符集是单字节编码，向下兼容ASCII，Latin1覆盖了所有的单字节，因此，可以将任意字符串保存在latin1字符集中，而不用担心有内容不符合latin1的编码规范而被抛弃。
mysql使用者经常利用Latin1的这种全覆盖特性，将其它类型的字符串，gbk,utf8,big5等，保存在latin1列中。保存的过程中没有数据丢失，只要原样取出来，便又是合法的gbk/utf8/big字符串。如果将gbk字符串保存在utf8列中，则gbk字符串中那些不符合utf8编码格式的内容，会被抛弃，保存的内容无法原样取出，数据实际上遭到了破坏。
# 将客户端的字符串该为utf8时新插入的数据为utf8则会遭到破坏
mysql> insert ignore into test01(name,name2) values('china1','中国一');
mysql> select * from test01;
+--------+---------------+
| name | name2 |
+--------+---------------+
| china | ä¸å›½ |
| china1 | ??? |
+--------+---------------+2）Unicode字符集
Unicode（Universal Code）是一种在计算机上使用的字符编码。Unicode 是为了解决传统的字符编码方案的局限而产生的，它为每种语言中的每个字符设定了统一并且唯一的二进制编码，以满足跨语言、跨平台进行文本转换、处理的要求。Unicode存在不同的编码方案，包括Utf-8，Utf-16和Utf-32。Mysql支持的unicode的字符集主要有以下几种：
| character set | Supported characters | Required storage per character |
| Utf8 | BMP only | 1、2、3 bytes |
| Ucs2 | BMP only | 2bytes |
| Utf8mb4 | BMP and supplementary | 1、2、3、4bytes |
| Utf18 | BMP and supplementary | 2、4bytes |
| Utf16le | BMP and supplementary | 2、4bytes |
| Utf32 | BMP and supplementary | 4bytes |
BMP(BMP-Basic Multilingual Plane)：基本多语言范围。如果将BMP之外的扩展字符转换成utf8等仅支持BMP的字符集时，不识别的字符会转化成？号；除UTF8外，客户端字符集不支持设置为其他的Unicode字符集。
UTF8字符集：UTF8(Unicode Transformation Format with 8-bit units)是一种存放Unicode数据的编码规则。各类字符占用的大小如下：
基础拉丁字母，数字，标点符号会占用一个字节
扩展的拉丁字符，希腊语，斯拉夫语，阿拉伯语等会占用两个字节
韩语，中文，日语字符会占用3个或4个字节
在MySQL中，UTF8字符集不支持扩展字符，且仅占用最多3个字节
3）国家字符集
标准SQL中会有两种预定义的字符集数据类型，分别是nchar和nvarchar。 在MySQL中这种预定义的字符集就是UTF8。对字符串来说，可以使用N'字符串'的方式表明使用国家字符集。
mysql> create table test03(name nchar(10),name2 nvarchar(10),name3 varchar(10));
mysql> show create table test03;
+--------+----------------------------------------------------------+
| Table | Create Table
+--------+----------------------------------------------------------+
| test03 | CREATE TABLE `test03` (
`name` char(10) CHARACTER SET utf8 DEFAULT NULL,
`name2` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
`name3` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+-----------------------------------------------------------+4）中文字符集和排序规则
| 字符集 | 描述 | 排序规则 |
| big5 | Big5 繁体中文 | big5_bin、big5_chinese_ci (default) |
| gb2312 | GB2312 简体中文 | gb2312_bin、gb2312_chinese_ci (default) |
| gbk | GBK简体中文 | gbk_bin、gbk_chinese_ci (default) |
| gb18030 | 中国国家标准GB18030) | gb18030_bin：二进制排序规则 gb18030_chinese_ci (default)：若是字母就按照字母排序，如果是汉字则按照拼音排序 gb18030_unicode_520_c：Unicode排序规则 |
mysql> create table test04(name01 varchar(10) character set utf8 not null,name02 varchar(10) character set gbk,name03 varchar(10) character set gb2312,name04 varchar(10)) engine=InnoDB charset=latin1;
mysql> insert ignore into test04 values('中国','中国','中国','中国');
# 由于连接字符集和客户端字符集都为utf8导致latin字符集插入中文时数据遭到破坏
mysql> select * from test04;
+--------+--------+--------+--------+
| name01 | name02 | name03 | name04 |
+--------+--------+--------+--------+
| 中国 | 中国 | 中国 | ?? |
+--------+--------+--------+--------+（3）查看mysql中字符集及排序规则
# 查看当前数据库字符集
mysql> show variables like '%charac%';
+--------------------------+-----------------------------------------+
| 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/local/mysql-5.7.32/share/charsets/ |
+--------------------------+-----------------------------------------+各字符集的含义如下：
character_set_client：连接客户端发送的语句的字符集
character_set_connection：客户端与服务交互时的字符集
character_set_database：当前所在库的字符集
character_set_results：参数决定了语句的执行结果以什么字符集返回给客户端
character_set_server：当前登录的数据库实例的字符集
character_set_system：数据库所在系统的字符集
# 查看当前数据库排序规则
mysql> show variables like '%collation%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | utf8_general_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+2、mysql字符集排序规则
（1）mysql字符集排序规则
每个指定的字符集都会有一个或多个支持的排序规则，可以通过两种方式查看，一种是查看information_schema.collations表，另一种是通过show collation命令查看；不同的字符集不可能有相同的排序规则，每个字符集都会有一个默认的排序规则。
（2）排序规则的命名规则
排序规则的命令通常是以对应的字符集的名字为开头，并以自己的特定属性结尾，当排序规则特指某种语言时，则中间的部分就为这种语言的名字，如” utf8_turkish_ci”代表UTF8字符集中的土耳其语排序中对大小写不敏感
| 结尾符 | 代表含义 |
| _ai | 口音不敏感 |
| _as | 口音敏感 |
| _ci | 大小写不敏感 |
| _cs | 大小写敏感 |
| _bin | 二进制 |
当排序规则名字中没有指定_as或者_ai时，则是否口音敏感是由_ci或者_cs决定。对Unicode的字符集来说，对应的排序规则也可能会包含unicode排序算法的版本号，如果没有这个版本号显示则表示是基于4.0.0这个版本。
3、字符集及排序规则的设置
（1）实例级别字符集和排序规则的设置
实例级别的字符集可以在实例启动的时候指定或在默认的配置文件中指定，默认的字符集是latin1，当仅指定了字符集没有指定排序规则时，则默认的排序规则是该字符集的默认排序规则。
设置参数：
character-set-server：指定字符集
collation-server：指定排序规则
# 启动mysql实例时设置字符集
]# /etc/init.d/mysqld start --character-set-server=latin1 --collation-server=latin1_swedish_ci
# 在mysql配置文件中设置字符集，在老版本中使用” Default-character-set”参数
[mysqld]
character-set-server=utf8
# 修改实例级别的默认字符集，对root用户无效
mysql> show variables like '%character_set%';（2）库级别字符集和排序规则的设置
数据库级别的字符集和排序规则，可以通过create database和alter database语句来指定和修改。
# 创建一个数据库并指定排序规则
mysql> create database test01 character set utf8;
# 修改创建的库的排序规则
mysql> alter database test01 character set latin1;
mysql> show create database test01;
+----------+-------------------------------------------------+
| Database | Create Database
+----------+-------------------------------------------------------+
| test01 | CREATE DATABASE `test01` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+--------------------------------------------------------+（3）表级别字符集和排序规则的设置
每个表都有自己的字符集和排序规则，可以通过create table和alter table语句指定或修改表的字符集和排序规则；如果没有指定排序规则时会使用字符集默认的排序规则，如果没有指定字符集时则使用排序规则对应的字符集。当两个属性都没有指定时，会使用数据库级别的字符集和排序规则。
# 创建表时指定排序规则和字符集
mysql> create table test01(id int,name varchar(100)) character set utf8 collate utf8_general_ci;
# 修改表的字符集，表的字符集不会改变已经存在的字段的字符集和字段里的数据
mysql> alter table test01 character set latin1;
mysql> show create table test01;
+--------+--------------------------------------------------+
| Table | Create Table
+--------+---------------------------------------------------+
| test01 | CREATE TABLE `test01` (
`id` int(11) DEFAULT NULL,
`name` varchar(100) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+---------------------------------------------------------+（4）列级别字符集和排序规则的设置
每个字符串字段都有自己的字符集和排序规则，可以在create table和alter table指定和修改字段的字符集和排序规则。如果通过alter table语句修改某一列的字符集时，MySQL会试图转换其中已有的数据，这样的转换有可能会导致数据丢失。
# 创建一张表并指定某些字段的排序规则
mysql> CREATE TABLE test02 (id int,name VARCHAR(5) CHARACTER SET latin1 COLLATE latin1_german1_ci);
# 修改列级别字符集
mysql> alter table test02 modify name varchar(12) character set utf8;
# 当修改为范围更小的级别的字符集时会报错
mysql> alter table test02 modify name varchar(12) character set greek;
ERROR 1366 (HY000): Incorrect string value: '\xC3\xA4\xC2\xB8\xC2\xAD...' for column 'name' at row 1（5）连接级字符集和排序规则
每个数据库客户端连接都有自己的字符集和排序规则属性，客户端发送的语句的字符集是由character_set_client决定，而与服务端交互时会根据character_set_connection和collation_connection两个参数将接收到的语句转化。当涉及到显示字符串的比较时，由collation_connection参数决定，而当比较的是字段里的字符串时则根据字段本身的排序规则决定。
客户端可以很方便的调整字符集和排序规则，当使用”set names”语句设置字符集时相当于同时设置了character_set_client、character_set_connection、character_set_results的值。
# 设置客户端的字符集的排序规则
mysql> set names latin1;也可以通过参数 --init_connect="SET NAMES 'utf8'" 来让每个客户端连接都自动设置字符集，但缺点是对拥有super权限的用户不生效；init_connect表示服务器为每个连接的客户端执行的字符串，要想指定多个语句，用分号间隔开。
# 通过命令设置客户端的排序规则的字符集
mysql> SET GLOBAL init_connect='SET AUTOCOMMIT=0;set names utf8';
#通过配置文件设置默认的客户端的字符集和排序规则
[mysqld]
init_connect='SET AUTOCOMMIT=0;set names utf8’（6）排序规则在sql语句中使用
在SQL语句中指定排序规则可以覆盖之前定义的默认排序规则
mysql> select sname,gender from student order by gender collate latin1_german2_ci;
+--------+--------+
| sname | gender |
+--------+--------+
| Andy | 0 |
| Bob | 0 |
# 使用函数时定义排序规则
mysql> select max(score collate latin1_german2_ci) from score;
+--------------------------------------+
| max(score collate latin1_german2_ci) |
+--------------------------------------+
| 95 |
+--------------------------------------+（7）字符集和排序规则使用注意事项
当应用程序使用MySQL默认的字符集和排序规则时，则应用程序端不需要特别的设置，而当不是使用默认属性时则需要特别设置。
4、mysql数据库字符集的转化
1）MySQL Server收到请求时将请求数据从character_set_client转换为character_set_connection；
2）进行内部操作前将请求数据从character_set_connection转换为内部操作字符集
3）将操作结果从内部操作字符集转换为character_set_results
5、mysql数据库字符集的乱码及解决方法
（1）字符集乱码原因
1）客户机没有正确地设置client字符集，导致原先的SQL语句被转换成connection所指字符集，而这种转换，是会丢失信息的
2）数据库字体没有设置正确，那么connection字符集转换成database字符集照样丢失编码
（2）解决字符乱码方案
1）首先要明确你的客户端时候何种编码格式，这是最重要的（IE6一般用utf8，命令行一般是gbk，一般程序是gb2312)
2）确保数据库使用utf8格式，很简单，所有编码通吃。
3）要保证connection字符集大于等于client字符集，不然就会信息丢失
2805
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
