Mysql8的特性

一、递归查询（树型表查询）

课程分类表是一个树型结构，其中parentid字段为父结点ID，它是树型结构的标志字段。

1. 层级固定

如果树的层级固定可以使用表的自链接去查询，比如：我们只查询两级课程分类，可以用下边的SQL

内连接

select
      one.id            one_id,
      one.name          one_name,
      one.parentid      one_parentid,
      one.orderby       one_orderby,
      one.label         one_label,
      two.id            two_id,
      two.name          two_name,
      two.parentid      two_parentid,
      two.orderby       two_orderby,
      two.label         two_label
  from course_category one
           inner join course_category two on one.id = two.parentid
  where one.parentid = 1
    and one.is_show = 1
    and two.is_show = 1
  order by one.orderby,
           two.orderby;

2. 层级不确定

2.1、 向下递归，即找到初始节点的所有下级节点

此时可以使用MySQL递归实现，使用with语法

 WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...

cte_name :公共表达式的名称,可以理解为表名,用来表示as后面跟着的子查询
col_name :公共表达式包含的列名,可以写也可以不写

下边是一个递归的简单例子:

with RECURSIVE t1  AS
(
  SELECT 1 as n
  UNION ALL
  SELECT n + 1 FROM t1 WHERE n < 5
)
SELECT * FROM t1;

输出：

说明：
t1 相当于一个表名
select 1 相当于这个表的初始值，这里使用UNION ALL 不断将每次递归得到的数据加入到表中。
n<5为递归执行的条件，当n>=5时结束递归调用。

下边我们使用递归实现课程分类的查询

with recursive t1 as (
select * from  course_category p where  id= '1'
union all
 select t.* from course_category t inner join t1 on t1.id = t.parentid
)
select *  from t1 order by t1.id, t1.orderby

查询结果：

t1表中初始的数据是id等于1的记录，即根结点。
通过inner join t1 t2 on t2.id = t.parentid 找到id='1’的下级节点 。
通过这种方法就找到了id='1’的所有下级节点，下级节点包括了所有层级的节点。

2.2、 上递归

案例：

with recursive t1 as (
select * from  course_category p where  id= '1-1-1'
union all
 select t.* from course_category t inner join t1 on t1.parentid = t.id
)
select *  from t1 order by t1.id, t1.orderby

初始节点为1-1-1，通过递归找到它的父级节点，父级节点包括所有级别的节点。
以上是我们研究了树型表的查询方法，通过递归的方式查询课程分类比较灵活，因为它可以不限制层级。

mysql为了避免无限递归默认递归次数为1000，可以通过设置cte_max_recursion_depth参数增加递归深度，还可以通过max_execution_time限制执行时间，超过此时间也会终止递归操作。
mysql递归相当于在存储过程中执行若干次sql语句，java程序仅与数据库建立一次链接执行递归操作，所以只要控制好递归深度，控制好数据量性能就没有问题。

思考题

• 如果java程序在递归操作中连接数据库去查询数据组装数据，这个性能高吗？

二、MySQL常用命令

1、创建用户

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';

查询用户

• select user,host from mysql.user;
  MySQL8.0报错：Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

2、远程连接失败

• 原因：可能是mysql数据库user表中，用户的 host 字段配置是不允许当前host访问或者是访问的加密方式不对，旧版的MySQL和新版的密码加密方式不一致，但是很多企业和个人都习惯使用旧版的加密方式

原因：
	1、加密方式
	2、用户设置的host设置的是固定IP或localhost
	3、用户权限不够

解决：

1. 先使用mysql数据库

use mysql;

mysql数据库user表中，用户的 host 字段配置是不允许当前host访问

2. 查看所允许的访问地址

select host, user plugin from user;

3. 修改所允许的访问地址，即可

update user set host='%' where user='root';

访问的加密方式不对，旧版的MySQL和新版的密码加密方式不一致，但是很多企业和个人都习惯使用旧版的加密方式

4. 查看密码加密方式

select host, user,  plugin from user;

5. 修改密码的加密方式

alter user 'root'@'localhost' identified with mysql_native_password by '用户密码';

或

alter user 'root'@'%' identified with mysql_native_password by'用户密码';

6. 刷新权限

FLUSH PRIVILEGES;

3、用户授权遇到的问题

问题

mysql>grant all privileges ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH grant OPTION;
 
 
mysql>ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'IDENT
IFIED BY 'root' WITH grant OPTION' at line 1

查阅资料得知，Mysql8.0以后不再像以前版本一样的授权方式 ，需要重新创建用户，然后再重新授权。

Starting with MySQL 8 you no longer can (implicitly) create a user using the GRANT command. Use CREATE USER instead, followed by the GRANT statement:

#解决方案如下：
mysql>CREATE USER 'root'@'%' IDENTIFIED BY 'root';
 
mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;