第16章 变量、流程控制与游标
1. 变量
1.1 系统变量
1.1.1 系统变量分类
系统变量分为全局系统变量（需要添加global关键字）以及会话系统变量（需要添加session关键字），有时也把全局系统变量简称为全局变量，有时也把会话系统变量称为local变量。如果不写，默认会话级别。静态变量（在 MySQL 服务实例运行期间它们的值不能使用 set 动态修改）属于特殊的全局系统变量。
- 全局系统变量针对于所有会话（连接）有效，但
不能跨重启 - 会话系统变量仅针对于当前会话（连接）有效。会话期间，当前会话对某个会话系统变量值的修改，不会影响其他会话同一个会话系统变量的值。
- 会话1对某个全局系统变量值的修改会导致会话2中同一个全局系统变量值的修改。
1.1.2 查看系统变量
- 查看所有或部分系统变量
#查看所有全局变量 SHOW GLOBAL VARIABLES; #查看所有会话变量 SHOW SESSION VARIABLES; #或 SHOW VARIABLES;
#查看满足条件的部分系统变量。 SHOW GLOBAL VARIABLES LIKE '%标识符%'; #查看满足条件的部分会话变量 SHOW SESSION VARIABLES LIKE '%标识符%';
- 查看指定系统变量
作为 MySQL 编码规范，MySQL 中的系统变量以两个“@”开头，其中“@@global”仅用于标记全局系统变量，“@@session”仅用于标记会话系统变量。“@@”首先标记会话系统变量，如果会话系统变量不存在，则标记全局系统变量。
#查看指定的系统变量的值 SELECT @@global.变量名; #查看指定的会话变量的值 SELECT @@session.变量名; #或者 SELECT @@变量名;
- 修改系统变量的值
方式1：修改MySQL 配置文件 ，继而修改MySQL系统变量的值（该方法需要重启MySQL服务）
方式2：在MySQL服务运行期间，使用“set”命令重新设置系统变量的值
#为某个系统变量赋值 #方式1： SET @@global.变量名=变量值; #方式2： SET GLOBAL 变量名=变量值; #为某个会话变量赋值 #方式1： SET @@session.变量名=变量值; #方式2： SET SESSION 变量名=变量值;
1.2 用户变量
1.2.1 用户变量分类
用户变量是用户自己定义的，作为 MySQL 编码规范，MySQL 中的用户变量以一个“@”开头。根据作用范围不同，又分为会话用户变量和局部变量。
- 会话用户变量：作用域和会话变量一样，只对
当前连接会话有效。 - 局部变量：只在 BEGIN 和 END 语句块中有效。局部变量只能在
存储过程和函数中使用。
1.2.2 会话用户变量
- 变量的定义
#方式1：“=”或“:=” SET @用户变量 = 值; SET @用户变量 := 值; #方式2：“:=” 或 INTO关键字 SELECT @用户变量 := 表达式 [FROM 等子句]; SELECT 表达式 INTO @用户变量 [FROM 等子句];
- 查看用户变量的值 （查看、比较、运算等）
SELECT @用户变量
1.2.3 局部变量
定义：可以使用DECLARE语句定义一个局部变量
作用域：仅仅在定义它的 BEGIN ... END 中有效
位置：只能放在 BEGIN ... END 中，而且只能放在第一句
BEGIN #声明局部变量 DECLARE 变量名1 变量数据类型 [DEFAULT 变量默认值]; DECLARE 变量名2,变量名3,... 变量数据类型 [DEFAULT 变量默认值]; #为局部变量赋值 SET 变量名1 = 值; SELECT 值 INTO 变量名2 [FROM 子句]; #查看局部变量的值 SELECT 变量1,变量2,变量3; END
1.定义变量
DECLARE 变量名 类型 [default 值]; # 如果没有DEFAULT子句，初始值为NULL
2.变量赋值
方式1：一般用于赋简单的值
SET 变量名=值; SET 变量名:=值;
方式2：一般用于赋表中的字段值
SELECT 字段名或表达式 INTO 变量名 FROM 表;
3.使用变量（查看、比较、运算等）
SELECT 局部变量名;
1.2.4 对比会话用户变量与局部变量
作用域 |
定义位置 |
语法 |
|
会话用户变量 |
当前会话 |
会话的任何地方 |
加@符号，不用指定类型 |
局部变量 |
定义它的BEGIN END中 |
BEGIN END的第一句话 |
一般不用加@,需要指定类型 |
2. 定义条件与处理程序
定义条件是事先定义程序执行过程中可能遇到的问题，处理程序定义了在遇到问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。这样可以增强存储程序处理问题的能力，避免程序异常停止运行。
说明：定义条件和处理程序在存储过程、存储函数中都是支持的。
在存储过程中未定义条件和处理程序，且当存储过程中执行的SQL语句报错时，MySQL数据库会抛出错误，并退出当前SQL逻辑，不再向下继续执行。
2.1 定义条件
DECLARE 错误名称 CONDITION FOR 错误码（或错误条件）
错误码的说明：
MySQL_error_code和sqlstate_value都可以表示MySQL的错误。
- MySQL_error_code是数值类型错误代码。
- sqlstate_value是长度为5的字符串类型错误代码。
- 例如，在ERROR 1418 (HY000)中，1418是MySQL_error_code，'HY000'是sqlstate_value。
#使用MySQL_error_code DECLARE Field_Not_Be_NULL CONDITION FOR 1048; #使用sqlstate_value DECLARE Field_Not_Be_NULL CONDITION FOR SQLSTATE '23000';
2.2 定义处理程序
DECLARE 处理方式 HANDLER FOR 错误类型 处理语句
- 处理方式：处理方式有3个取值：CONTINUE、EXIT、UNDO。
CONTINUE：表示遇到错误不处理，继续执行。EXIT：表示遇到错误马上退出。UNDO：表示遇到错误后撤回之前的操作。MySQL中暂时不支持这样的操作。
- 错误类型（即条件）可以有如下取值：
SQLSTATE '字符串错误码'：表示长度为5的sqlstate_value类型的错误代码；MySQL_error_code：匹配数值类型错误代码；错误名称：表示DECLARE ... CONDITION定义的错误条件名称。SQLWARNING：匹配所有以01开头的SQLSTATE错误代码；NOT FOUND：匹配所有以02开头的SQLSTATE错误代码；SQLEXCEPTION：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码；
- 处理语句：如果出现上述条件之一，则采用对应的处理方式，并执行指定的处理语句。语句可以是像“
SET 变量 = 值”这样的简单语句，也可以是使用BEGIN ... END编写的复合语句。
定义处理程序的几种方式，代码如下：
#方法1：捕获sqlstate_value DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'NO_SUCH_TABLE'; #方法2：捕获mysql_error_value DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'NO_SUCH_TABLE'; #方法3：先定义条件，再调用 DECLARE no_such_table CONDITION FOR 1146; DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info = 'NO_SUCH_TABLE'; #方法4：使用SQLWARNING DECLARE EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR'; #方法5：使用NOT FOUND DECLARE EXIT HANDLER FOR NOT FOUND SET @info = 'NO_SUCH_TABLE'; #方法6：使用SQLEXCEPTION DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR';
3. 流程控制
3.1 分支结构之 IF
IF 表达式1 THEN 操作1 [ELSEIF 表达式2 THEN 操作2]…… [ELSE 操作N] END IF
3.2 分支结构之 CASE
CASE 语句的语法结构1：
#情况一：类似于switch CASE 表达式 WHEN 值1 THEN 结果1或语句1(如果是语句，需要加分号) WHEN 值2 THEN 结果2或语句2(如果是语句，需要加分号) ... ELSE 结果n或语句n(如果是语句，需要加分号) END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）
CASE 语句的语法结构2：
#情况二：类似于多重if CASE WHEN 条件1 THEN 结果1或语句1(如果是语句，需要加分号) WHEN 条件2 THEN 结果2或语句2(如果是语句，需要加分号) ... ELSE 结果n或语句n(如果是语句，需要加分号) END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）
3.3 循环结构之LOOP
LOOP循环语句用来重复执行某些语句。LOOP内的语句一直重复执行直到循环被退出（使用LEAVE子句），跳出循环过程。
[loop_label:] LOOP 循环执行的语句 END LOOP [loop_label]
3.4 循环结构之WHILE
[while_label:] WHILE 循环条件 DO 循环体 END WHILE [while_label];
3.5 循环结构之REPEAT
[repeat_label:] REPEAT 循环体的语句 UNTIL 结束循环的条件表达式 END REPEAT [repeat_label]
对比三种循环结构：
1、这三种循环都可以省略名称，但如果循环中添加了循环控制语句（LEAVE或ITERATE）则必须添加名称。 2、 LOOP：一般用于实现简单的"死"循环；WHILE：先判断后执行；REPEAT：先执行后判断，无条件至少执行一次。
3.6 跳转语句之LEAVE语句
LEAVE语句：可以用在循环语句内，或者以 BEGIN 和 END 包裹起来的程序体内，表示跳出循环或者跳出程序体的操作。如果你有面向过程的编程语言的使用经验，你可以把 LEAVE 理解为 break。
LEAVE 标记名
3.7 跳转语句之ITERATE语句
ITERATE语句：只能用在循环语句（LOOP、REPEAT和WHILE语句）内，表示重新开始循环，将执行顺序转到语句段开头处。如果你有面向过程的编程语言的使用经验，你可以把 ITERATE 理解为 continue，意思为“再次循环”。
ITERATE label
4. 游标
4.1 什么是游标（或光标）
游标，提供了一种灵活的操作方式，让我们能够对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作的数据结构。游标让 SQL 这种面向集合的语言有了面向过程开发的能力。
4.2 使用游标步骤
游标必须在声明处理程序之前被声明，并且变量和条件还必须在声明游标或处理程序之前被声明。
第一步，声明游标
DECLARE cursor_name CURSOR FOR select_statement;
第二步，打开游标
OPEN cursor_name
第三步，使用游标（从游标中取得数据）
FETCH cursor_name INTO var_name [, var_name] ...
注意：游标的查询结果集中的字段数，必须跟 INTO 后面的变量数一致，否则，在存储过程执行的时候，MySQL 会提示错误。
第四步，关闭游标
CLOSE cursor_name
当我们使用完游标后需要关闭掉该游标。因为游标会占用系统资源 ，如果不及时关闭，游标会一直保持到存储过程结束，影响系统运行的效率。
4.3 小结
游标是 MySQL 的一个重要的功能，为逐条读取结果集中的数据，提供了完美的解决方案。跟在应用层面实现相同的功能相比，游标可以在存储程序中使用，效率高，程序也更加简洁。
但同时也会带来一些性能问题，比如在使用游标的过程中，会对数据行进行加锁，这样在业务并发量大的时候，不仅会影响业务之间的效率，还会消耗系统资源，造成内存不足，这是因为游标是在内存中进行的处理。
建议：养成用完之后就关闭的习惯，这样才能提高系统的整体效率。
补充：MySQL 8.0的新特性—全局变量的持久化
使用SET GLOBAL语句设置的变量值只会临时生效。数据库重启后，服务器又会从MySQL配置文件中读取变量的默认值。 MySQL 8.0版本新增了SET PERSIST命令。
SET PERSIST global max_connections = 1000;
MySQL会将该命令的配置保存到数据目录下的 mysqld-auto.cnf 文件中，下次启动时会读取该文件，用其中的配置来覆盖默认的配置文件。
第17章 触发器
1. 触发器概述
MySQL从5.0.2版本开始支持触发器。MySQL的触发器和存储过程一样，都是嵌入到MySQL服务器的一段程序。
触发器是由事件来触发某个操作，这些事件包括INSERT、UPDATE、DELETE事件。所谓事件就是指用户的动作或者触发某项行为。如果定义了触发程序，当数据库执行这些语句时候，就相当于事件发生了，就会自动激发触发器执行相应的操作。
当对数据表中的数据执行插入、更新和删除操作，需要自动执行一些数据库逻辑时，可以使用触发器来实现。
2. 触发器的创建
2.1 创建触发器语法
CREATE TRIGGER 触发器名称 {BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名 FOR EACH ROW 触发器执行的语句块;
说明：
表名：表示触发器监控的对象。BEFORE|AFTER：表示触发的时间。BEFORE 表示在事件之前触发；AFTER 表示在事件之后触发。INSERT|UPDATE|DELETE：表示触发的事件。
- INSERT 表示插入记录时触发；
- UPDATE 表示更新记录时触发；
- DELETE 表示删除记录时触发。
DELIMITER // CREATE TRIGGER before_insert BEFORE INSERT ON test_trigger FOR EACH ROW BEGIN INSERT INTO test_trigger_log (t_log) VALUES('before_insert'); END // DELIMITER ;
3. 查看、删除触发器
3.1 查看触发器
方式1：查看当前数据库的所有触发器的定义
SHOW TRIGGERS\G
方式2：查看当前数据库中某个触发器的定义
SHOW CREATE TRIGGER 触发器名
方式3：从系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息。
SELECT * FROM information_schema.TRIGGERS;
3.2 删除触发器
DROP TRIGGER IF EXISTS 触发器名称;
4. 触发器的优缺点
4.1 优点
1、触发器可以确保数据的完整性。
2、触发器可以帮助我们记录操作日志。
3、触发器还可以用在操作数据前，对数据进行合法性检查。
4.2 缺点
1、触发器最大的一个问题就是可读性差。
比如触发器中的数据插入操作多了一个字段，系统提示错误。可是，如果你不了解这个触发器，很可能会认为是更新语句本身的问题，或者是表的结构出了问题。
2、相关数据的变更，可能会导致触发器出错。
特别是数据表结构的变更，都可能会导致触发器出错，进而影响数据操作的正常运行。这些都会由于触发器本身的隐蔽性，影响到应用中错误原因排查的效率。
4.3 注意点
注意，如果在子表中定义了外键约束，并且外键指定了ON UPDATE/DELETE CASCADE/SET NULL子句，此时修改父表被引用的键值或删除父表被引用的记录行时，也会引起子表的修改和删除操作，此时基于子表的UPDATE和DELETE语句定义的触发器并不会被激活。
第18章 MySQL8其它新特性
1. 新特性1：窗口函数
1.1 窗口函数分类
MySQL从8.0版本开始支持窗口函数。窗口函数的作用类似于在查询中对数据进行分组，不同的是，分组操作会把分组的结果聚合成一条记录，而窗口函数是将结果置于每一条数据记录中。
窗口函数可以分为静态窗口函数和动态窗口函数。
- 静态窗口函数的窗口大小是固定的，不会因为记录的不同而不同；
- 动态窗口函数的窗口大小会随着记录的不同而变化。
函数分类 |
函数 |
函数说明 |
序号函数 |
ROW_NUMBER() |
顺序排序 |
RANK() |
并列排序，会跳过重复的序号，比如序号为1、1、3 |
|
DENSE_RANK() |
并列排序，不会跳过重复的序号，比如序号为1、1、2 |
|
分布函数 |
PERCENT_RANK() |
等级值百分比 |
CUME_DIST() |
累积分布值 |
|
前后函数 |
LAG(expr, n) |
返回当前行的前n行的expr的值 |
LEAD(expr, n) |
返回当前行的后n行的expr的值 |
|
首尾函数 |
FIRST_VALUE(expr) |
返回第一个expr的值 |
LAST_VALUE(expr) |
返回最后一个expr的值 |
|
其他函数 |
NTH_VALUE(expr, n) |
返回第n个expr的值 |
NTILE(n) |
将分区中的有序数据分为n个桶，记录桶编号 |
1.2 语法结构
窗口函数的语法结构是：
函数 OVER（[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）
或者是：
函数 OVER 窗口名 … WINDOW 窗口名 AS （[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）
- OVER 关键字指定函数窗口的范围。
- 如果省略后面括号中的内容，则窗口会包含满足WHERE条件的所有记录，窗口函数会基于所有满足WHERE条件的记录进行计算。
- 如果OVER关键字后面的括号不为空，则可以使用如下语法设置窗口。
- 窗口名：为窗口设置一个别名，用来标识窗口。
- PARTITION BY子句：指定窗口函数按照哪些字段进行分组。分组后，窗口函数可以在每个分组中分别执行。
- ORDER BY子句：指定窗口函数按照哪些字段进行排序。执行排序操作使窗口函数按照排序后的数据记录的顺序进行编号。
- FRAME子句：为分区中的某个子集定义规则，可以用来作为滑动窗口使用。
SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num, id, category_id, category, NAME, price, stock FROM goods;
1.3 小 结
窗口函数的特点是可以分组，而且可以在分组内排序。另外，窗口函数不会因为分组而减少原表中的行数，这对我们在原表数据的基础上进行统计和排序非常有用。
2. 新特性2：公用表表达式
公用表表达式（或通用表表达式）简称为CTE（Common Table Expressions）。CTE是一个命名的临时结果集，作用范围是当前语句。CTE可以理解成一个可以复用的子查询，当然跟子查询还是有点区别的，CTE可以引用其他CTE，但子查询不能引用其他子查询。所以，可以考虑代替子查询。
依据语法结构和执行方式的不同，公用表表达式分为普通公用表表达式和递归公用表表达式2 种。
2.1 普通公用表表达式
WITH CTE名称 AS （子查询） SELECT|DELETE|UPDATE 语句;
举例：查询员工所在的部门的详细信息。
WITH emp_dept_id AS (SELECT DISTINCT department_id FROM employees) SELECT * FROM departments d JOIN emp_dept_id e ON d.department_id = e.department_id;
2.2 递归公用表表达式
WITH RECURSIVE CTE名称 AS （子查询） SELECT|DELETE|UPDATE 语句;
案例：针对于我们常用的employees表，包含employee_id，last_name和manager_id三个字段。如果a是b的管理者，那么，我们可以把b叫做a的下属，如果同时b又是c的管理者，那么c就是b的下属，是a的下下属。
- 用递归公用表表达式中的种子查询，找出初代管理者。字段 n 表示代次，初始值为 1，表示是第一代管理者。
- 用递归公用表表达式中的递归查询，查出以这个递归公用表表达式中的人为管理者的人，并且代次的值加 1。直到没有人以这个递归公用表表达式中的人为管理者了，递归返回。
- 在最后的查询中，选出所有代次大于等于 3 的人，他们肯定是第三代及以上代次的下属了，也就是下下属了。这样就得到了我们需要的结果集。
代码实现：
WITH RECURSIVE cte AS(SELECT employee_id,last_name,manager_id,1 AS n FROM employees WHERE employee_id = 100 -- 种子查询，找到第一代领导 UNION ALL SELECT a.employee_id,a.last_name,a.manager_id,n+1 FROM employees AS a JOIN cte ON (a.manager_id = cte.employee_id) -- 递归查询，找出以递归公用表表达式的人为领导的人 ) SELECT employee_id,last_name FROM cte WHERE n >= 3;
2.3 小 结
公用表表达式的作用是可以替代子查询，而且可以被多次引用。递归公用表表达式对查询有一个共同根节点的树形结构数据非常高效，可以轻松搞定其他查询方式难以处理的查询。
第二部分 MySQL高级特性篇
第01章 Linux下MySQL的安装与使用
1. 安装前说明
1.1 查看是否安装过MySQL
- 如果你是用rpm安装, 检查一下RPM PACKAGE：
rpm -qa | grep -i mysql # -i 忽略大小写
- 检查mysql service：
systemctl status mysqld.service
1.2 MySQL的卸载
1. 关闭 mysql 服务
systemctl stop mysqld.service
2. 查看当前 mysql 安装状况
rpm -qa | grep -i mysql # 或 yum list installed | grep mysql
3. 卸载上述命令查询出的已安装程序
yum remove mysql-xxx mysql-xxx mysql-xxx mysqk-xxxx
务必卸载干净，反复执行rpm -qa | grep -i mysql确认是否有卸载残留
4. 删除 mysql 相关文件
- 查找相关文件
find / -name mysql
- 删除上述命令查找出的相关文件
rm -rf xxx
5.删除 my.cnf
rm -rf /etc/my.cnf
2. MySQL的Linux版安装
2.1 CentOS7下检查MySQL依赖
1. 检查/tmp临时目录权限（必不可少）
由于mysql安装过程中，会通过mysql用户在/tmp目录下新建tmp_db文件，所以请给/tmp较大的权限。执行 ：
chmod -R 777 /tmp
2. 安装前，检查依赖
rpm -qa|grep libaio rpm -qa|grep net-tools
2.2 CentOS7下MySQL安装过程
1. 将安装程序拷贝到/opt目录下
在mysql的安装文件目录下执行：（必须按照顺序执行）
rpm -ivh mysql-community-common-8.0.25-1.el7.x86_64.rpm rpm -ivh mysql-community-client-plugins-8.0.25-1.el7.x86_64.rpm rpm -ivh mysql-community-libs-8.0.25-1.el7.x86_64.rpm rpm -ivh mysql-community-client-8.0.25-1.el7.x86_64.rpm rpm -ivh mysql-community-server-8.0.25-1.el7.x86_64.rpm
rpm是Redhat Package Manage缩写，通过RPM的管理，用户可以把源代码包装成以rpm为扩展名的文件形式，易于安装。-i, --install 安装软件包-v, --verbose 提供更多的详细信息输出-h, --hash 软件包安装的时候列出哈希标记 (和 -v 一起使用效果更好)，展示进度条
若存在mariadb-libs问题，则执行yum remove mysql-libs即可
2.3 查看MySQL版本
mysql --version #或 mysqladmin --version
2.4 服务的初始化
为了保证数据库目录与文件的所有者为 mysql 登录用户，如果你是以 root 身份运行 mysql 服务，需要执行下面的命令初始化：
mysqld --initialize --user=mysql
说明： --initialize 选项默认以“安全”模式来初始化，则会为 root 用户生成一个密码并将该密码标记为过期，登录后你需要设置一个新的密码。生成的临时密码会往日志中记录一份。
查看密码：
cat /var/log/mysqld.log
root@localhost: 后面就是初始化的密码
2.5 启动MySQL，查看状态
#加不加.service后缀都可以 启动：systemctl start mysqld.service 关闭：systemctl stop mysqld.service 重启：systemctl restart mysqld.service 查看状态：systemctl status mysqld.service
2.6 查看MySQL服务是否自启动
systemctl list-unit-files|grep mysqld.service
- 如不是enabled可以运行如下命令设置自启动
systemctl enable mysqld.service
- 如果希望不进行自启动，运行如下命令设置
systemctl disable mysqld.service
3. MySQL登录
3.1 首次登录
通过mysql -hlocalhost -P3306 -uroot -p进行登录，在Enter password：录入初始化密码
3.2 修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
3.3 设置远程登录
1. 确认网络
1.在远程机器上使用ping ip地址保证网络畅通
2.在远程机器上使用telnet命令保证端口号开放访问
2. 关闭防火墙或开放端口
方式一：关闭防火墙
- CentOS6 ：
service iptables stop
- CentOS7：
#开启防火墙 systemctl start firewalld.service #查看防火墙状态 systemctl status firewalld.service #关闭防火墙 systemctl stop firewalld.service #设置开机启用防火墙 systemctl enable firewalld.service #设置开机禁用防火墙 systemctl disable firewalld.service
方式二：开放端口
- 查看开放的端口号
firewall-cmd --list-all
- 设置开放的端口号
firewall-cmd --add-service=http --permanent firewall-cmd --add-port=3306/tcp --permanent
- 重启防火墙
firewall-cmd --reload
4. Linux下修改配置
- 修改允许远程登陆
use mysql; select Host,User from user; update user set host = '%' where user ='root'; flush privileges;
%是个 通配符 ，如果Host=192.168.1.%，那么就表示只要是IP地址前缀为“192.168.1.”的客户端都可以连接。如果Host=%，表示所有IP都有连接权限。
注意：在生产环境下不能为了省事将host设置为%，这样做会存在安全问题，具体的设置可以根据生产环境的IP进行设置。
配置新连接报错：错误号码 2058，分析是 mysql 密码加密方法变了。
解决方法一：升级远程连接工具版本
解决方法二：
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'abc123';
5. 字符集的相关操作
5.1 各级别的字符集
show variables like 'character%';
- character_set_server：服务器级别的字符集
- character_set_database：当前数据库的字符集
- character_set_client：服务器解码请求时使用的字符集
- character_set_connection：服务器处理请求时会把请求字符串从character_set_client转为character_set_connection
- character_set_results：服务器向客户端返回数据时使用的字符集
小结
- 如果
创建或修改列时没有显式的指定字符集和比较规则，则该列默认用表的字符集和比较规则 - 如果
创建表时没有显式的指定字符集和比较规则，则该表默认用数据库的字符集和比较规则 - 如果
创建数据库时没有显式的指定字符集和比较规则，则该数据库默认用服务器的字符集和比较规则
5.2 请求到响应过程中字符集的变化
graph TB
A(客户端) --> |"使用操作系统的字符集编码请求字符串"| B(从character_set_client转换为character_set_connection)
B --> C(从character_set_connection转换为具体的列使用的字符集)
C --> D(将查询结果从具体的列上使用的字符集转换为character_set_results)
D --> |"使用操作系统的字符集解码响应的字符串"| A
第02章 MySQL的数据目录
1. MySQL8的主要目录结构
find / -name mysql
1.1 数据库文件的存放路径
show variables like 'datadir'; # /var/lib/mysql/
1.2 相关命令目录
相关命令目录：/usr/bin 和/usr/sbin。
1.3 配置文件目录
配置文件目录：/usr/share/mysql-8.0（命令及配置文件），/etc/mysql（如my.cnf）
2. 数据库和文件系统的关系
2.1 表在文件系统中的表示
2.3.1 InnoDB存储引擎模式
1. 表结构
为了保存表结构，InnoDB在数据目录下对应的数据库子目录下创建了一个专门用于描述表结构的文件
表名.frm
2. 表中数据和索引
① 系统表空间（system tablespace）
默认情况下，InnoDB会在数据目录下创建一个名为ibdata1、大小为12M的自拓展文件，这个文件就是对应的系统表空间在文件系统上的表示。
② 独立表空间(file-per-table tablespace)
在MySQL5.6.6以及之后的版本中，InnoDB并不会默认的把各个表的数据存储到系统表空间中，而是为每一个表建立一个独立表空间，也就是说我们创建了多少个表，就有多少个独立表空间。使用独立表空间来存储表数据的话，会在该表所属数据库对应的子目录下创建一个表示该独立表空间的文件，文件名和表名相同。
表名.ibd
MySQL8.0中不再单独提供表名.frm，而是合并在表名.ibd文件中。
③ 系统表空间与独立表空间的设置
我们可以自己指定使用系统表空间还是独立表空间来存储数据，这个功能由启动参数innodb_file_per_table控制
[server] innodb_file_per_table=0 # 0：代表使用系统表空间； 1：代表使用独立表空间
④ 其他类型的表空间
随着MySQL的发展，除了上述两种老牌表空间之外，现在还新提出了一些不同类型的表空间，比如通用表空间（general tablespace）、临时表空间（temporary tablespace）等。
2.3.2 MyISAM存储引擎模式
1. 表结构
在存储表结构方面， MyISAM 和 InnoDB 一样，也是在数据目录下对应的数据库子目录下创建了一个专门用于描述表结构的文件
表名.frm
2. 表中数据和索引
在MyISAM中的索引全部都是二级索引，该存储引擎的数据和索引是分开存放的。所以在文件系统中也是使用不同的文件来存储数据文件和索引文件，同时表数据都存放在对应的数据库子目录下。
test.frm 存储表结构 #MySQL8.0 改为了 b.xxx.sdi test.MYD 存储数据 (MYData) test.MYI 存储索引 (MYIndex
第03章 用户与权限管理
1. 用户管理
1.1 登录MySQL服务器
启动MySQL服务后，可以通过mysql命令来登录MySQL服务器，命令如下：
mysql –h hostname|hostIP –P port –u username –p DatabaseName –e "SQL语句"
-h参数后面接主机名或者主机IP，hostname为主机，hostIP为主机IP。-P参数后面接MySQL服务的端口，通过该参数连接到指定的端口。MySQL服务的默认端口是3306，不使用该参数时自动连接到3306端口，port为连接的端口号。-u参数后面接用户名，username为用户名。-p参数会提示输入密码。DatabaseName参数指明登录到哪一个数据库中。如果没有该参数，就会直接登录到MySQL数据库中，然后可以使用USE命令来选择数据库。-e参数后面可以直接加SQL语句。登录MySQL服务器以后即可执行这个SQL语句，然后退出MySQL服务器。
mysql -uroot -p -hlocalhost -P3306 mysql -e "select host,user from user"
1.2 创建用户
CREATE USER 用户名 [IDENTIFIED BY '密码'][,用户名 [IDENTIFIED BY '密码']];
举例：
CREATE USER zhang3 IDENTIFIED BY '123123'; # 默认host是 % CREATE USER 'kangshifu'@'localhost' IDENTIFIED BY '123456';
1.3 修改用户
UPDATE mysql.user SET USER='li4' WHERE USER='wang5'; FLUSH PRIVILEGES;
1.4 删除用户
方式1：使用DROP方式删除（推荐）
DROP USER user[,user]…;
举例：
DROP USER li4 ; # 默认删除host为%的用户 DROP USER 'kangshifu'@'localhost';
方式2：使用DELETE方式删除（不推荐，有残留信息）
DELETE FROM mysql.user WHERE Host=’hostname’ AND User=’username’; FLUSH PRIVILEGES;
1.5 设置当前用户密码
1. 使用ALTER USER命令来修改当前用户密码
ALTER USER USER() IDENTIFIED BY 'new_password';
2. 使用SET语句来修改当前用户密码
SET PASSWORD='new_password';
1.6 修改其它用户密码
1. 使用ALTER语句来修改普通用户的密码
ALTER USER user [IDENTIFIED BY '新密码'] [,user[IDENTIFIED BY '新密码']]…;
2. 使用SET命令来修改普通用户的密码
SET PASSWORD FOR 'username'@'hostname'='new_password';
2. 权限管理
2.1 权限列表
show privileges;
CREATE和DROP权限，可以创建新的数据库和表，或删除（移掉）已有的数据库和表。如果将MySQL数据库中的DROP权限授予某用户，用户就可以删除MySQL访问权限保存的数据库。SELECT、INSERT、UPDATE和DELETE权限允许在一个数据库现有的表上实施操作。SELECT权限只有在它们真正从一个表中检索行时才被用到。INDEX权限允许创建或删除索引，INDEX适用于已有的表。如果具有某个表的CREATE权限，就可以在CREATE TABLE语句中包括索引定义。ALTER权限可以使用ALTER TABLE来更改表的结构和重新命名表。CREATE ROUTINE权限用来创建保存的程序（函数和程序），ALTER ROUTINE权限用来更改和删除保存的程序，EXECUTE权限用来执行保存的程序。GRANT权限允许授权给其他用户，可用于数据库、表和保存的程序。FILE权限使用户可以使用LOAD DATA INFILE和SELECT ... INTO OUTFILE语句读或写服务器上的文件，任何被授予FILE权限的用户都能读或写MySQL服务器上的任何文件（说明用户可以读任何数据库目录下的文件，因为服务器可以访问这些文件）。
2.2 授予权限的原则
权限控制主要是出于安全因素，因此需要遵循以下几个经验原则：
1、只授予能满足需要的最小权限，防止用户干坏事。比如用户只是需要查询，那就只给select权限就可以了，不要给用户赋予update、insert或者delete权限。
2、创建用户的时候限制用户的登录主机，一般是限制成指定IP或者内网IP段。
3、为每个用户设置满足密码复杂度的密码。
4、定期清理不需要的用户，回收权限或者删除用户。
2.3 授予权限
GRANT 权限1,权限2,…权限n ON 数据库名称.表名称 TO 用户名@用户地址 [IDENTIFIED BY ‘密码口令’];
- 该权限如果发现没有该用户，则会直接新建一个用户。
- 给li4用户用本地命令行方式，授予atguigudb这个库下的所有表的插删改查的权限。
GRANT SELECT,INSERT,DELETE,UPDATE ON atguigudb.* TO li4@localhost;
- 授予通过网络方式登录的joe用户 ，对所有库所有表的全部权限，密码设为123。注意这里唯独不包括grant的权限
GRANT ALL PRIVILEGES ON *.* TO joe@'%' IDENTIFIED BY '123';
2.4 查看权限
- 查看当前用户权限
SHOW GRANTS; # 或 SHOW GRANTS FOR CURRENT_USER; # 或 SHOW GRANTS FOR CURRENT_USER();
- 查看某用户的全局权限
SHOW GRANTS FOR 'user'@'主机地址';
2.5 收回权限
注意：在将用户账户从user表删除之前，应该收回相应用户的所有权限。
- 收回权限命令
REVOKE 权限1,权限2,…权限n ON 数据库名称.表名称 FROM 用户名@用户地址;
- 举例
#收回全库全表的所有权限 REVOKE ALL PRIVILEGES ON *.* FROM joe@'%'; #收回mysql库下的所有表的插删改查权限 REVOKE SELECT,INSERT,UPDATE,DELETE ON mysql.* FROM joe@localhost;
- 注意：
须用户重新登录后才能生效
3. 角色管理
3.1 创建角色
CREATE ROLE 'role_name'[@'host_name'] [,'role_name'[@'host_name']]...
角色名称的命名规则和用户名类似。如果host_name省略，默认为%，role_name不可省略，不可为空。
3.2 给角色赋予权限
GRANT privileges ON table_name TO 'role_name'[@'host_name'];
上述语句中privileges代表权限的名称，多个权限以逗号隔开。可使用SHOW语句查询权限名称
SHOW PRIVILEGES\G
3.3 查看角色的权限
SHOW GRANTS FOR 'role_name';
只要你创建了一个角色，系统就会自动给你一个“USAGE”权限，意思是连接登录数据库的权限。
3.4 回收角色的权限
REVOKE privileges ON tablename FROM 'rolename';
3.5 删除角色
DROP ROLE role [,role2]...
注意，如果你删除了角色，那么用户也就失去了通过这个角色所获得的所有权限。
3.6 给用户赋予角色
角色创建并授权后，要赋给用户并处于激活状态才能发挥作用。
GRANT role [,role2,...] TO user [,user2,...];
查询当前已激活的角色
SELECT CURRENT_ROLE();
3.7 激活角色
方式1：使用set default role 命令激活角色
SET DEFAULT ROLE ALL TO 'kangshifu'@'localhost';
方式2：将activate_all_roles_on_login设置为ON
SET GLOBAL activate_all_roles_on_login=ON;
这条 SQL 语句的意思是，对所有角色永久激活。
3.8 撤销用户的角色
REVOKE role FROM user;
3.9 设置强制角色(mandatory role)
方式1：服务启动前设置
[mysqld] mandatory_roles='role1,role2@localhost,r3@%.atguigu.com'
方式2：运行时设置
SET PERSIST mandatory_roles = 'role1,role2@localhost,r3@%.example.com'; #系统重启后仍然有效 SET GLOBAL mandatory_roles = 'role1,role2@localhost,r3@%.example.com'; #系统重启后失效
第04章 逻辑架构
1. 逻辑架构剖析
1.1 第1层：连接层
系统（客户端）访问MySQL服务器前，做的第一件事就是建立TCP连接。
经过三次握手建立连接成功后，MySQL服务器对TCP传输过来的账号密码做身份认证、权限获取。
- 用户名或密码不对，会收到一个Access denied for user错误，客户端程序结束执行
- 用户名密码认证通过，会从权限表查出账号拥有的权限与连接关联，之后的权限判断逻辑，都将依赖于此时读到的权限
TCP连接收到请求后，必须要分配给一个线程专门与这个客户端的交互。所以还会有个线程池，去走后面的流程。每一个连接从线程池中获取线程，省去了创建和销毁线程的开销。
1.2 第2层：服务层
- SQL Interface: SQL接口
- 接收用户的SQL命令，并且返回用户需要查询的结果。比如SELECT ... FROM就是调用SQL Interface
- MySQL支持DML（数据操作语言）、DDL（数据定义语言）、存储过程、视图、触发器、自定义函数等多种SQL语言接口
- Parser: 解析器
- 在解析器中对 SQL 语句进行语法分析、语义分析。将SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。如果在分解构成中遇到错误，那么就说明这个SQL语句是不合理的。
- 在SQL命令传递到解析器的时候会被解析器验证和解析，并为其创建
语法树，并根据数据字典丰富查询语法树，会验证该客户端是否具有执行该查询的权限。创建好语法树后，MySQL还会对SQL查询进行语法上的优化，进行查询重写。
- Optimizer: 查询优化器
- SQL语句在语法解析之后、查询之前会使用查询优化器确定 SQL 语句的执行路径，生成一个
执行计划。 - 这个执行计划表明应该
使用哪些索引进行查询（全表检索还是使用索引检索），表之间的连接顺序如何，最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询，并将查询结果返回给用户。 - 它使用“
选取-投影-连接”策略进行查询。例如：
SELECT id,name FROM student WHERE gender = '女';
这个SELECT查询先根据WHERE语句进行选取，而不是将表全部查询出来以后再进行gender过滤。 这个SELECT查询先根据id和name进行属性投影，而不是将属性全部取出以后再进行过滤，将这两个查询条件连接起来生成最终查询结果。
- Caches & Buffers： 查询缓存组件
- MySQL内部维持着一些Cache和Buffer，比如Query Cache用来缓存一条SELECT语句的执行结果，如果能够在其中找到对应的查询结果，那么就不必再进行查询解析、优化和执行的整个过程了，直接将结果反馈给客户端。
- 这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等 。
- 这个查询缓存可以在
不同客户端之间共享。 - 从MySQL 5.7.20开始，不推荐使用查询缓存，并在
MySQL 8.0中删除。
1.3 第3层：引擎层
插件式存储引擎层（ Storage Engines），真正的负责了MySQL中数据的存储和提取，对物理服务器级别维护的底层数据执行操作，服务层通过API与存储引擎进行通信。
1.4 小结
简化为三层结构：
- 连接层：客户端和服务器端建立连接，客户端发送 SQL 至服务器端；
- SQL 层（服务层）：对 SQL 语句进行查询处理；与数据库文件的存储方式无关；
- 存储引擎层：与数据库文件打交道，负责数据的存储和读取。
2. SQL执行流程
2.1 MySQL 中的 SQL执行流程
MySQL的查询流程：
1. 查询缓存：Server 如果在查询缓存中发现了这条 SQL 语句，就会直接将结果返回给客户端；如果没有，就进入到解析器阶段。需要说明的是，因为查询缓存往往效率不高，所以在 MySQL8.0 之后就抛弃了这个功能。
查询缓存是提前把查询结果缓存起来，这样下次不需要执行就可以直接拿到结果。需要说明的是，在MySQL 中的查询缓存，不是缓存查询计划，而是查询对应的结果。这就意味着查询匹配的鲁棒性大大降低，只有相同的查询操作才会命中查询缓存。两个查询请求在任何字符上的不同（例如：空格、注释、大小写），都会导致缓存不会命中。因此 MySQL 的 查询缓存命中率不高 。
同时，如果查询请求中包含某些系统函数、用户自定义变量和函数、一些系统表，如 mysql、 information_schema、 performance_schema 数据库中的表，那这个请求就不会被缓存。
此外，既然是缓存，那就有它缓存失效的时候。MySQL的缓存系统会监测涉及到的每张表，只要该表的结构或者数据被修改，如对该表使用了INSERT、UPDATE、DELETE、TRUNCATE TABLE、ALTER TABLE、DROP TABLE或DROP DATABASE语句，那使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除！对于更新压力大的数据库来说，查询缓存的命中率会非常低。
2. 解析器：在解析器中对 SQL 语句进行语法分析、语义分析。
分析器先做“词法分析”。你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。 MySQL 从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”。
接着，要做“语法分析”。根据词法分析的结果，语法分析器（比如：Bison）会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。如果SQL语句正确，则会生成一个语法树。
3. 优化器：在优化器中会确定 SQL 语句的执行路径，比如是根据全表检索，还是根据索引检索等。在查询优化器中，可以分为逻辑查询优化阶段和物理查询优化阶段。
4. 执行器：在执行之前需要判断该用户是否具备权限。如果没有，就会返回权限错误。如果具备权限，就执行 SQL查询并返回结果。在 MySQL8.0 以下的版本，如果设置了查询缓存，这时会将查询结果进行缓存。
SQL 语句在 MySQL 中的流程是： SQL语句→查询缓存→解析器→优化器→执行器 。
3. 数据库缓冲池(buffer pool)
InnoDB存储引擎是以页为单位来管理存储空间的，我们进行的增删改查操作其实本质上都是在访问页面（包括读页面、写页面、创建新页面等操作）。而磁盘 I/O 需要消耗的时间很多，而在内存中进行操作，效率则会高很多，为了能让数据表或者索引中的数据随时被我们所用，DBMS 会申请占用内存来作为数据缓冲池，在真正访问页面之前，需要把在磁盘上的页缓存到内存中的Buffer Pool之后才可以访问。
这样做的好处是可以让磁盘活动最小化，从而减少与磁盘直接进行 I/O 的时间。要知道，这种策略对提升 SQL 语句的查询性能来说至关重要。如果索引的数据在缓冲池里，那么访问的成本就会降低很多。
3.1 缓冲池 vs 查询缓存
1. 缓冲池（Buffer Pool）
从图中，你能看到 InnoDB 缓冲池包括了数据页、索引页、插入缓冲、锁信息、自适应 Hash 和数据字典信息等。
缓存原则：
“位置 * 频次”这个原则，可以帮我们对 I/O 访问效率进行优化。
首先，位置决定效率，提供缓冲池就是为了在内存中可以直接访问数据。
其次，频次决定优先级顺序。因为缓冲池的大小是有限的，比如磁盘有 200G，但是内存只有 16G，缓冲池大小只有 1G，就无法将所有数据都加载到缓冲池里，这时就涉及到优先级顺序，会优先对使用频次高的热数据进行加载。
2. 查询缓存
查询缓存是提前把查询结果缓存起来，这样下次不需要执行就可以直接拿到结果。需要说明的是，在MySQL 中的查询缓存，不是缓存查询计划，而是查询对应的结果。因为命中条件苛刻，而且只要数据表发生变化，查询缓存就会失效，因此命中率低。
3.2 缓冲池如何读取数据
缓冲池管理器会尽量将经常使用的数据保存起来，在数据库进行页面读操作的时候，首先会判断该页面是否在缓冲池中，如果存在就直接读取，如果不存在，就会通过内存或磁盘将页面存放到缓冲池中再进行读取。
3.3 查看/设置缓冲池的大小
查看缓冲池的大小
show variables like 'innodb_buffer_pool_size';
设置缓冲池的大小
set global innodb_buffer_pool_size = 268435456;
或者
[server] innodb_buffer_pool_size = 268435456
3.4 多个Buffer Pool实例
[server] innodb_buffer_pool_instances = 2
何查看缓冲池的个数
show variables like 'innodb_buffer_pool_instances';
每个Buffer Pool实例实际占内存空间
innodb_buffer_pool_size/innodb_buffer_pool_instances
