【MySQL从入门到精通】【高级篇】(一)字符集的修改与底层原理

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 今天正式开始MySQL的学习,基础部分的学习先跳过,直接进入高级部分的学习。本文主要参考B站中的MySQL数据库教程天花板,mysql安装到mysql高级,强!硬!

1. 简介

今天正式开始MySQL的学习,基础部分的学习先跳过,直接进入高级部分的学习。本文主要参考B站中的MySQL数据库教程天花板,mysql安装到mysql高级,强!硬!

2. 环境

环境 版本
Red Hat 4.8.5-39
MySQL 5.7

3. 字符集

3.1. 修改字符集

首先需要介绍的就是字符集,MySQL 5.7 默认的字符集是latin1,在MySQL 8.0以后默认的字符集是utf8mb4。

latin1字符集的有个问题就是不能保存中文,所以需要修改服务器默认的字符集,只需要修改 /etc/my.cnf 文件,通过命令vim /etc/my.cnf打开配置文件,在该文件中添加如下配置:

character-set-server=utf8

添加完该配置之后重启MySQL服务器。

systemctl restart mysqld.service

3.2. 查看系统字符集

首先需要通过mysql -u[用户名] -p[密码] (例如:mysql -uroot -pmysql)进入到mysql命令行,接着通过如下命令可以查看MySQL服务器的字符集编码:

show variables like '%character%';

其中:

character_set_client:服务器解码请求时使用的字符集

character_set_connection: 服务器处理请求时会把请求字符串从character_set_client转character_set_connection

character_set_database: 当前数据库的字符集

character_set_server: 服务器级别的字符集

character_set_results: 服务器香客户端返回数据时使用的字符集。

其中:服务器级别的字符集和当前数据库的字符集可能不一样。

3.3. 查看数据库的字符集

show create database [数据库名];

3.3.1. 修改数据库的字符集

alter database [数据库] character set [字符集名称];

3.4. 查看数据表的字符集

#修改数据表字符集
alter table [数据表] character set [字符集名称];
#查看数据表字符集
show create database [数据库名];

4. 各级别的字符集

MySQL有4个级别的字符集和比较规整,分别是:

服务器级别:由character_set_server指定

数据库级别:数据库级别的字符集默认跟随服务器级别的字符集,也可以自行指定不同的字符集

表级别:表级别的字符集默认跟随数据库级别的字符集,也可以自行指定不同的字符集

列级别:列级别的字符集默认根据表级别的字符集,也可以自行指定不同的字符集

4.1. 服务器级别

character_set_server: 服务器级别的字符集

我们可以在启动服务器程序时通过启动选项或者在服务器程序运行过程中使用set语句修改这两个变量的值,比如我们可以在配置文件/etc/my.cnf中这样写:

character-set-server=utf8
collation-server=utf8_unicode_ci

4.2. 数据库级别

character_set_database : 当前数据库的字符集

我们在创建和修改数据库的时候可以指定字符集和比较规则,具体的语法如下:

CREATE DATABASE 数据库名
    [[DEFAULT] CHARACTER SET 字符集名称]
  [[DEFAULT] COLLATE 比较规则名称]
ALTER DATABASE 数据库名
    [[DEFAULT] CHARACTER SET 字符集名称]
  [[DEFAULT] COLLATE 比较规则名称]

其中的DEFAULT 可以省略,并不影响语句的语义,比如:

mysql> create database demodb1
    -> character set utf8
    -> collate utf8_unicode_ci;
Query OK, 1 row affected (0.01 sec)

4.3. 表级别

我们也可以在创建和修改表的时候指定表的字符集和比较规则,语法如下:

CREATE TABLE 表名(列的信息)
    [[DEFAULT] CHARACTER SET 字符集名称]
  [[DEFAULT] COLLATE 比较规则名称]
ALTER TABLE 表名(列的信息)
    [[DEFAULT] CHARACTER SET 字符集名称]
  [[DEFAULT] COLLATE 比较规则名称]

创建表与创建数据库类似,其中DEFAULT也可以省略。比如;

mysql> create table t1(id int,name varchar(50))
    -> character set utf8mb4
    -> collate utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.01 sec)

4.4. 列级别

对于存储字符串的列,同一个表中的不同列也可以有不同的字符集和比较规则,我们在创建和修改列定义的时候可以指定该列的字符集和比较规则,语法如下:

CREATE TABLE 表名(
  列名 字符串类型 [CHARACTER SET 字符集名称][COLLATE 比较规则名称],
  其他列....
)
ALTER TABLE 表名 MODIFY COLUMN 列名 字符串类型 [CHARACTER SET 字符集名称][COLLATE 比较规则名称];

比如我们修改一下表 t1 中列id的字符集合比较规则可以这么写:

ALTER TABLE t1 MODIFY COLUMN `id` int(11)  CHARACTER SET utf8 COLLATE utf8_unicode_ci;

最佳的实践是:设定服务器级别的编码是utf8即可,数据库以及数据表保持一致。

5. 字符集与比较规则

5.1. utf8与utf8mb4

utf8 字符集表示一个字符需要使用1~4个字节,但是我们常用的一些字符使用1~3 个字节就可以表示了,而字符集表示一个字符所用的最大字节长度,在某些方面会影响系统的存储和性能,所以涉及MySQL的设计者偷偷定义了两个概念:

1.utf8mb3 : 阉割过的utf8字符集,只使用1~3 个字节表示字符

2.utf8mb4: 正宗的utf8字符集,使用1~4个字节表示字符。

在MySQL中utf8是utf8mb3的别名,所以之后在MySQL中提到utf8就意味着使用1~3个字节表示一个字符。如果大家有使用4字节编码一个字符的情况,比如存储一些emoji表情,那么请使用utf8mb4。

通过如下指令可以查看MySQL支持的字符集:

SHOW CHARSET;


5.2. 比较规则

上表中,MySQL版本一共支持41种字符集,其中的Default collation 列表示这种字符集中一种默认的比较规则,里面包含着该比较规则主要作用于哪种语言,比如:utf8_polish_ci 表示以波兰语的规则比较,utf8_general_ci 是一种通用的比较规则。

后缀表示该比较规则是否区分语言中的重音,大小写,具体如下:

后缀 英文释义 描述
_ai accent insensitive 不区分重音
_as accent sensitive 区分重音
_ci case insensitve 不区分大小写
_cs case sensitive 区分大小写
_bin binary 以二进制方式比较

最后一列Maxlen,它代表该种字符集表示一个字符最多需要几个字节。

字符集名称 Maxlen
ascill 1
latin1 1
gb2312 2
gbk 2
utf8 3
utf8mb4 4

常用操作:

#查看gbk字符集的比较规则
SHOW COLLATION LIKE 'gbk%'
#查看utf8字符集的比较规则
SHOW COLLATION LIKE 'utf8%'

总结

本文还是一篇基础文,详细介绍了MySQL中的字符集以及比较规则。


相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
21天前
|
存储 SQL 关系型数据库
MySQL进阶突击系列(03) MySQL架构原理solo九魂17环连问 | 给大厂面试官的一封信
本文介绍了MySQL架构原理、存储引擎和索引的相关知识点,涵盖查询和更新SQL的执行过程、MySQL各组件的作用、存储引擎的类型及特性、索引的建立和使用原则,以及二叉树、平衡二叉树和B树的区别。通过这些内容,帮助读者深入了解MySQL的工作机制,提高数据库管理和优化能力。
|
3天前
|
SQL 关系型数据库 MySQL
MySQL事务日志-Undo Log工作原理分析
事务的持久性是交由Redo Log来保证,原子性则是交由Undo Log来保证。如果事务中的SQL执行到一半出现错误,需要把前面已经执行过的SQL撤销以达到原子性的目的,这个过程也叫做"回滚",所以Undo Log也叫回滚日志。
MySQL事务日志-Undo Log工作原理分析
|
22天前
|
缓存 关系型数据库 MySQL
MySQL 索引优化与慢查询优化:原理与实践
通过本文的介绍,希望您能够深入理解MySQL索引优化与慢查询优化的原理和实践方法,并在实际项目中灵活运用这些技术,提升数据库的整体性能。
60 5
|
1月前
|
SQL 存储 关系型数据库
MySQL进阶突击系列(01)一条简单SQL搞懂MySQL架构原理 | 含实用命令参数集
本文从MySQL的架构原理出发,详细介绍其SQL查询的全过程,涵盖客户端发起SQL查询、服务端SQL接口、解析器、优化器、存储引擎及日志数据等内容。同时提供了MySQL常用的管理命令参数集,帮助读者深入了解MySQL的技术细节和优化方法。
|
3月前
|
存储 缓存 关系型数据库
MySQL事务日志-Redo Log工作原理分析
事务的隔离性和原子性分别通过锁和事务日志实现,而持久性则依赖于事务日志中的`Redo Log`。在MySQL中,`Redo Log`确保已提交事务的数据能持久保存,即使系统崩溃也能通过重做日志恢复数据。其工作原理是记录数据在内存中的更改,待事务提交时写入磁盘。此外,`Redo Log`采用简单的物理日志格式和高效的顺序IO,确保快速提交。通过不同的落盘策略,可在性能和安全性之间做出权衡。
1751 14
MySQL事务日志-Redo Log工作原理分析
|
3月前
|
存储 关系型数据库 MySQL
MySQL主从复制原理和使用
本文介绍了MySQL主从复制的基本概念、原理及其实现方法,详细讲解了一主两从的架构设计,以及三种常见的复制模式(全同步、异步、半同步)的特点与适用场景。此外,文章还提供了Spring Boot环境下配置主从复制的具体代码示例,包括数据源配置、上下文切换、路由实现及切面编程等内容,帮助读者理解如何在实际项目中实现数据库的读写分离。
151 1
MySQL主从复制原理和使用
|
3月前
|
缓存 算法 关系型数据库
Mysql(3)—数据库相关概念及工作原理
数据库是一个以某种有组织的方式存储的数据集合。它通常包括一个或多个不同的主题领域或用途的数据表。
99 5
Mysql(3)—数据库相关概念及工作原理
|
3月前
|
SQL 关系型数据库 MySQL
Mysql中搭建主从复制原理和配置
主从复制在数据库管理中广泛应用,主要优点包括提高性能、实现高可用性、数据备份及灾难恢复。通过读写分离、从服务器接管、实时备份和地理分布等机制,有效增强系统的稳定性和数据安全性。主从复制涉及I/O线程和SQL线程,前者负责日志传输,后者负责日志应用,确保数据同步。配置过程中需开启二进制日志、设置唯一服务器ID,并创建复制用户,通过CHANGE MASTER TO命令配置从服务器连接主服务器,实现数据同步。实验部分展示了如何在两台CentOS 7服务器上配置MySQL 5.7主从复制,包括关闭防火墙、配置静态IP、设置域名解析、配置主从服务器、启动复制及验证同步效果。
Mysql中搭建主从复制原理和配置
|
3月前
|
SQL 关系型数据库 MySQL
阿里面试:MYSQL 事务ACID,底层原理是什么? 具体是如何实现的?
尼恩,一位40岁的资深架构师,通过其丰富的经验和深厚的技術功底,为众多读者提供了宝贵的面试指导和技术分享。在他的读者交流群中,许多小伙伴获得了来自一线互联网企业的面试机会,并成功应对了诸如事务ACID特性实现、MVCC等相关面试题。尼恩特别整理了这些常见面试题的系统化解答,形成了《MVCC 学习圣经:一次穿透MYSQL MVCC》PDF文档,旨在帮助大家在面试中展示出扎实的技术功底,提高面试成功率。此外,他还编写了《尼恩Java面试宝典》等资料,涵盖了大量面试题和答案,帮助读者全面提升技术面试的表现。这些资料不仅内容详实,而且持续更新,是求职者备战技术面试的宝贵资源。
阿里面试:MYSQL 事务ACID,底层原理是什么? 具体是如何实现的?
|
3月前
|
存储 SQL 关系型数据库
mysql中主键索引和联合索引的原理与区别
本文详细介绍了MySQL中的主键索引和联合索引原理及其区别。主键索引按主键值排序,叶节点仅存储数据区,而索引页则存储索引和指向数据域的指针。联合索引由多个字段组成,遵循最左前缀原则,可提高查询效率。文章还探讨了索引扫描原理、索引失效情况及设计原则,并对比了InnoDB与MyISAM存储引擎中聚簇索引和非聚簇索引的特点。对于优化MySQL性能具有参考价值。