性能文章>MySQL的字符集与排序规则对开发有哪些影响?>

MySQL的字符集与排序规则对开发有哪些影响?原创

1年前
526223

任何计算机存储数据,都需要字符集,因为计算机存储的数据其实都是二进制编码,将一个个字符,映射到对应的二进制编码的这个映射就是字符编码(字符集)。这些字符如何排序呢?决定字符排序的规则就是排序规则。

查看内置字符集与比较规则

通过show charset;命令,可以查看所有的字符集。
以下仅展示了我们常用的字符集:

+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
+----------+---------------------------------+---------------------+--------+
  • ascii:共收录128个字符,包括空格、标点符号、数字、大小写字母和一些不可见字符。由于总共才128个字符,所以可以使用1个字节来进行编码
  • 共收录256个字符,是在ASCII字符集的基础上又扩充了128个西欧常用字符(包括德法两国的字母),也可以使用1个字节来进行编码。
  • 收录了汉字以及拉丁字母、希腊字母、日文平假名及片假名字母、俄语西里尔字母。其中收录汉字6763个,其他文字符号682个,兼容ASCII字符集。这是一个变长字符集,如果该字符在ascii字符集中,则采用1字节编码,否则采用两字节。
  • GBK是在gb2312基础上扩容后的标准。收录了所有的中文字符。同样的,这是一个变长字符集,如果该字符在ascii字符集中,则采用1字节编码,否则采用两字节。
  • utf8和utf8mb4: 收录地球上能想到的所有字符,而且还在不断扩充。这种字符集兼容ASCII字符集,采用变长编码方式,编码一个字符需要使用1~4个字节。MySQL为了节省空间,其中的utf8是标准 UTF8 阉割后的,只有1~3字节编码的字符集,基本包含了所有常用的字符。如果还要使用 enoji 表情,那么需要使用utf8mb4,这个是完整的 UTF8 字符集。
  • utf16: 不同于utf8,utf16用两个字节或者四个字节编码字符,可以理解为utf8的不节省空间的一种形式
  • utf32: 固定用四个字节编码字符,可以理解为utf8的不节省空间的一种形式

通过查看information_schema.character_sets表,也可以看到所有的字符集:

mysql> select * from information_schema.character_sets where character_set_name = "utf8";
+--------------------+----------------------+---------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION   | MAXLEN |
+--------------------+----------------------+---------------+--------+
| utf8               | utf8_general_ci      | UTF-8 Unicode |      3 |
+--------------------+----------------------+---------------+--------+
1 row in set (0.06 sec)

通过show collation;命令,可以查看所有的字符集,我们这里来查看utf8mb4的排序规则:

mysql> show collation like 'utf8mb4%';
+------------------------+---------+-----+---------+----------+---------+
| Collation              | Charset | Id  | Default | Compiled | Sortlen |
+------------------------+---------+-----+---------+----------+---------+
| utf8mb4_general_ci     | utf8mb4 |  45 | Yes     | Yes      |       1 |
| utf8mb4_bin            | utf8mb4 |  46 |         | Yes      |       1 |
| utf8mb4_unicode_ci     | utf8mb4 | 224 |         | Yes      |       8 |
| utf8mb4_icelandic_ci   | utf8mb4 | 225 |         | Yes      |       8 |
| utf8mb4_latvian_ci     | utf8mb4 | 226 |         | Yes      |       8 |
| utf8mb4_romanian_ci    | utf8mb4 | 227 |         | Yes      |       8 |
| utf8mb4_slovenian_ci   | utf8mb4 | 228 |         | Yes      |       8 |
| utf8mb4_polish_ci      | utf8mb4 | 229 |         | Yes      |       8 |
| utf8mb4_estonian_ci    | utf8mb4 | 230 |         | Yes      |       8 |
| utf8mb4_spanish_ci     | utf8mb4 | 231 |         | Yes      |       8 |
| utf8mb4_swedish_ci     | utf8mb4 | 232 |         | Yes      |       8 |
| utf8mb4_turkish_ci     | utf8mb4 | 233 |         | Yes      |       8 |
| utf8mb4_czech_ci       | utf8mb4 | 234 |         | Yes      |       8 |
| utf8mb4_danish_ci      | utf8mb4 | 235 |         | Yes      |       8 |
| utf8mb4_lithuanian_ci  | utf8mb4 | 236 |         | Yes      |       8 |
| utf8mb4_slovak_ci      | utf8mb4 | 237 |         | Yes      |       8 |
| utf8mb4_spanish2_ci    | utf8mb4 | 238 |         | Yes      |       8 |
| utf8mb4_roman_ci       | utf8mb4 | 239 |         | Yes      |       8 |
| utf8mb4_persian_ci     | utf8mb4 | 240 |         | Yes      |       8 |
| utf8mb4_esperanto_ci   | utf8mb4 | 241 |         | Yes      |       8 |
| utf8mb4_hungarian_ci   | utf8mb4 | 242 |         | Yes      |       8 |
| utf8mb4_sinhala_ci     | utf8mb4 | 243 |         | Yes      |       8 |
| utf8mb4_german2_ci     | utf8mb4 | 244 |         | Yes      |       8 |
| utf8mb4_croatian_ci    | utf8mb4 | 245 |         | Yes      |       8 |
| utf8mb4_unicode_520_ci | utf8mb4 | 246 |         | Yes      |       8 |
| utf8mb4_vietnamese_ci  | utf8mb4 | 247 |         | Yes      |       8 |
+------------------------+---------+-----+---------+----------+---------+
26 rows in set (0.13 sec)

同样的,通过查询information_schema.collations也可以:

mysql> select * from information_schema.collations where character_set_name = "utf8mb4";
+------------------------+--------------------+-----+------------+-------------+---------+
| COLLATION_NAME         | CHARACTER_SET_NAME | ID  | IS_DEFAULT | IS_COMPILED | SORTLEN |
+------------------------+--------------------+-----+------------+-------------+---------+
| utf8mb4_general_ci     | utf8mb4            |  45 | Yes        | Yes         |       1 |
| utf8mb4_bin            | utf8mb4            |  46 |            | Yes         |       1 |
| utf8mb4_unicode_ci     | utf8mb4            | 224 |            | Yes         |       8 |
| utf8mb4_icelandic_ci   | utf8mb4            | 225 |            | Yes         |       8 |
| utf8mb4_latvian_ci     | utf8mb4            | 226 |            | Yes         |       8 |
| utf8mb4_romanian_ci    | utf8mb4            | 227 |            | Yes         |       8 |
| utf8mb4_slovenian_ci   | utf8mb4            | 228 |            | Yes         |       8 |
| utf8mb4_polish_ci      | utf8mb4            | 229 |            | Yes         |       8 |
| utf8mb4_estonian_ci    | utf8mb4            | 230 |            | Yes         |       8 |
| utf8mb4_spanish_ci     | utf8mb4            | 231 |            | Yes         |       8 |
| utf8mb4_swedish_ci     | utf8mb4            | 232 |            | Yes         |       8 |
| utf8mb4_turkish_ci     | utf8mb4            | 233 |            | Yes         |       8 |
| utf8mb4_czech_ci       | utf8mb4            | 234 |            | Yes         |       8 |
| utf8mb4_danish_ci      | utf8mb4            | 235 |            | Yes         |       8 |
| utf8mb4_lithuanian_ci  | utf8mb4            | 236 |            | Yes         |       8 |
| utf8mb4_slovak_ci      | utf8mb4            | 237 |            | Yes         |       8 |
| utf8mb4_spanish2_ci    | utf8mb4            | 238 |            | Yes         |       8 |
| utf8mb4_roman_ci       | utf8mb4            | 239 |            | Yes         |       8 |
| utf8mb4_persian_ci     | utf8mb4            | 240 |            | Yes         |       8 |
| utf8mb4_esperanto_ci   | utf8mb4            | 241 |            | Yes         |       8 |
| utf8mb4_hungarian_ci   | utf8mb4            | 242 |            | Yes         |       8 |
| utf8mb4_sinhala_ci     | utf8mb4            | 243 |            | Yes         |       8 |
| utf8mb4_german2_ci     | utf8mb4            | 244 |            | Yes         |       8 |
| utf8mb4_croatian_ci    | utf8mb4            | 245 |            | Yes         |       8 |
| utf8mb4_unicode_520_ci | utf8mb4            | 246 |            | Yes         |       8 |
| utf8mb4_vietnamese_ci  | utf8mb4            | 247 |            | Yes         |       8 |
+------------------------+--------------------+-----+------------+-------------+---------+
26 rows in set (0.11 sec)
  • 每个字符集都有一个默认的排序规则:IS_DEFAULT 为 Yes。
  • 比较规则名称以与其关联的字符集的名称开头,可以用通过这个开头查询所有的字符集,也可以查询information_schema.collations精确指定字符集
  • 字符集后面跟着的是语言编码,因为utf8mb4包含了所有字符,不同国家的文字语言排序肯定不一样。
  • 最后末尾的ci代表case insensitive,大小写不敏感,所有可能的后缀如下所示:
  • ai: accent insensitive 不区分重音
  • as: accent sensitive 区分重音
  • ci: case insensitive 不区分大小写
  • cs: case sensitive 区分大小写
  • bin: binary 以二进制方式比较

应用字符集与比较规则
字符集与比较规则配置有四个级别:

  • MySQL实例级别
  • 库级别
  • 表级别
  • 字段级别
    指定的级别粒度越小,则以粒度越小的字符集还有比较规则优先。例如指定MySQL实例级别字符集是utf8mb4,指定某个表字符集是latin1,那么这个表的所有字段如果不指定的话,编码就是latin1

由于字符集和比较规则是互相有联系的,如果我们只修改了字符集,比较规则也会跟着变化,如果只修改了比较规则,字符集也会跟着变化,具体规则如下:

  • 只修改字符集,则比较规则将变为修改后的字符集默认的比较规则。
  • 只修改比较规则,则字符集将变为修改后的比较规则对应的字符集。

实例级别

通过两个系统变量来指定实例级别的字符集与排序规则。

配置文件:

[server]
character_set_server=utf8mb4
collation_server=utf8mb4_general_ci

启动之后,可以查看并修改这两个变量。

mysql>  show variables like 'character_set_server';
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| character_set_server | utf8mb4 |
+----------------------+---------+
1 row in set (0.06 sec)

mysql> show variables like 'collation_server';
+------------------+--------------------+
| Variable_name    | Value              |
+------------------+--------------------+
| collation_server | utf8mb4_general_ci |
+------------------+--------------------+
1 row in set (0.05 sec)

mysql> set character_set_server = 'utf8mb4';
Query OK, 0 rows affected (0.00 sec)

mysql> set collation_server = 'utf8mb4_general_ci';
Query OK, 0 rows affected (0.00 sec)

库级别

创建数据库的时候,可以指定字符集还有排序规则。

mysql> create database test_db character set utf8mb4 collate utf8mb4_general_ci;
Query OK, 1 row affected (0.01 sec)

不指定的话,就用实例级别的字符集还有排序规则。

查看当前数据库的字符集还有排序规则则是通过use命令指定数据库之后,查看character_set_database变量以及collation_database来实现:

mysql> show variables like 'character_set_database';
+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| character_set_database | utf8mb4 |
+------------------------+---------+
1 row in set (0.07 sec)

mysql> show variables like 'collation_database';
+--------------------+--------------------+
| Variable_name      | Value              |
+--------------------+--------------------+
| collation_database | utf8mb4_general_ci |
+--------------------+--------------------+
1 row in set (0.09 sec)

就算设置这两个变量,也是无效的:

mysql> set character_set_database = 'utf8';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'character_set_database';
+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| character_set_database | utf8mb4 |
+------------------------+---------+
1 row in set (0.09 sec)

修改数据库的字符集还有排序规则的方式:

mysql> alter database test_db character set = 'utf8';
Query OK, 1 row affected (0.01 sec)

mysql> show variables like 'character_set_database';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| character_set_database | utf8  |
+------------------------+-------+
1 row in set (0.08 sec)

这个更新只会对新建的表如果没指定字符集和排序规则的生效,并不会更新老表的字符集还有排序规则。

表级别

可以在创建时指定字符集合排序规则,不指定的话,用数据库的字符集还有排序规则,也可以修改字符集和排序规则。


mysql> create table test (name varchar(32)) character set utf8mb4 collate utf8mb4_bin; 
Query OK, 0 rows affected (0.04 sec)

mysql> show create table test;
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                          |
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `name` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.09 sec)

mysql> alter table test character set = 'utf8';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test;
+-------+--------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                         |
+-------+--------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)

可以看出,仅仅是表的字符集还有排序规则变了,对于已有字段,并没有改变编码和排序规则。

列级别

可以在创建表的时候,指定不同的列有不同的字符集和排序规则,也可以修改列的字符集和排序规则:

mysql> create table test (name varchar(32) character set utf8 collate utf8_bin) character set utf8mb4 collate utf8mb4_bin; 
Query OK, 0 rows affected (0.03 sec)

mysql> show create table test;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                          |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `name` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.09 sec)

mysql> alter table test modify column name  varchar(32) COLLATE latin1_bin;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                              |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `name` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.09 sec)

MySQL客户端字符编码问题

有时候,我们会遇到字符编码不一致导致的程序问题。例如我们的 Java 程序,使用 jdbc 链接。读取的数据,打印出来是乱码。或者是,MySQL 无法识别我们客户端发来的命令。这涉及到字符编码问题。我们需要保持 Java 程序的字符编码与 JDBC 链接指定的字符编码一致,这样才不会有乱码的问题。

指定 Java 程序编码:通过启动参数:-Dfile.encoding=UTF-8 设置默认的字符编码(java.nio.charset.Charset.defaultCharset();)是utf-8(对应 MySQL 的utf8还有utf8mb4)。

指定 JDBC 链接编码

jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8

mysql客户端命令行指定字符集

mysql -h 127.0.0.1 -P 3306 -u root  --default-character-set=utf8mb4  -p

之后查看有关编码的环境变量,都是和设置的这个字符集一样。

mysql> SHOW VARIABLES LIKE 'character_set_client';
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| character_set_client | utf8mb4 |
+----------------------+---------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW VARIABLES LIKE 'character_set_connection';
+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| character_set_connection | utf8mb4 |
+--------------------------+---------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW VARIABLES LIKE 'character_set_results';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| character_set_results | utf8mb4 |
+-----------------------+---------+
1 row in set, 1 warning (0.00 sec)

其中:

  • character_set_client: 服务器解码请求时使用的字符集
  • character_set_connection:服务器处理请求时将字符集转换成这个字符集处理。操作具体列时,在转换为具体列的编码。
  • character_set_results:服务器向客户端返回数据时使用的字符集

MySQL 设计这三个编码的时候,出于以下考虑:

  • 一个 MySQL,可能有多种不同语言和操作系统或者国家的客户端,所以通过设置character_set_client还有character_set_results进行兼容。
  • 由于操作具体列数据的时候需要编码转换,如果character_set_connection和字段一致的话,就不用转换了,所以设置character_set_connection可以让 MySQL 用一种编码理解命令统一处理,同时设置character_set_connection为最常用的可以减少转换

一般情况下,保持这三个一致就好。我们就设置好连接使用的字符集就行了。

今日分享就此结束,欢迎大家踊跃留言、评论哦~

请先登录,再评论

关于字符集的讲解很详细,赞👍👍👍

11年前

睁开眼,就能学习。社区给力啊!!!!

21年前

为你推荐

JVM 源码分析之一个 Java 进程究竟能创建多少线程
概述虽然这篇文章的标题打着JVM源码分析的旗号,不过本文不仅仅从 JVM 源码角度来分析,更多的来自于 Linux Kernel 的源码分析,今天要说的是 JVM 里比较常见的一个问题。这个问题可能有
线程池运用不当的一次线上事故
在高并发、异步化等场景,线程池的运用可以说无处不在。线程池从本质上来讲,即通过空间换取时间,因为线程的创建和销毁都是要消耗资源和时间的,对于大量使用线程的场景,使用池化管理可以延迟线程的销毁,大大提高
YGC问题排查,又让我涨姿势了!
在高并发下,Java程序的GC问题属于很典型的一类问题,带来的影响往往会被进一步放大。不管是「GC频率过快」还是「GC耗时太长」,由于GC期间都存在Stop The World问题,因此很容易导致服务
【活动结果公布:兑奖见置顶评论】每日一签到,解锁你的PerfMa壕礼
叮......Perfma达人福利发车啦!社区每日签到解锁活动上线啦~除了每日签到必得积分奖励外,还为大家增加了壕礼相赠、解锁Perfma终极大奖等环节奖励 ~来Perfma社区留下你的签到足迹,告诉
使用Top_X插件排查内存过载问题
Top命令是Linux 系统下常用的监控工具,用于实时获取进程级别的 CPU 或内存使用情况。XPocket中的Top_X为Linux Top的增强版,可以显示CPU占用率/负载,CPU及内存进程使用
记一次Synchronized关键字使用不合理,导致的多线程下线程阻塞问题排查
在为客户进行性能诊断调优时,碰到了一个Synchronized关键字使用不合理导致多线程下线程阻塞的情况。用文字记录下了问题的整个发现-排查-分析-优化过程,排查过程中使用了我司商业化产品——XLan
记一次类加载失败导致线程阻塞问题排查
作为PerfMa解决方案管理部门的技术专家,我在工作遇见过很多各种问题导致的性能问题,并参与了为客户的系统进行性能诊断调优的全过程。这一次碰到了一个类加载失败导致的性能问题。用文字记录下了问题的整个发
一次大量 JVM Native 内存泄露的排查分析(64M 问题)
我们有一个线上的项目,刚启动完就占用了使用 top 命令查看 RES 占用了超过 1.5G,这明显不合理,于是进行了一些分析找到了根本的原因,下面是完整的分析过程,希望对你有所帮助。会涉及到下面这些内容Linux 经典的 64M 内存问题堆内存分析、Native 内存分析的基本套路