mysql字符集和排序字符集的区别

1.MySQL的字符集（character set）问题（乱码问题）：

1.1 版本与字符集：

​ Mysql5.5版本在my.ini上上修改character-set-server为utf8

​ Mysql8.0以上安装完默认character_set_server 为utf8mb4

​ default-character-set=utf8 适合5.1及之前版本

​ character-set-server=utf8 适合5.5及之后版本

​ 在修改完之前创建的****database是无效的，再创建一个库才有效

1.2 utf8mb4和utf8的区别：

​ utf8mb4是utf8 的扩展版，支持更多的字码形式

​ utf8mb4最大支持四个字节，utf8最大三个字节

1.3 其他编码的问题：

​ ASCII编码中，一个英文字母（不分大小写）占用一个字节的空间，一个中文汉字占用两个字节的空间。一个二进制的数字序列，在计算机中作为一个数字单元存储时，一般为8位二进制数，换算为十进制。最小值0，最大值255。

​ UTF-8编码中，一个英文字符占用一个字节的存储空间，一个中文（含繁体）占用三个字节的存储空间。 Unicode（俗称万国码）编码中，一个英文占用两个字节的存储空间，一个中文（含繁体）占用两个字节的存储空间。

​ UTF-16编码中，一个英文字母字符或一个汉字字符存储都需要占用2个字节的存储空间（Unicode扩展区的一些汉字存储需要4个字节）

​ UTF-32编码中，世界上任何字符的存储都需要占用4个字节的存储空间。

1.4为什么要用utf8mb4：

​ 随着互联网的发展，产生了许多新类型的字符，例如emoji这种类型的符号，也就是我们通常在聊天时发的小黄脸表情，这种字符的出现不在基本多平面的Unicode字符之中，导致无法在MySQL中使用utf8存储，MySQL于是对utf8字符进行了扩展，增加了utf8mb4这个编码。

1.5 按照数据库大小的储存最优思想

​ 如果不用126之后的utf8mb4扩展码，使用utf8能够节省一定空间 （编码表）： https://blog.csdn.net/leelyliu/article/details/52879685

​ 但是utf8mb4拥有目前来说比较好的兼容性，因情况取舍

1.6 MySQL 中字符集相关变量：

​ character_set_client：客户端请求数据的字符集

​ character_set_connection：从客户端接收到数据，然后传输的字符集

​ character_set_database：默认数据库的字符集，无论默认数据库如何改变，都是这个字符集；如果没有默认数据库，那就使用 character_set_server指定的字符集，这个变量建议由系统自己管理，不要人为定义。 **character_set_filesystem：**把操作系统上的文件名转化成此字符集，即把 character_set_client转换character_set_filesystem， 默认binary是不做任何转换的

​ character_set_results：结果集的字符集

​ **character_set_server：**数据库服务器的默认字符集

​ **character_set_system：**存储系统元数据的字符集，总是 utf8，不需要设置

1.7创建指定的字符集数据库：

1.8其他：

​ 对于 CHAR 类型数据，utf8mb4 会多消耗一些空间，根据 Mysql 官方建议，使用VARCHAR替代CHAR。

2.MySQL的排序字符集（collation）：

2.1 版本和默认：

​ 以前都默认为utf8_general_ci

​ Mysql8.0以上默认使用utf8mb4_0900_ai_ci

2.2 常用的utf8排序字符集：

​ utf8_general_ci 不区分大小写，这个你在注册用户名和邮箱的时候就要使用。

​ utf8_general_cs 区分大小写，如果用户名和邮箱用这个 就会照成不良后果

​ utf8_bin:字符串每个字符串用二进制数据编译存储。 区分大小写，而且可以存二进制的内容

​ utf8_unicode_ci和utf8_general_ci对中、英文来说没有实质的差别。

​ utf8_general_ci校对速度快，但准确度稍差。（准确度够用，一般建库选择这个）

​ utf8_unicode_ci准确度高，但校对速度稍慢。

2.3常用的utf8mb4排序字符集：

2.3.1 utf8mb4_general_ci：
​ ci即case insensitive，不区分大小写。没有实现Unicode排序规则，在遇到某些特殊语言或者字符集，排序结果可能不一致，但是，在绝大多数情况下，这些特殊字符的顺序并不需要那么精确。另外，在比较和排序的时候速度更快。

2.3.2 utf8mb4_bin：
​ 将字符串每个字符用二进制数据编译存储，区分大小写，而且可以存二进制的内容。

2.3.3 utf8mb4_unicode_ci：
不区分大小写，基于标准的Unicode来排序和比较，能够在各种语言之间精确排序，在特殊情况下，Unicode排序规则为了能够处理特殊字符的情况，实现了略微复杂的排序算法，所以兼容度比较高，但是性能不高。

2.4 排序规则概念：

​ 定义：是指对指定字符集下不同字符的比较规则。排序规则有以下特征：

​ 它和字符集（CHARSET）相关

​ 每种字符集都有多种它支持的排序规则

​ 每种字符集都会默认指定一种排序规则为默认值。

2.5 排序规则作用：

排序规则指定后，它会影响我们使用 ORDER BY语句查询的结果顺序，会影响到 WHERE条件中大于小于号的筛选结果，会影响 DISTINCT、GROUP BY、HAVING 语句的查询结果。另外，mysql 建索引的时候，如果索引列是字符类型，也会影响索引创建，只不过这种影响我们感知不到。总之，凡是涉及到字符类型比较或排序的地方，都和排序规则有关。

2.6 排序规则按照库、表、列等级别设置及优先级：

看第四大点

3. 对于mysql字符集和排序字符集的命令作用域（设置数据库编码的方式）：

3.1 基于session会话级别设置mysql的字符集与字符排序规则：

​ 命令：

set       character_set_database=utf8;
set       character_set_server=utf8;

​ 作用范围：

​ 本次会话，只在这个命令窗口有效

3.2 基于全局gloable设置MySQL的字符集与字符排序规则：
命令：

set global  character_set_database=utf8;
set global  character_ser_server=utf8;

作用范围：

​ 本次服务有效，重启服务失效

3.3 永久性设置MySQL的字符集与字符排序规则：

​ 在mysql的配置文件my.ini中修改：

​ 作用范围：永久有效

4. 按照MySQL实例级别、库级别、表级别、列级别以及SQL指定 设置排序优先级：

4.1mysql实例级别：

​ 可以通过修改mysql的配置文件 my.ini来修改相应的排序规则，修改好后，重启mysql服务。

​ 查看mysql实例级别的字符集和排序规则：

show variables       like '%character%';
show variables       like '%collation%';

4.2 库级别设置：

 在创建数据库的时候指定数据集和排序规则

CREATE DATABASE TESTDB 
DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

​ 查看库级别排序规则：

show create database testdb;

4.3 表级别设置：

​ 在创建表的时候指定表的数据集和排序规则：

use testdb;
            
CREATE TABLE user(
    `id` int(11) NOT NULL,
    `name` varchar(255) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

​ 查看表级别排序规则：

show table status from testdb like 'user';
//testdb 为数据库名， user 为要查看的表名

4.4 列级别设置：

​ 在创建表的时候指定列的数据集和排序规则：

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

​ 查看列级别排序规则：

show full columns from user;

4.5 SQL指定设置：

​ SQL语句中指定排序规则：

SELECT id, name FROM `user` 
ORDER BY name COLLATE utf8mb4_unicode_ci;

​ 排序规则优先级：

优先级顺序是 SQL语句 > 列级别设置 > 表级别设置 > 库级别设置 > 实例级别设置

​ 也就是说，如果SQL语句中指定了排序规则，则以其指定为准，否则以下一级为准（也就是列级别），如果列级别没有指定，默认是继承表级别的设置，以此类推。