MySQL原理 - 字符集与排序规则

通过查看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)

字符集与比较规则配置有四个级别：

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

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

配置文件：

[server]character_set_server=utf8mb4collation_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: 0mysql> 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: 0mysql> 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)

有时候，我们会遇到字符编码不一致导致的程序问题。例如我们的 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)

其中：

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

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

MySQL原理 - 字符集与排序规则

标签：names   创建表   字符排序   情况下   0 rows   精确   form   nic   ble   

小编还为您整理了以下内容，可能对您也有帮助：

MySQL varchar存储、字符集、排序规则、索引长度

由于历史原因，MySQL刚开始设计的时候，"天真的"认为使用3个字节就足够存储字符串了，因此将UTF-8进行阉割;然而遇到复杂的汉字或者emoji表情等4字节的宽字符的时候，存储就会出现异常，因此在版本5.7.3开始引入utf8mb4，其表示为most bytes 4，即最多占用4个字节。

utf8mb4_unicode_ci是基于官方的Unicode规则进行排序和压缩，其算法相对负责，对于大部分的语言和字符集排序有着很高的准确率;而uft8mb4_general_ci可以理解为一种为了提升速度的简化版Unicode规则，但由于它不完全遵循Unicode规则，在使用某种特定语言或者字符集时，会出现非预期的结果。

例:

总结:

UTF-8编码的字符可以是1-4个字节，但是在MySQL中最大只能存储3个字节。

在版本5.5开始引入innodb_large_prefix，其默认值为off，索引的前缀最大为767个字节；若值为on时(版本5.7.7开始作为默认值)，最大为3072个字节。

总结:

在后期版本 innodb_large_prefix 将会被逐渐废弃并移除。从版本8.0开始，索引长度由表字段(row format)决定，若为DYNAMIC或COMPRESSED时，值为3072；为REDUNDANT或COMPACT时，值为767。且row_format=dynamic时，长度3072是基于innodb_page_size=16KB，随着innodb_page_size的值按比例增减，其索引前缀长度也响应减小，如若为8KB时，长度为1536，因此在索引长度时，需根据使用的MySQL版本以及相应的参数进行配置决定。

请简述设置mysql数据库字符集的规则

字符集：罗列所有图形字符的一张大表。
排序规则：定义各个图形字符之间的大小比较规则，比如：是否区分大小写，区分全角和半角等。在软件使用中，一般我们只指定字符编码即可，因为确定了字符编码字符集自然就确定了。但是在数据库类软件中，我们除了要指定编码规则，还需要指定排序规则，因为，数据库是要提供模糊匹配、排序显示功能的。sql可以查看mysql支持的字符集编码和排序规则，其中每个字符集编码都有一个默认的排序规则。

请简述设置mysql数据库字符集的规则

字符集：罗列所有图形字符的一张大表。
排序规则：定义各个图形字符之间的大小比较规则，比如：是否区分大小写，区分全角和半角等。在软件使用中，一般我们只指定字符编码即可，因为确定了字符编码字符集自然就确定了。但是在数据库类软件中，我们除了要指定编码规则，还需要指定排序规则，因为，数据库是要提供模糊匹配、排序显示功能的。sql可以查看mysql支持的字符集编码和排序规则，其中每个字符集编码都有一个默认的排序规则。

navicat 工具新建数据库时，字符集 和 排序规则，是什么意思啊，又该怎么选择

字符集一般是utf8. 排序也选utf8. 一般选择和表一样。

通过mysql命令修改：修改数据库的字符集 mysql>use mydb mysql>alter database mydb character set utf-8; 

创建数据库指定数据库的字符集 mysql>create database mydb character set utf-8;

 通过配置文件修改: 修改/var/lib/mysql/mydb/db.opt default-character-set=latin1 default-collation=latin1_swedish_ci 为 default-character-set=utf8 default-collation=utf8_general_ci 重起MySQL。

扩展资料：

Navicat Premium 使能简单并快速地在各种数据库系统间传输数据，或传输一份指定 SQL 格式及编码的纯文本文件。这可以简化从一台服务器迁移数据到另一台服务器的类型的进程。不同数据库的批处理作业也可以计划并在指定的时间运行。

不同数据库的批处理作业可以计划并在指定的时间运行。其他功能包括导入向导、导出向导、查询创建工具、报表创建工具、资料同步、备份、工作计划及更多。

参考资料来源：百度百科-navicat

MySQL中数据库的默认字符集和校对规则有哪些？

1. ASCII

用途：用来映射简单的单字节字符，比如大小写英文字母、阿拉伯数字、常用的标点符、运算符、控制字符等。

编码范围：U+0000 - U+007F

注意：对于用这类字符的场景够用了，但是却无法表达比如汉字，日文等编码。

2. UNICODE

用途：用来映射包含 ASCII 以内的其他的所有字符。

编码范围：U+0000 - U+10FFFF

注意：ASCII 是 UNICODE 的子集，ASCII 编码的字符可以无损转换为 UNICODE 编码的字符。

MySQL 常用字符集

1. Latin1

Latin1 是 cp1252 或者 ISO-8859-1 的别名。ISO-8859-1 编码是单字节编码，向下兼容 ASCII。

编码范围：U+0000 - U+00FF

ISO-8859-1 收录的字符除 ASCII 收录的字符外，还包括西欧语言、希腊语、泰语、阿拉伯语、希伯来语对应的文字符号。

单字节内的空间都被 ISO-8859-1 编码占用，所以能够用 ISO-8859-1 编码存储、传输其他任何编码的字节流。

比如把一个 Utf8mb4 的编码或者 GBK 的编码存入 Latin1，不会有任何问题。因为 Latin1 保留了原始的字节流，这也就是 MySQL 长期以来把 Latin1 做默认字符集的原因。

但是由于 Latin1 对任何字符都存放字节流，造成了字符个数的浪费。

比如：

CHAR(10) CHARACTER SET LATIN1;CHAR(10) CHARACTER SET UTF8;

该字段中存储字符个数 UTF8 是 Latin1 的三倍！！！

2. GB18030

GB18030 是中国官方标准字符集，向前兼容 GBK、GB2312，是这两个的超集。用 1、2、4 个字节分别表示一个符号。比如对一般中文字符，默认是用两个字节编码存储。Windows 系统，默认用的就是 GB18030。

若只是存储中文字符，那 GB18030 最佳。

原因有两点：

1）占用空间小，比如比 UTF8 小。

2）存储的汉字根据拼音来排序，检索快。

3. UTF8

UTF8 是 Unicode 的编码实现，可以存储 UNICODE 编码对应的任何字符， 这也是使用最多的一种编码。最大的特点就是变长的编码方式，用 1 到 4 个字节表示一个符号，可以根据不同的符号编码字节长度。

字母或数字用 1 字节，汉字用 3 字节，emoji 表情符号用 4 字节。UTF8 字符集目前是使用最广泛的。

注意！MySQL 里常说的 UTF8 是 UTF8MB3 的别名，UTF8MB3 是 UTF8MB4 的子集，UTF8MB4 才是真正的 4 字节 UTF8 字符集！

UTF8MB3 表示最大支持 3 个字节存储字符，UTF8MB4 表示最大 4 个字节存储字符。根据实际需要和未来展望，MySQL 8.0 已经默认用 UTF8MB4 基础字符集。