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语句中指定了排序规则,则以其指定为准,否则以下一级为准(也就是列级别),如果列级别没有指定,默认是继承表级别的设置,以此类推。