《MySQL DBA修炼之道》——3.7 字符集和国际化支持

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 本节书摘来自华章出版社《MySQL DBA修炼之道》一书中的第3章,第3.7节,作者:陈晓勇,更多章节内容可以访问云栖社区“华章计算机”公众号查看字符集和国际化支持什么是字符集 字符集(character set)是一套符号和编码。

本节书摘来自华章出版社《MySQL DBA修炼之道》一书中的第3章,第3.7节,作者:陈晓勇,更多章节内容可以访问云栖社区“华章计算机”公众号查看

3.7 字符集和国际化支持

3.7.1 什么是字符集
字符集(character set)是一套符号和编码。校对规则(collation)是在字符集内用于比较字符的一套规则,即字符集的排序规则。
假设我们有一个字母表使用了4个字母:'A'、'B'、'a'、'b'。现在为每个字母赋予一个数值:'A'=0,'B'= 1,'a'= 2,'b'= 3,字母'A'是一个符号,数字0是'A'的编码,那么这4个字母和它们的编码组合在一起就是一个字符集。我们可以认为字符集是字符的二进制的编码方式,即二进制编码到一套符号的映射。
对于字符集,MySQL能够做如下这些事情。
使用多种字符集来存储字符串。
使用多种校对规则来比较字符串。
?在同一台服务器、同一个数据库甚至在同一个表中,使用不同的字符集或校对规则来混合字符串。
允许定义任何级别的字符集和校对规则。
可使用SHOW CHARACTER SET语句列出可用的字符集。
mysql>SHOW CHARACTER SET;
可使用SHOW COLLATION语句列出utf8字符集的校对规则。
mysql>SHOW COLLATION LIKE 'utf8%';
3.7.2 国际化支持
因为现存编码不能在多语言电脑环境中使用,而且字符数有局限。所以诞生了Unicode(统一码、万国码、国际码、单一码)。Unicode是计算机科学领域里的一项业界标准。它对世界上大部分的文字系统进行了整理、编码,使得电脑可以用更为简单的方式来呈现和处理文字。
一个字符的Unicode编码是确定的,但Unicode的实现方式不同于编码方式。在实际传输过程中,由于不同系统平台的设计不一定都是一致的,且出于节省空间的目的,对Unicode编码的实现方式也有所不同。Unicode的实现方式称为Unicode转换格式(Unicode Transformation Format,UTF)。这其中有一种UTF-8编码,它是一种变长编码,MySQL中经常使用的utf8字符集就是UTF-8 编码。UTF-8编码的思想是不同的Unicode字符采用变长字节序列编码:基本拉丁字母、数字和标点符号使用一个字节。大多数的欧洲和中东手写字母适合两个字节序列。韩语、中文和日本象形文字使用三个字节序列。
utf8是MySQL存储Unicode数据的一种可选方法,MySQL还有其他的存储Unicode数据的字符集,这里就不做额外介绍了。


9d204fef4ca19cf020736c187d3263161d0b5539

对于utf8,还需要了解这样两个概念:超集、子集。
有字符集A、B。如果B支持的所有字符A都支持,那么字符集A是字符集B的超集。如果A是B的超集,那么字符集B是字符集A的子集。比如,GBK字符集是GB2312字符集的超集,它们又都是ASCII字符集的超集。
3.7.3 字符集设置
字符集设置可以分为两类:一类是创建对象的默认值;另一类是控制server端和client端交互通信的配置。
1.创建对象的默认值
字符集和校对规则有4个级别的默认设置:服务器级、数据库级、表级和连接级。
使用如下语句列出可用的字符集。
mysql> SHOW CHARACTER SET;
列出可用的校对规则可使用如下语句。
mysql> SHOW COLLATION
更低级别的配置会继承更高级别的配置。例如,如果创建一个数据库,不指定字符集,那么它会继承服务器级的默认字符集。
对于生产环境的升级脚本,建议在表级别指定默认的字符集,以避免歧义或继承了错的数据库默认字符集。
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_result:MySQL Server把结果集和错误信息转换为character_set_result指定的字符集,并发送给客户端。
图3-13说明了字符集的转换过程。

2cde3a749f8368f6db15511b5c3750bc824ffe6b

图3-13来自《High Performance MySQL》一书。由图3-13可以知道,当一个客户端和数据库打交道时,客户端、连接、操作系统、数据库、输出结果都有自己的字符集设置,如果字符集不一致,那么就可能需要进行转换,一般情况下,目标字符集应确保是源字符集的超集,以确保转换正常,如果目标字符集不能容纳源字符集的编码或设置错了字符集,那么转换会导致乱码。
以下列举了一些常用的设置字符集的操作。
SET NAMES x
通过MySQL客户端导入数据时,在使用“mysql > source /path/imp_data.sql”命令的过程中有时可能会出现乱码,这时可能需要先运行SET NAMES x语句设置字符集。
SET NAMES x语句与下面这3个语句是等价的。
mysql> SET character_set_client = x;
mysql> SET character_set_connection = x;
mysql> SET character_set_results = x;
再来看看SET CHARACTER SET x语句,它等同于下面这3条语句。
mysql> SET character_set_client = x;
mysql> SET character_set_results = x;
mysql> SET collation_connection = @@collation_database;
有些客户端命令支持“--default-character-set”选项,此选项允许用户连接时设置字符集。它等同于以下这3条语句。
mysql> SET character_set_client = x;
mysql> SET character_set_connection = x;
mysql> SET character_set_results = x;
如果数据库服务器中有很多数据库使用不同的字符集,且有各种不同语系的客户端,很复杂,那么使用init-connect=SET NAMES binary是一种可以考虑的方式。这个指令的目的是让clinet与server交互的时候以as-is 模式(是什么就是什么,不做任何转换)来传送。
索引可用来排序,但如果指定了用其他的非默认排序规则,那么将不能利用索引进行排序,比如在下面的语句中:
EXPLAIN SELECT col_1,col_2 
  FROM table_name ORDER BY col_2 COLLATE utf8_bin  ```
col_2字段使用的是utf8字符集,且其上有索引,默认的utf8字符集的排序规则是utf8_general_ci,而上面的案例指定的是用utf8_bin进行排序,那么EXPLAIN输出可以看到有filesort(文件排序),即没有利用到索引进行排序。
同理,如果连接两张表使用的连接列不是一样的字符集,那么也不能利用索引,因为必须先执行转换工作,可用EXPLAIN EXTENDED先进行确认。
默认情况下,MySQL的字符集是latin1(ISO_8859_1)。latin1字符集是单字节编码,应用于英文系列,最多能表示的字符范围是0~255(编码范围是0x00~0xFF),其中0x00~0x7F之间和ASCII码完全一致,因此它是向下兼容ASCII的。latin1字符有限,如用来存储中文、日文、韩文、希伯来文等语言时往往会导致乱码,为了避免乱码,支持国际化,个人建议是生产环境都统一使用utf8字符集,除非你有特殊理由。
以下是关于在生产环境中使用utf8字符集的一些说明和注意事项。
1)为什么生产环境中建议使用utf8字符集?
主要是为了维护和开发都方便。大家都统一使用utf8字符集,将一劳永逸地避免各种乱码问题。一个数据库如果存在各种字符集,就会很容易出错,也会大大提高开发的难度。国际化支持也是使用utf8字符集的一个考虑。
当然,utf8字符集也有弊端,主要就是空间的消耗。比如,对于CHAR(10),将需要用到30个字节来存放。对于VARCHAR(10),则是按照字符串的长度来存储的,虽然不存在过多的磁盘空间消耗,但MySQL内部实现的一些数据结构,如临时表需要分配最大可能的长度,也可能导致内存大大增加。更多的空间还意味着更差的I/O性能。
有时,我们可能为了节省空间(如果空间真的是一个需要考虑的因素)而选择其他字符集(如用GBK存储汉字),对于大批量的机器,特定的服务选择特定的字符集,这种情况下所节省的空间也是很可观的,但对于一般的中小型公司,建议统一使用utf8,一劳永逸地解决乱码问题是更明智的选择。
2)如何判断多字节字符集的字符串长度?
LENGTH()返回值为字符串的长度,单位为字节。一个多字节字符算作多字节。
CHAR_LENGTH()返回值为字符串的长度,长度的单位为字符。一个多字节字符算作一个单字符。
例如:对于一个包含了5个二字节的字符集,LENGTH()返回值为10,而CHAR_LENGTH()的返回值为5。
3)有时创建索引的时候,可能会提示出错。
MySQL会假定每个字符有3个字节,由于索引长度有限制,那么创建索引的时候,可能会提示下面这样的错误。
``ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes``
这时需要明白,自己创建的是多字节字符集,字节数实际上超过1000了。
4)UTF-8 是可变长度的编码,使用1到4个字节来存储。但MySQL 5.1及以前的版
本,对UTF-8的支持并不彻底,它的utf8只是3字节字符集,有些文字符号是不能存储的,如 emoji表情。
MySQL5.5增加了字符集utf8mb4(4-Byte UTF-8 Unicode Encoding),可以存储一些MySQL utf8不能存储的字符,需要留意的是,设置了utf8mb4字符集后,需要重启MySQL Server才能生效。
<div style="text-align: center">
 <img src="https://yqfile.alicdn.com/3a5764a89229d770e608a20ee0affae6381641cc.png" >
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
4月前
|
SQL 存储 关系型数据库
【MySQL系列】SQL语句入门(创建删除操作)、字符集和数据类型详解
哈喽,大家好💓,在上一篇博客中,大致讲解了数据库的相关知识,它的作用、定义、分类等等。从本篇博客开始,将详细讲解关系型数据库MySQL的操作与使用,以及SQL语句的讲解。
|
6月前
|
关系型数据库 MySQL 数据库
Mysql 创建数据库字符集与排序规则
Mysql 创建数据库字符集与排序规则
159 0
|
24天前
|
存储 关系型数据库 MySQL
从零开始学Mysql - 字符集和编码(下)
从零开始学Mysql - 字符集和编码(下)
23 0
|
24天前
|
存储 SQL 关系型数据库
从零开始学Mysql - 字符集和编码(上)
从零开始学Mysql - 字符集和编码(上)
57 0
|
5月前
|
存储 关系型数据库 MySQL
3.3 【MySQL】字符集和比较规则的应用
3.3 【MySQL】字符集和比较规则的应用
36 0
|
2月前
|
关系型数据库 MySQL 数据库
Mysql 创建数据库字符集与排序规则
Mysql 创建数据库字符集与排序规则
46 2
|
2月前
|
关系型数据库 MySQL Linux
Linux下修改MySQL字符集等配置
Linux下修改MySQL字符集等配置
40 0
|
2月前
|
SQL 关系型数据库 MySQL
完美,阿里DBA骨干团队编写的792页MySQL调优笔记真香
这个世界是由问题组成的,理想的状态和实际状态之间的差异造成了问题。国家领导解决人民生活幸福的大问题,公司的总经理解决盈利的问题,而本书只想解决MySQL数据库性能这么一一个“小问题”。
|
7月前
|
存储 SQL NoSQL
MySQL 字符集
MySQL 字符集
37 0
|
2月前
|
存储 关系型数据库 MySQL
mysql修改字符集
mysql修改字符集
27 1