MySQL的字符集和校对规则，你都会了吗？

缺乏、安全感 2023-02-15 03:56 30阅读 0赞

**    一、字符集(Character set)**

是多个字符(英文字符，汉字字符，或者其他国家语言字符)的集合，字符集种类较多，

每个字符集包含的字符个数不同。

常见字符集：

ASCII字符集：基于罗马字母表的一套字符集，它采用1个字节的低7位表示字符，高位始终为0。

LATIN1字符集：相对于ASCII字符集做了扩展，仍然使用一个字节表示字符，但启用了高位，扩展了字符集的表示范围。

GBK字符集：支持中文，字符有一字节编码和两字节编码方式。

UTF8字符集：Unicode字符集的一种，是计算机科学领域里的一项业界标准，支持了所有国家的文字字符，utf8采用1-4个字节表示字符。

> 1、MySQL与字符集

只要涉及到文字的地方，就会存在字符集和编码方式。MySQL系统变量值：

![format_png][]

**2、正确使用字符集**

数据库服务端的字符集具体要看存储什么字符

![format_png 1][]

更新流程字符集转换过程：character\_set\_client-->character\_set\_connection-->表字符集。

查询流程字符集转换过程：表字符集-->character\_set\_result

3.character\_set\_database

当前默认数据库的字符集，比如执行use xxx后，当前数据库变为xxx，若xxx的字符集为utf8，那么此变量值就变为utf8(供系统设置，无需人工设置)。

**3、MySQL客户端与字符集**

1.对于输入来说：

客户端使用的字符集必须通过character\_set\_client、character\_set\_connection体现出来：

①在客户端对数据进行编码（Linux：utf8、windows：gbk）

②MySQL接到SQL语句后(比如insert)，发现有字符，询问客户端通过什么方式对字符编码：客户端通过character\_set\_client参数告知MySQL客户端的编码方式(所以此参数需要正确反映客户端对应的编码)

③当MySQL发现客户端的client所传输的字符集与自己的connection不一样时，会将client的字符集转换为connection的字符集

④MySQL将转换后的编码存储到MySQL表的列上，在存储的时候再判断编码是否与内部存储字符集（按照优先级判断字符集类型）上的编码一致，如果不一致需要再次转换

2.对于查询来说：

客户端使用的字符集必须通过character\_set\_results来体现，服务器询问客户端字符集，通过character\_set\_results将结果转换为与客户端相同的字符集传递给客户端。(character\_set\_results默认等于character\_set\_client)

**4、MySQL字符编码转换原理**

问：若character\_set\_client为UTF8，而character\_set\_database为GBK，则会出现需要进行编码转换的情况，字符集转换的原理是什么？

答：假设gbk字符集的字符串“你好”，需要转为utf8字符集存储，实际就是对于“你好”字符串中的每个汉字去utf8编码表里面查询对应的二进制，然后存储。

![format_png 2][]

**5、字符集常见处理操作**

1.查看字符集编码设置

mysql> show variables like '%character%';

2.设置字符集编码

mysql> set names 'utf8';

相当于同时：

set character\_set\_client = utf8;

set character\_set\_results = utf8;

set character\_set\_connection = utf8;

3.修改数据库字符集

mysql> alter database database\_name character set xxx;

只修改库的字符集，影响后续创建的表的默认定义；对于已创建的表的字符集不受影响。（一般在数据库实现字符集即可，表和列都默认采用数据库的字符集）

4.修改表的字符集

mysql> alter table table\_name character set xxx；

只修改表的字符集，影响后续该表新增列的默认定义，已有列的字符集不受影响。

mysql> alter table table\_name convert to character set xxx;

同时修改表字符集和已有列字符集，并将已有数据进行字符集编码转换。

5.修改列字符集

mysql> alter table table\_name modify col\_name varchar(col\_length) character set xxx;

**6、字符集的正确实践**

MySQL软件工具本身是没有字符集的，主要是因为工具所在的OS的字符集（Windows：gbk、Linux：utf8），所以字符集的正确实践非常重要：

1.对于insert来说，character\_set\_client、character\_set\_connection相同，而且正确反映客户端使用的字符集

2.对于select来说，character\_set\_results正确反映客户端字符集

3.数据库字符集取决于我们要存储的字符类型

4.字符集转换最多发生一次，这就要求character\_set\_client、character\_set\_connection相同

5.所有的字符集转换都发生在数据库端

综述：

1、建立数据库的时候注意字符集（gbk、utf8）；

2、连接数据库以后，无论是执行dml还是select，只要涉及到varchar、char列，就需要设置正确的字符集参数。

**7、校对规则**

字符集是一套符号和对应的编号

查看数据库支持的所有字符集(charset)：

mysql> show character set;

校对规则(collation)：

是在字符集内用于字符比较和排序的一套规则，比如有的规则区分大小写，有的则无视。

mysql> create table t1(id int,name varchar(20));    \#t1建表没有指定校对规则

![format_png 3][]

mysql> show collation;  \#查看数据库支持的所有校对规则

mysql> show variables like 'collation\_%';  \#查看当前字符集和校对规则设置

![format_png 4][]

校对规则特征：

①两个不同的字符集不能有相同的校对规则；

②每个字符集有一个默认校对规则；

③存在校对规则命名约定：以其相关的字符集名开始，中间包括一个语言名，并且以\_ci（大小写不敏感）、\_cs（大小写敏感）或\_bin（二元）结束。

注意：

系统使用utf8字符集，若使用utf8\_bin校对规则执行SQL查询时区分大小写，使用utf8\_general\_ci不区分大小写(默认的utf8字符集对应的校对规则是utf8\_general\_ci)。

示例：

mysql> create table t2(id int,name varchar(20)) character set=gbk collate=gbk\_bin;    \#t2建表指定校对规则（区分大小写）

![format_png 5][]

![format_png 6][]

[MySQL存储过程的动态行转列][MySQL]

[Java 应用性能调优的一些实践][Java]

[可视化界面在线生成JVM参数][JVM]

[java内存溢出问题分析过程二(附MAT超全操作文档)][java_MAT]

[使用Eclipse Memory Analyzer Tool（MAT）分析线上故障(一)][Eclipse Memory Analyzer Tool_MAT]

[一文让你理解什么是shallow heap及retained heap][shallow heap_retained heap]

[内存分析诊断系列-理解heap dump][-_heap_dump]

[记一次服务器被当肉鸡挖矿的经历][Link 1]

[如何编写一个可复用的SpringBoot应用运维脚本][SpringBoot]

[高效率编写Dockerfile需要绕过的一些坑][Dockerfile]

[Mysql百万量级数据高效导入Redis][Mysql_Redis]

[多线程之CountDownLatch的用法及原理笔记][CountDownLatch]

![format_png 7][]

我就知道你“在看”

![format_png 8][]

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy94aWNXWVRTSUN6UnRjYzY0dW80UHY5N3RBZlJkd2Z2blNUaWNxeFIyWEdnV2J1QmNVeUlNSW5BRkJuWnVlb1J4TDBmdDA2aWFoNWljMXJwTnlDc1E0dHRNaWFnLzY0MA?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy94aWNXWVRTSUN6UnRjYzY0dW80UHY5N3RBZlJkd2Z2blNWZmlia1lpYnFuU09jOHZWQ2YyMWljb3JNQjc5Z2pkZWNLVTlIM1BiSnRpY0k1aHBrUFk5RFFzaWJ0US82NDA?x-oss-process=image/format,png
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy94aWNXWVRTSUN6UnRjYzY0dW80UHY5N3RBZlJkd2Z2blNmeEI4SHp2Zm1LbWNzN3Q1TGFPaWNRMWliRDZUdkdMeVJUaWNpY1BvRXE4eWFSbHhNNFhjRUs0ZlZnLzY0MA?x-oss-process=image/format,png
[format_png 3]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy94aWNXWVRTSUN6UnRjYzY0dW80UHY5N3RBZlJkd2Z2blM3S04wOFBWS3RRbFVJZFlSZTVzNWhZRjEyZW00SnFyMHQ5NzFIMFl6bG1ka2NYcnlPWmtMQUEvNjQw?x-oss-process=image/format,png
[format_png 4]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy94aWNXWVRTSUN6UnRjYzY0dW80UHY5N3RBZlJkd2Z2blNwUkdFNHJMdWZsOWphY010TmpaSDVrWjgwR0s4b0p4c200SFJvYnM0TFRQc0ZvNmdtZkFiNFEvNjQw?x-oss-process=image/format,png
[format_png 5]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy94aWNXWVRTSUN6UnRjYzY0dW80UHY5N3RBZlJkd2Z2blNlMUd5cVFabGljdXhYaWJnSFdUVzVTNUlmU2ZpY3hTWU92R0ZwbzhWMzFnVkV1cDBIQ0lBdXpGWFEvNjQw?x-oss-process=image/format,png
[format_png 6]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2dpZi94aWNXWVRTSUN6UnRDYWxzMWRGZFVjZ1lTWDVFMFhQN2liWklsc2FpYzNnMGg5TWtDT2VONkI5aWNuOHJCa3Z0TnBWbmxoc2hGQ1BxNHFXcjFxMnJYdk5HSmcvNjQw?x-oss-process=image/format,png
[MySQL]: http://mp.weixin.qq.com/s?__biz=MzU2NDc4MjE2Ng%3D%3D&chksm=fc44f629cb337f3ffbc90e256e938f1bebcc6cd76b2220c9e68cfa0b9d641330f9c40d466946&idx=1&mid=2247484176&scene=21&sn=e52ec2c145e92753f6b3deee477de88e#wechat_redirect
[Java]: http://mp.weixin.qq.com/s?__biz=MzU2NDc4MjE2Ng%3D%3D&chksm=fc44f7dccb337ecadd6f748c4d5aae213003d3cc40c501d9865591e18539343083070888203b&idx=1&mid=2247484133&scene=21&sn=70156fc59563970321007d04a3d160b0#wechat_redirect
[JVM]: http://mp.weixin.qq.com/s?__biz=MzU2NDc4MjE2Ng%3D%3D&chksm=fc44f7e6cb337ef0bf114949eec1eb93e404f8ae64988a03cdc53a014119f496172a5de568f4&idx=1&mid=2247484127&scene=21&sn=92e774ee1edc2dd0013c60f1347d0fe6#wechat_redirect
[java_MAT]: https://blog.csdn.net/u012811805/article/details/104770774
[Eclipse Memory Analyzer Tool_MAT]: http://mp.weixin.qq.com/s?__biz=MzU2NDc4MjE2Ng%3D%3D&chksm=fc44f7e8cb337efe3c8a0d65fdcad6021c841cd5c00c47e20b09ff7fa31470a29865cda12a0a&idx=1&mid=2247484113&scene=21&sn=2f603fec4d105d832db0783369b9fe57#wechat_redirect
[shallow heap_retained heap]: https://blog.csdn.net/u012811805/article/details/104737895
[-_heap_dump]: http://mp.weixin.qq.com/s?__biz=MzU2NDc4MjE2Ng%3D%3D&chksm=fc44f794cb337e82fdcd6ad918d1a490d90908b6f453ddb45ad55b4a24a8b749e09baa9cc7a8&idx=1&mid=2247484077&scene=21&sn=5ae695faef094c78f2aa257b58b3034a#wechat_redirect
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzU2NDc4MjE2Ng%3D%3D&chksm=fc44f7a7cb337eb1916c752918a881cd6cc033caa83657876ae3292e9e0639b7040ecfb37a9c&idx=1&mid=2247484062&scene=21&sn=d16d8ad10c5d4d26919276e7973708a5#wechat_redirect
[SpringBoot]: http://mp.weixin.qq.com/s?__biz=MzU2NDc4MjE2Ng%3D%3D&chksm=fc44f7adcb337ebbd0f51405a4502fc8e0ad0afdc562cbab581209e92b040645de7189a96f23&idx=1&mid=2247484052&scene=21&sn=e8e0520c74584721b366085834fd0489#wechat_redirect
[Dockerfile]: https://blog.csdn.net/u012811805/article/details/104638007
[Mysql_Redis]: http://mp.weixin.qq.com/s?__biz=MzU2NDc4MjE2Ng%3D%3D&chksm=fc44f743cb337e55a10570a29831dd060548a10e51988cbe27440c6c92d6b54589bf5accbc0d&idx=1&mid=2247484026&scene=21&sn=e537c94bb695d28f7a82f19f4b688899#wechat_redirect
[CountDownLatch]: https://blog.csdn.net/u012811805/article/details/104203801
[format_png 7]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy94aWNXWVRTSUN6UnZ6aGRLck1pY1NQSWliZzhQRWJPa2E4bmpjOHJFZmFLa09YTHAyd21wckxpY0JRaWNYMWlhdVFzWEdTTDlpYXhreFZTb3RQQ2F2U2hva1NvVHcvNjQw?x-oss-process=image/format,png
[format_png 8]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2dpZi94aWNXWVRTSUN6UnRDYWxzMWRGZFVjZ1lTWDVFMFhQN2liTVhOMUtqMlN0cmptVWoybEpMbHp0WUo0ejZqN1JYd0VGRUtjYUhpYmt4ekt2ODVqMlgwYTJLZy82NDA?x-oss-process=image/format,png