MySQL 字符集相关梳理
故事背景
今天在做项目数据迁移,涉及到将 A 表中的某个字段复制到 B 表,本地(MAC Mysql 8.0.25)完全没问题,但是在服务器上执行迁移文件时报错:
经过一番查阅资料发现是由于 emoji 字符导致的,由于服务器中 Mysql 版本为 5.6 在 A 表中设置的字符集为 utf8mb4 而 B 表中默认使用 utf8,utf8 最大只支持三个字节,而 emoji 表情字节占用大于三个,因此导入失败。下面关于 MySQL 字符相关做如下梳理:
MySQL 字符集
版本与字符集
MySQL5.5 版本在 my.ini 上修改 character_set_server 为 utf8
MySQL8.0 以上版本默认 character_set_server 为 utf8mb4
utf8mb4 和 utf8 的区别
-
utf8mb4 是 utf8 的扩展版,支持更多的字码形式
-
utf8mb4 最大支持 4 个字节,utf8 最大支持三个字节
其他编码问题
ASCII 编码:一个英文字母(不区分大小写)占用一个字节的空间,一个中文汉字占用两个字节的空间。一个二进制的数字序列,在计算机中作为一个数字单元存储是,一般为 8 位二进制,换算为十进制,最小值0,最大值255
UTF-8:一个英文字符占用一个字节的存储空间,一个中文(含繁体)占用三个字节的存储空间。
Unicode(万国码):一个英文占用两个字节的存储空间,一个中文(含繁体)占用两个字节的存储空间
UTF-16:一个英文字符或一个汉字字符存储都需要占用两个字节的存储空间
UTF-32L:在世界上任何字符的存储都需要占用4个字节的存储空间
Mysql 查看和修改数据库字符集方法
查看字符集
- 查看 Mysql 数据库服务器和数据库字符集
# 方法一:查看数据库字符集
show variables like '%character%';
# 方法二:查看数据库排序字符集
show variables like '%collation%';
备注:创建数据库时指定字符集:create database mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
- 查看 Mysql 支持的所有字符集
show charset;
- 查看表的字符集
show table status from 库名 like '表名';
- 查看表中所有列的字符集
show full columns from '表名';
设置字符集
-
设置字符集
设置字符集一般有两种方法,一种是创建表的时候设置字符集,另一种是表建成之后修改字符集
-
创建的时候指定字符集
create database dbName default character set=字符集;
-
创建表时单独指定字符集
create table 表名 default character set=字符集;
-
修改字符集
-
修改全局字符集
/*建立连接使用的编码*/ set character_set_connection=utf8; /*数据库的编码*/ set character_set_database=utf8; /*结果集的编码*/ set character_set_results=utf8; /*数据库服务器的编码*/ set character_set_server=utf8; /*存储系统元数据的字符集*/ set character_set_system=utf8; /*客户端请求数据的字符集*/ set character_set_client=utf8; set collation_connection=utf8; set collation_database=utf8; set collation_server=utf8;
-
修改数据库字符集
alter database 库名 default characher set 字符集;
-
修改表的字符集
alter table 表名 convert to characher set 字符集;
-
修改字段字符集
alter table 表名 modify 字段名 字段属性 character set 字符集;
MySQl 排序字符集
以前默认为 utf8_general_ci,MySQL 8.0 以上默认 utf8mb4_0900_ai_ci
常用的 utf8 排序字符集
-
utf8_general_ci 不区分大小写,这个你在注册用户名和邮箱的时候就要使用。
-
utf8_general_cs 区分大小写,如果用户名和邮箱用这个 就会照成不良后果
-
utf8_bin:字符串每个字符串用二进制数据编译存储。 区分大小写,而且可以存二进制的内容
-
utf8_unicode_ci和utf8_general_ci对中、英文来说没有实质的差别。
-
utf8_general_ci校对速度快,但准确度稍差。(准确度够用,一般建库选择这个)
-
utf8_unicode_ci准确度高,但校对速度稍慢。
常用的 utf8mb4 排序字符集
cii即case insensitive,不区分大小写。没有实现Unicode排序规则,在遇到某些特殊语言或者字符集,排序结果可能不一致,但是,在绝大多数情况下,这些特殊字符的顺序并不需要那么精确。另外,在比较和排序的时候速度更快。
-
utf8mb4_bin:将字符串每个字符用二进制数据编译存储,区分大小写,而且可以存二进制的内容。
-
utf8mb4_unicode_ci:不区分大小写,基于标准的Unicode来排序和比较,能够在各种语言之间精确排序,在特殊情况下,Unicode排序规则为了能够处理特殊字符的情况,实现了略微复杂的排序算法,所以兼容度比较高,但是性能不高。
排序规则概念
定义:是指对指定字符集下不同字符的比较规则。排序规则有以下特征:
- 它和字符集(CHARSET)相关
- 每种字符集都有多种它支持的排序规则
- 每种字符集都会默认指定一种排序规则为默认值。
排序规则作用
排序规则指定后,它会影响我们使用 ORDER BY语句查询的结果顺序,会影响到 WHERE条件中大于小于号的筛选结果,会影响 DISTINCT、GROUP BY、HAVING 语句的查询结果。另外,mysql 建索引的时候,如果索引列是字符类型,也会影响索引创建,只不过这种影响我们感知不到。总之,凡是涉及到字符类型比较或排序的地方,都和排序规则有关。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战