MySQL字符集与排序规则总结
字符集与排序规则概念
在数据库当中都有字符集和排序规则的概念，很多开发⼈员甚⾄包括有些DBA都会将这个混淆，当然这个情况也有⼀些情有可原的原因。⼀来两者本来就是相辅相成，相互依赖关联；另外⼀⽅⾯，有些数据库并没有清晰的区分开两者。例如，SQL Server中字符集和排序规则就是合在⼀起的，创建⼀个新的数据库，只有⼀个Collation给你选择，并没有字符集选项概念，实际上你在选择⼀个Collatin时，就选定了数据库的字符集和排序规则，例
如Chinese_PRC_CI_AS。在MySQL中，字符集和排序规则是区分开来的，你需要单独设置字符集和排序规则。当然MySQL字符集和排序规则也是相关联的。除⾮特殊需求，只要设置其⼀即可。设置字符集，即设置了默认的排序规则。
我们先来搞清楚字符、字符集与字符编码的概念。相信很多⼈都在这些概念上犯过迷糊。什么是字符呢？什么是字符集呢，什么有是字符编码呢？
字符（Charcter）是⽂字与符号的总称，包括⽂字、图形符号、数学符号等。26个英⽂字母属于字符，每个汉字也属于⼀个字符。
字符集是⼀组抽象的字符（Charcter）组合的集合。举⼀个例⼦，所有的汉字就算⼀个“字符集合”，所有的英语字母也算⼀个“字符集合”。注意，我这⾥说它们是字符集合，⽽且还有双引号。是因为字符集并不简单的是字符的集合，准确概述来说，字符集是⼀套符号和编码的规则。字符集需要以某种字符编码⽅式来表⽰、存储字符。我们知道，计算机内部，所有信息最终都是⼀个⼆进制值。每⼀个⼆进制位（bit）有0和1两种状态。⽽如果⽤不同的0和1组合表⽰不同的字符就是编码。反汇编和逆向工程
关于字符编码，我们知道字符最终是以⼆进制形式存储在磁盘的，这也是为什么要有字符编码的原因，因为计算机最终都要以⼆进制形式存储，那么编码规则就是⽤什么样的⼆进制来代表这个字符。例如，我们所熟知的ASCII码表中，01000011这个⼆进制对应的⼗进制是67，它代表的就是英语字母C。准确概述来说，字符编码⽅式是⽤⼀个或多个字节的⼆进制形式表⽰字符集中的⼀个字符。每种字符集都有⾃⼰特有的编码⽅式，因此同⼀个字符，在不同字符集的编码⽅式下，可能会产⽣不同的⼆进制形式。
另外，字符集合只是指定了⼀个集合中有哪些字符，⽽字符编码，是为这个集合中所有字符定义相关编号，⽽字符集（注意与字符集合的区别）是字符和集合与编码规则的混合体，这也是有时候编码⽅案代表字符集的原因。
说了这么多，相信有些⼈不能区分UTF8 与 Unicode，例如我们连接MySQL的字符串，这⾥⾯就会包含字符编码与字符集。
<string value="jdbc:mysql://x/TEST?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull"/>那么Unicode与UTF-8 、UTF-16 、UTF-32是什么关系？
Unicode（统⼀码、万国码、单⼀码）是⼀种字符集，Unicode是国际组织制定的可以容纳世界上所有⽂字和符号的字符编码⽅案。Unicode⽤数字0-
0x10FFFF来映射这些字符，最多可以容纳1114112个字符，或者说有1114112个码位。UTF-8、UTF-16、UTF-32都是将数字转换到程序数据的编码⽅
案。.在Unicode中：汉字“中”对应的数字是20013。我们可以⽤：UTF-8、UTF-16、UTF-32表⽰这个数字，将数字20013存储在计算机中。UTF-8对应
是：E4B8AD,UTF-16对应是：FEFF4E2D,UTF-32对应是：0000FEFF00004E2D。简单来说，UTF-8、UTF-16、UTF-32是Unicode码⼀种实现形式，都是属于Unicode编码。
在MySQL中，常见的⼏个字符集有latin1、GBK、GB2312、BIG5、UTF8、UTF8MB4、UTF16、UTF32等。
⽽MySQl的排序规则（collation），⼀般指对字符集中字符串之间的⽐较、排序制定的规则， MySLQ排序规则特征：
o两个不同的字符集不能有相同的校对规则；
o每个字符集有⼀个默认校对规则；
o存在校对规则命名约定：以其相关的字符集名开始，中间包括⼀个语⾔名，并且以_ci（⼤⼩写不敏感）、_cs（⼤⼩写敏感）或_bin（⼆元）结束。
其实对于排序规则的细节问题，我们关注较少，反⽽对排序规则中是否涉及⼤⼩写敏感关注较多。 例如，系统使⽤utf8字符集，若使⽤utf8_bin校对规则执⾏SQL查询时区分⼤⼩写，使⽤utf8_general_ci不区分⼤⼩写(默认的utf8字符集对应的校对规则是utf8_general_ci)。
MySQL字符集的分类
MySQL数据库的相关字符集设置相当灵活和复杂（灵活性太⾼，就会引起复杂性），要搞清、弄懂这些概念还真需要花⼀点时间。这个也是很多⼈遭遇中⽂乱码的真正原因。具体来说，MySQL的字符集有分层的、灵活的特点。如果没有指定字段的字符集，那么就默认使⽤当前表的字符集，如果没有指定当前表的字符集，那么就会默认使⽤当前数据库的字符集.... 要了解不同字符集的分类，我们先从MySQL的系统变量（字符集相关的系统变量）开始
mysql> show variables like'character_set%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
character_set_client 客户端数据使⽤的字符集
MySQL Client发送给mysqld的语句或数据使⽤字符集。
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. (Many clients support a --default-character-set option to enable this character set to be specified explicitly. See also Section 10.1.4, “Connection Character Sets and Collations”.) The global value of the variable is used to set the session value in cases when the client-requested value is unknown or not available, or the server is configured to ignore client requests:
The client is from a version of MySQL older than MySQL 4.1, and thus does not request a character set.
jquery定义数组The client requests a character set not known to the server. For example, a Japanese-enabled client requests sjis when connecting to a server not configured with sjis support.
mysqld was started with the --skip-character-set-client-handshake option, which causes it to ignore client character set configuration. This reproduces MySQL 4.0 behavior and is useful should you wish to upgrade the server without upgrading all the clients.
ucs2, utf16, utf16le, and utf32 cannot be used as a client character set, which means that they also do not work for SET NAMES or SET CHARACTER SET.
character_set_connection连接层字符集
其实很多⼈对这个字符集⼀脸懵逼，这个字符集与character_set_client有啥区别呢？这个字符集⽤于没有introducer修饰的字符串和数字到字符串的转换。
由introducer修饰的⽂本字符串在请求过程中不经过多余的转码，直接转换为内部字符集处理。
The character set used for literals that do not have a character set introducer and for number-to-string conversion. For information about introducers, seeSection 10.1.3.8, “Character Set Introducers”.
character_set_database数据库字符集
MySQL可以给实例下不同数据库单独设置各⾃的字符集。这个跟SQL Server是类似的。
特斯拉事件对中国的影响The character set used by the default database. The server sets this variable whenever the default database changes. If there is no default database, the variable has the same value as character_set_server.
character_set_filesystem
The file system character set. This variable is used to interpret string literals that refer to file names, such as in the LOAD DATA INFILE and SELECT ... INTO OUTFILE statements and the LOAD_FILE() function. Such file names are converted from character_set_client to character_set_filesystem before the file opening attempt occurs. The default value is binary, which means that no conversion occurs. For systems on which multibyte file names are permitted, a different value may be more appropriate. For example, if the system represents file names using UTF-8, set character_set_filesystem to 'utf8'.
basic语言代码⽂件系统字符集。该变量⽤于解释引⽤⽂件名的字符串⽂字，例如在LOAD DATA INFILE和SELECT ... INTO OUTFILE语句和LOAD_FILE（）函数中。在⽂件打开尝试发⽣之前，这样的⽂件名将从character_set_client转换为character_set_filesystem。默认值为⼆进制，这意味着不会发⽣转换。对于允许多字节⽂件名的系统，不同的值可能更合适。例如，如果系统使⽤UTF-8表⽰⽂件名，则将character_set_filesystem设置为“utf8”。mysql语句分类
character_set_results查询结果字符集
mysqld 在返回查询结果集或者错误信息到客户端时，使⽤的编码字符集
The character set used for returning query results such as result sets or error messages to the client.
character_set_server 服务器字符集，默认的字符集。
服务器级别(实例级别) 的字符集。如果创建数据库时，不指定字符集，那么就会默认使⽤服务器的编码字符集。
The server's default character set.
character_set_system 系统元数据字符集
它是系统元数据(表名、字段名等)存储时使⽤的编码字符集，该字段和具体存储的数据⽆关。总是固定不变的UTF8字符集。
The character set used by the server for storing identifiers. The value is always utf8.
另外，之前的版本还有default-character-set，MySQL 5.5版本开始，移除了参数default_character_se
t 取⽽代之的是参数character_set_server。
character_set_client、character_set_connection、character_set_results这3个参数值是由客户端每次连接进来设置的，和服务器端没关系。MySQL会存在不同字符集的转换过程，
MySQL⽀持的字符集
MySQL不同版本⽀持的字符集有所不同，你可以使⽤命令show charset或show character set来查看当前MySQL版本⽀持的字符集。
mysql> show charset;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
+----------+-----------------------------+---------------------+--------+
40 rows in set (0.00 sec)
第⼀列表⽰字符集、第⼆列表⽰字符集描述、第三列表⽰默认排序规则、第四列表⽰字符集的⼀个字符占⽤的最⼤字节数。当然你也可以使⽤下⾯SQL语句查询，效果是⼀样的。
mysql> select * from information_schema.character_sets;
MySQL字符集的查看
查看MySQL当前字符集
可以使⽤show variables like '%character%'查看相关字符集。
mysql> show variables like'%character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
查看客户端使⽤的字符集
mysql> show variables like'%character_set_client%';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| character_set_client | utf8 |
+----------------------+-------+
1 row in set (0.00 sec)
查看连接层字符集
mysql> show variables like'character_set_connection'; +--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| character_set_connection | utf8 |
+--------------------------+-------+
1 row in set (0.00 sec)
查看MySQL查询结果字符集
mysql> show variables like'character_set_results';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| character_set_results | utf8 |
+-----------------------+-------+
1 row in set (0.01 sec)
查看MySQL服务器字符集
mysql> show variables like'character_set_server';
+----------------------+--------+
| Variable_name | Value |
fertilising+----------------------+--------+
| character_set_server | latin1 |
+----------------------+--------+
1 row in set (0.00 sec)
mysql> status;
查看MySQL数据库的字符集
mysql> use YourDB;
mysql> show variables like'character_set_database'
&
mysql> status;
注意：上⾯这些命令是查看当前数据库的字符集。
mysql>show create database dbname --dbname为你要查看的数据库。
mysql> show create database MyDB;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| MyDB | CREATE DATABASE `MyDB` /*!40100 DEFAULT CHARACTER SET utf8mb4*/ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
要查看当前MySQL实例下⾯所有数据库的字符集和排序规则，可以使⽤下⾯脚本
SELECT SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME
FROM INFORMATION_SCHEMA.SCHEMATA ;
查看MySQL表的字符集
⽅式1: show create table xxxx;
⽅式2: 查看INFORMATION_SCHEMA.TABLES下的TABLE_COLLATION，从⽽推断表的字符集
SELECT TABLE_SCHEMA, TABLE_NAME,TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES;
SELECT TABLE_SCHEMA, TABLE_NAME,TABLE_COLLATION
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME='TEST';
查看MySQL字段的字符集
如下所⽰，如果在创建表的时候已经指定了字段使⽤的字符集，那么show create table xxx 就能看到字段使⽤字符集，如果没有显⽰指定字段的字符集，show create table xxx 看不到其字符集，其实，这表⽰字段就会默认使⽤表的字符集。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论