Mysql高级

字符集与比较规则

utf8与utf8mb4

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

• utf8mb3：阉割过的utf8字符集，只使用1-3个字节表示字符
• utf8mb4：正宗的utf8字符集，使用1-4个字节表示字符
  在MySQL中utf8是utf8mb3的别名，所以之后在MySQL中提到的utf8就意味着使用1-个字节来表示一个字符，如果有使用4个字节编码一个字符的情况，如emoji表情，需要使用utf8mb4。

请求到响应过程中字符集的变化

从客户端发往服务器的请求本质上就是一个字符串，服务器向客户端返回的结果本质上也是一个字符串，而字符串其实是使用某种字符集编码的二进制数据。这个字符串可不是使用一种字符集的编码方式一条道走到黑的，从发送请求到返回结果这个过程中伴随着多次字符集的转换，在这个过程中会用到3个系统变量：

• character_set_client 服务器解码请求时使用的字符集
• character_set_connection 服务器处理请求时会把请求字符串从character_set_client转为character_set_connection
• character_set_results 服务器向客户端返回数据时使用的字符集
  这几个系统变量的查看方式：show variables like ‘%character%’;
  

MySQL数据目录

目录位置

• 数据存储目录：/var/lib/mysql，可通过show variables like ‘datadir’ 查看
• 命令查看目录：/usr/bin
• 配置文件目录：/etc/my.cnf

数据库和文件系统的关系

像InnoDB，MyISAM这样的存储引擎都是把表存储在磁盘上的，操作系统用来管理磁盘的结构被称为文件系统，所以用专业一点的话来表述就是InnoDB，MyISAM这样的存储引擎都是把表存储在文件系统上的，当我们想读取数据的时候，这些存储引擎会从文件系统中把数据读出来返回给我们，当我们想写入数据的时候，这些存储引擎会把这些数据又写回文件系统

默认数据库

• mysql
  MySQL系统自带的核心数据库，它存储了MySQL的用户账户和权限信息，一些存储过程，事件的定义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等
• information_schema
  这个数据库保存着MySQL服务器维护的所有其他数据库的信息，比如有哪些表，哪些视图，哪些触发器，哪些列，哪些索引，这些信息并不是真实的用户数据，而是一些描述信息，有时候也称之为元数据，其中提供了一些以innodb_sys开头的表，用于表示内部系统表
• performance_schema
  这个数据库主要保存MySQL服务器运行过程中的一些状态信息，可以用来监控MySQL服务的各类性能指标。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况等信息
• sys
  这个数据库主要通过视图的形式把information_schema和performance_schema结合起来，帮助系统管理员和开发人员监控MySQL的技术性能

数据存放位置

版本5.7

• innode
  该版本默认会放在/var/lib/mysql/数据库名/表名，以.ibd文件存储，即独立表空间，也可以放在数据库的目录下的ibdatal文件中，即系统表空间，我们可以自己指定使用哪一个来存储数据，这个功能由启动参数innodb_file_per_table控制。/var/lib/mysql/数据库名/表名目录下还有.frm文件存储表结构，.opt存储库的编码，比较规则等信息。
• myisam
  在独立表空间中有.frm，.MYD(数据文件)，.MYI(索引文件)三个文件

版本8.0

• innode
  .frm文件的信息放在了.ibd中，.opt文件的配置信息也单独放在了.ibd中
• myisam
  .frm变为了.sdi文件，其他与5.7一样

视图

视图只是一个虚拟的表，新增一个视图只会生成.frm文件

用户与权限管理

新增用户

使用CREATE USER语句来创建新用户，必须拥有CREATE USER权限，每添加一个用户，MySQL.user表中添加一条新记录，但是新创建的账户没有任何权限，在user表中是user和host联合主键唯一标识一个用户。

CREATE USER 用户名[@'host'] [IDENTIFIED BY '密码'];

修改用户

UPDATE mysql.user SET USER='修改名' WHERE user='原名';
FLUSH PRIVILEGES;

删除用户

DROP USER 用户名[@'host'];

修改当前登录用户的密码

ALTER USER USER() IDENTIFIED BY '新密码';

修改其他用户的密码

ALTER USER user IDENTIFIED BY '新密码';

授予权限

GRANT 权限1,权限2,... ON 数据库名.表名称 TO 用户名@用户地址 [IDENTIIED BY '密码']

GRANT ALL PRIVILEGES ON *.* TO 用户名

如果需要赋予包括GRANT的权限，添加参数"WITH GRANT OPTION"选项即可。

权限分类

查看权限

SHOW GRANTS;

SHOW GRANTS FOR 'user'@'主机地址';

回收权限

REVOKE 权限1,权限2,... ON 数据库名称.表名称 FROM 用户名@用户地址；

用户权限执行流程

角色管理

角色理解

角色是在MySQL8.0中引入的新功能，在MySQL中，角色是权限的集合，可以为角色添加或者移除权限。

创建角色

CREATE ROLE 'role_name'[@'host_name'] [,'role_name'[@'host_name']]...

给角色赋予权限

GRANT privileges ON database.table_name TO 'role_name'[@'host_name'];

回收角色权限

REVOKE privileges ON