MySQL8.x版本的新的功能特性总结

目录

一、特性总结

1.运维管理方面的改进

2.开发相关的新特性

3、复制相关改进

4、安全相关改进

5、优化器部分

6、其他改进

二、重点总结开发相关特性：

1.窗口函数

• 1.1 ROW_NUMBER()

​•1.2 RANK()

• 1.3 DENSE_RANK()

• 1.4 PERCENT_RANK()

• 1.5 CUME_DIST()

• 1.6 LAG()

•1.7 LEAD()

• 1.8 FIRST_VALUE()

•1.9 LAST_VALUE()

• 1.10 NTILE()

• 1.11 NTH_VALUE()

• 1.12 AVG()

• 1.13 COUNT()

• 1.14 MAX()

• 1.15 MIN()

•1.16 SUM()

• 1.17 STDDEV_POP()和 STDDEV_SAMP()

• 1.18 VAR_POP() 和 VAR_SAMP()

• 1.19 GROUPING()

• 1.20 JSON_ARRAYAGG()和 JSON_OBJECTAGG()

• 1.21 STRING_AGG()

• 1.22 BIT_AND()

• 1.23 BIT_OR()

• 1.24 ARRAY_AGG()

1.25 GROUPING()的注意事项

    请注意，`GROUPING()`函数通常与`ROLLUP`或`CUBE`一起使用，用于在分组聚合查询中识别完全聚合的行。`JSON_ARRAYAGG()`和`JSON_OBJECTAGG()`函数将结果聚合成 JSON 格式，这对于需要将数据以 JSON 格式输出的场景非常有用。`STRING_AGG()`函数将字符串聚合成一个单独的字符串，中间用指定的分隔符隔开。`BIT_AND()`和`BIT_OR()`函数在位级别上对数字进行逻辑与和逻辑或操作。`ARRAY_AGG()`函数将值聚合成一个数组。

2.JSON函数的使用

• 2.1 JSON_EXTRACT：

​• 2.2 JSON_SEARCH：

还可简写省略第二个参数  'one'  ，因为默认就是  'one'  。

• 2.3 JSON_SET：

• 2.4 JSON_REMOVE：

• 2.5 JSON_INSERT：

• 2.6 JSON_REPLACE：

• 2.7 JSON_MERGE：

•2.8 JSON_MERGE_PATCH：

• 2.9 JSON_TABLE：

• 2.10 JSON_KEYS：

• 2.11 JSON_OVERLAPS：

• 2.12 JSON_ARRAY_APPEND：

• 2.13 JSON_DEPTH：

• 2.14 JSON_LENGTH：

• 2.15 JSON_VALID：

• 2.16 JSON_PRETTY：

• 2.17 JSON_STORAGE_FREE：

• 2.18 JSON_STORAGE_SIZE：

• 2.19 JSON_CONTAINS：

• 2.20 JSON_CONTAINS_PATH：

• 2.21 JSON_SCHEMA_VALIDATION：

• 2.22 JSON_UNQUOTE：

• 2.23 JSON_ARRAY：

• 2.24 JSON_OBJECT：

• 2.25 JSON_QUOTE：

• 2.26 JSON_EXTRACT_SCALAR：

•2.27 JSON_SET_DEFAULT：

• 2.28 JSON_TABLE with NESTED：

• 2.29 JSON_CONTAINS_PATH with all：

3.CTE的使用

3.1.CTE 的基本概念

3.2.CTE 的语法结构

3.3.CTE 的主要用途

---

一、特性总结

1.运维管理方面的改进

• 可持久化变量：允许配置参数在重启后依然有效，减少了运维工作量。

• 管理员端口：为管理员提供了一个独立的端口，方便进行维护操作。

• 资源组：可以更精细地控制资源分配，提升系统稳定性。

• 数据库粒度只读：支持将单个数据库设置为只读，增强了数据安全性。

• 改进的show processlist：提供更详细的进程信息，便于诊断问题。

• 加速索引创建速度：大幅提升了索引创建的效率。

• 控制连接内存使用量：可以有效防止内存泄漏和资源耗尽。

2.开发相关的新特性

• 默认字符集更新：默认字符集升级为utf8mb4，支持更多字符。

• GROUP BY ASC/DESC语法：增加了对GROUP BY语句的排序支持。

• 公用表达式（CTE）：提高了查询效率，减少了重复计算。

• 窗口函数：支持更复杂的分析查询。

• check约束：增强了数据完整性校验。

• 隐藏列、不可见索引：提供了更多数据管理灵活性。

• 降序索引和函数索引：优化了查询性能。

3、复制相关改进

• Binlog包含元数据：便于数据恢复和复制管理。

• 记录事务提交时间戳：有助于分析事务延迟。

• Binlog过期时间：自动清理过旧的Binlog，节省存储空间。

• ReplicaSet复制方案：简化了主从复制的配置和管理。

4、安全相关改进

• 角色管理：支持角色的创建、删除和权限管理。

• 更细粒度的权限控制：提供了更灵活的权限分配方案。

• 密码策略：增强了密码复杂度和重用限制。

5、优化器部分

• hash join：提高了复杂查询的执行效率。

• 直方图：优化了查询计划的生成。

6、其他改进

• 原子DDL：InnoDB表的DDL支持事务完整性，要么成功要么回滚。

• Redo日志优化：通过无锁化和专用线程处理Redo日志，提升了性能。

• 自增列优化：自增列计数器在重启后保持不变。

• 索引损坏处理：InnoDB在Redo日志中写入损坏标志，确保损坏索引不再恢复。

• EXPLAIN ANALYZE：提供了查询性能分析工具，可以显示实际执行情况。

这些新特性使得MySQL 8.x版本在性能、安全性、易用性和开发效率等方面都有了显著提升，为数据库管理和开发带来了诸多便利。

本文主要总结一下项目开发中常用到的用于查询的一些新的特性功能：窗口函数；CTE以及JSON函数

二、重点总结开发相关特性：

1.窗口函数

MySQL 8.0 引入了窗口函数（Window Functions），也称为分析函数（Analytic Functions），它们专门用于处理复杂的统计分析问题，例如移动平均、累计总和、百分比排名等。以下是 MySQL 8.0 支持的一些主要窗口函数：
• 序号函数：

• `ROW_NUMBER()`：为窗口内的每一行生成一个序号。

• `RANK()`：计算窗口内每一行的排名，允许并列。

• `DENSE_RANK()`：计算窗口内每一行的排名，不允许并列产生的间隙。

• 分布函数：

• `PERCENT_RANK()`：计算窗口内每一行在整个分区中的相对位置，返回百分比排名值。

• `CUME_DIST()`：计算累积分布值，即分区值小于或等于当前行值的百分比。

• 前后函数：

• `LAG()`：获取窗口内当前行之前的某一行的值。

• `LEAD()`：获取窗口内当前行之后的某一行的值。

• 头尾函数：

• `FIRST_VALUE()`：获取窗口内第一行的值。

• `LAST_VALUE()`：获取窗口内最后一行的值。

• 聚合函数：

• 许多聚合函数如`SUM()`,`AVG()`,`MIN()`,`MAX()`等也可以作为窗口函数使用。

• 其他函数：

• `NTH_VALUE()`：获取窗口内第 N 行的值。

• `NTILE()`：将分区中的行分配到指定数量的桶中，并返回当前行所在的桶编号。

窗口函数的基本语法如下：

SELECT
    WINDOW_FUNCTION() OVER(
        [PARTITION BY `字段1`, `字段2`, ...]
        [ORDER BY `字段3`, `字段4`]
        [FRAME_CLAUSE]
    ) [AS `别名`]
FROM `表名`;

其中，`OVER`子句定义了窗口的分区方式、排序和框架（frame），而窗口函数则对每个窗口中的行进行计算。

窗口函数的使用允许在不改变原始结果集行数的情况下，为每行添加额外的分析数据，这在处理复杂的数据分析时非常有用。例如，你可以在不改变原始数据集的情况下，为每个员工添加其在财政年度中的销售总额。

创建一个 employees   表来演示这些函数的使用

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '员工的唯一标识符',
    first_name VARCHAR(50) COMMENT '员工的名字',
    last_name VARCHAR(50) COMMENT '员工的姓氏',
    hire_date DATE COMMENT '员工的入职日期',
    salary DECIMAL(10, 2) COMMENT '员工的薪水，单位为货币（例如美元）',
    department_id INT COMMENT '员工所属的部门ID'
) COMMENT='员工信息表，存储员工的基本信息和薪资数据';

然后插入一些数据：

INSERT INTO employees (first_name, last_name, hire_date, salary, department_id) VALUES
('John', 'Doe', '2010-01-11', 70000.00, FLOOR(1 + (RAND() * 5))),
('Jane', 'Smith', '2012-05-22', 65000.00, FLOOR(1 + (RAND() * 5))),
('Alice', 'Johnson', '2013-08-09', 80000.00, FLOOR(1 + (RAND() * 5))),
('Bob', 'Brown', '2014-10-12', 60000.00, FLOOR(1 + (RAND() * 5))),
('Charlie', 'Davis', '2015-12-15', 75000.00, FLOOR(1 + (RAND() * 5))),
('David', 'Miller', '2016-02-20', 90000.00, FLOOR(1 + (RAND() * 5))),
('Eva', 'Wilson', '2017-04-23', 68000.00, FLOOR(1 + (RAND() * 5))),
('Frank', 'Moore', '2018-06-30', 72000.00, FLOOR(1 + (RAND() * 5))),
('Grace', 'Taylor', '2019-08-05', 88000.00, FLOOR(1 + (RAND() * 5))),
('Hannah', 'Anderson', '2020-10-11', 55000.00, FLOOR(1 + (RAND() * 5))),
('Isaac', 'Thomas', '2021-12-18', 62000.00, FLOOR(1 + (RAND() * 5))),
('Julia', 'Jackson', '2022-02-25', 78000.00, FLOOR(1 + (RAND() * 5))),
('Kevin', 'White', '2023-04-01', 58000.00, FLOOR(1 + (RAND() * 5))),
('Laura', 'Harris', '2024-06-08', 85000.00, FLOOR(1 + (RAND() * 5))),
('Mia', 'Martin', '2025-08-15', 70000.00, FLOOR(1 + (RAND() * 5))),
('Nathan', 'King', '2026-10-22', 92000.00, FLOOR(1 + (RAND() * 5))),
('Olivia', 'Lee', '2027-12-29', 65000.00, FLOOR(1 + (RAND() * 5))),
('Paul', 'Young', '2028-03-05', 77000.00, FLOOR(1 + (RAND() * 5))),
('Queen', 'Robinson', '2029-05-12', 89000.00, FLOOR(1 + (RAND() * 5))),
('Richard', 'Clark', '2030-07-19', 59000.00, FLOOR(1 + (RAND() * 5))),
('Samantha', 'Hall', '2031-09-26', 66000.00, FLOOR(1 + (RAND() * 5))),
('Tina', 'Walker', '2032-11-02', 73000.00, FLOOR(1 + (RAND() * 5)));

有了数据可以使用窗口函数进行查询了

常用的 MySQL 窗口函数的用法示例：

• 1.1 ROW_NUMBER()

• 为每个员工分配一个序号。

 SELECT 
       employee_id,
       first_name,
       last_name,
       hire_date,
       ROW_NUMBER() OVER (ORDER BY hire_date) AS hire_rank
   FROM 
       employees;

运行结果：

 •1.2 RANK()

• 为每个员工分配一个排名，如果有并列则会有相同的排名。

  SELECT 
       employee_id,
       first_name,
       last_name,
       salary,
       RANK() OVER (ORDER BY salary DESC) AS salary_rank
   FROM 
       employees;

运行结果：

• 1.3 DENSE_RANK()

• 为每个员工分配一个排名，并列的排名相同，不会有间隙。

 SELECT 
       employee_id,
       first_name,
       last_name,
       salary,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
   FROM 
       employees;

运行结果：

• 1.4 PERCENT_RANK()

• 计算每个员工的百分比排名。

SELECT 
    employee_id,
    first_name,
    last_name,
    salary,
    PERCENT_RANK() OVER (ORDER BY salary DESC) AS salary_percent_rank
FROM 
    employees;

运行结果：

• 1.5 CUME_DIST()

• 计算每个员工的累积分布百分比。

SELECT
	employee_id,
	first_name,
	last_name,
	salary,
	CUME_DIST() OVER ( ORDER BY salary DESC ) AS salary_cume_dist 
FROM
	employees;

运行结果：

• 1.6 LAG()

• 获取当前行前一行的薪水。

SELECT
	employee_id,
	first_name,
	last_name,
	salary,
	LAG( salary ) OVER ( ORDER BY employee_id ) AS prev_salary 
FROM
	employees;

运行结果：

•1.7 LEAD()

• 获取当前行后一行的薪水。

SELECT
	employee_id,
	first_name,
	last_name,
	salary,
	LEAD( salary ) OVER ( ORDER BY employee_id ) AS next_salary 
FROM
	employees;

运行后结果：

• 1.8 FIRST_VALUE()

• 获取每个部门中薪水最低的员工薪水。

   SELECT 
       department_id,
       employee_id,
       first_name,
       last_name,
       salary,
       FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary) AS min_salary
   FROM 
       employees;

运行结果：

•1.9 LAST_VALUE()

• 获取每个部门中薪水最高的员工薪水。

SELECT
	department_id,
	employee_id,
	first_name,
	last_name,
	salary,
	LAST_VALUE( salary ) OVER ( PARTITION BY department_id ORDER BY salary ) AS max_salary 
FROM
	employees;

运行后结果：

• 1.10 NTILE()

• 将员工分成四等分。

SELECT
	employee_id,
	first_name,
	last_name,
	salary,
	NTILE( 4 ) OVER ( ORDER BY salary ) AS quartile 
FROM
	employees;

运行结果：

• 1.11 NTH_VALUE()

`NTH_VALUE()`是 MySQL 8.0 中引入的一个窗口函数，用于从窗口框架中返回指定行的值。如果指定的行不存在，则返回`NULL`。
语法

NTH_VALUE(expression, N) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY sort_expression]
    [frame_clause]
)

• `expression`：要检索的列或表达式。

• `N`：指定的行号，从1开始。

• `PARTITION BY`：将结果集按指定列分组。

• `ORDER BY`：在每个分组内按指定列排序。

• `frame_clause`：定义窗口框架的范围（可选）。

示例 1：获取每个部门的第 3 行薪水
假设我们有一个`employees`表，包含员工的`department_id`和`salary`，我们希望获取每个部门中按薪水排序的第 3 行薪水。

SELECT 
    department_id,
    employee_id,
    first_name,
    last_name,
    salary,
    NTH_VALUE(salary, 3) OVER (
        PARTITION BY department_id 
        ORDER BY salary DESC
    ) AS third_highest_salary
FROM 
    employees;

• `PARTITION BY department_id`：按部门分组。

• `ORDER BY salary DESC`：在每个部门内按薪水降序排列。

• `NTH_VALUE(salary, 3)`：获取每个部门中薪水排名第 3 的值。

示例 2：获取每个部门的第 2 行员工姓名
假设我们希望获取每个部门中按入职日期排序的第 2 行员工的姓名。

SELECT 
    department_id,
    employee_id,
    first_name,
    last_name,
    hire_date,
    NTH_VALUE(CONCAT(first_name, ' ', last_name), 2) OVER (
        PARTITION BY department_id 
        ORDER BY hire_date
    ) AS second_employee
FROM 
    employees;

• `PARTITION BY department_id`：按部门分组。

• `ORDER BY hire_date`：在每个部门内按入职日期排序。

• `NTH_VALUE(CONCAT(first_name, ' ', last_name), 2)`：获取每个部门中第 2 行员工的全名。

示例 3：全局排序并获取第 5 行的薪水
如果不需要分组，可以直接在全局范围内使用`NTH_VALUE()`。

SELECT 
    employee_id,
    first_name,
    last_name,
    salary,
    NTH_VALUE(salary, 5) OVER (
        ORDER BY salary DESC
    ) AS fifth_highest_salary
FROM 
    employees;

• `ORDER BY salary DESC`：按薪水降序排列。

• `NTH_VALUE(salary, 5)`：获取全局范围内第 5 行的薪水。

注意事项

• 窗口框架：`NTH_VALUE()`的行为可能依赖于窗口框架的定义。如果不指定窗口框架，默认为`RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`。

• 行号范围：如果指定的行号超出窗口范围，`NTH_VALUE()`将返回`NULL`。

• 排序和分组：`ORDER BY`和`PARTITION BY`是可选的，但通常需要排序以确保结果的可预测性。

当然，以下是一些额外窗口函数的使用示例：

• 1.12 AVG()

• 计算窗口内的平均值。

SELECT 
    department_id,
    AVG(salary) OVER (PARTITION BY department_id) AS avg_salary
FROM 
    employees;

• 1.13 COUNT()

计算窗口内的行数。

SELECT 
    department_id,
    employee_id,
    COUNT(*) OVER (PARTITION BY department_id) AS num_employees
FROM 
    employees;

• 1.14 MAX()

计算窗口内的最大值。

SELECT 
    department_id,
    MAX(salary) OVER (PARTITION BY department_id) AS max_salary
FROM 
    employees;

• 1.15 MIN()

计算窗口内的最小值。

SELECT 
    department_id,
    MIN(salary) OVER (PARTITION BY department_id) AS min_salary
FROM 
    employees;

•1.16 SUM()

计算窗口内的总和。

SELECT 
    department_id,
    SUM(salary) OVER (PARTITION BY department_id) AS total_salary
FROM 
    employees;

• 1.17 STDDEV_POP()和 STDDEV_SAMP()

分别计算窗口内的总体标准差和样本标准差。

SELECT 
    department_id,
    STDDEV_POP(salary) OVER (PARTITION BY department_id) AS stddev_pop_salary
FROM 
    employees;

• 1.18 VAR_POP() 和 VAR_SAMP()

分别计算窗口内的总体方差和样本方差。

SELECT 
    department_id,
    VAR_POP(salary) OVER (PARTITION BY department_id) AS var_pop_salary
FROM 
    employees;

• 1.19 GROUPING()

 用于在 ROLLUP 或 CUBE 操作中识别分组。

SELECT 
    department_id,
    GROUPING(department_id) AS grouping_id,
    COUNT(*) AS num_employees
FROM 
    employees
 GROUP BY 
    GROUPING SETS (department_id, (SELECT 1));

用于在 ROLLUP 或 CUBE 操作中识别分组。

 GROUPING(department_id)   返回一个整数，表示是否为完全聚合的行（即所有部门的聚合）。

 GROUPING SETS   创建一个分组集合，包括单个部门和所有部门的聚合。 

• 1.20 JSON_ARRAYAGG()和 JSON_OBJECTAGG()

将窗口内的数据聚合成 JSON 数组或对象。

SELECT 
    department_id,
    JSON_ARRAYAGG(JSON_OBJECT(first_name, last_name)) OVER (PARTITION BY department_id) AS employees_json
FROM 
    employees;

• 1.21 STRING_AGG()

将窗口内的数据聚合成一个字符串

SELECT 
    department_id,
    STRING_AGG(CONCAT(first_name, ' ', last_name), ', ') WITHIN GROUP (ORDER BY first_name) AS employee_names
FROM 
    employees
GROUP BY 
    department_id;

• 1.22 BIT_AND()

计算窗口内的位与

SELECT 
    department_id,
    BIT_AND(employee_id) OVER (PARTITION BY department_id) AS bit_and_id
FROM 
    employees;

• 1.23 BIT_OR()

计算窗口内的位或

SELECT 
    department_id,
    BIT_OR(employee_id) OVER (PARTITION BY department_id) AS bit_or_id
FROM 
    employees;

• 1.24 ARRAY_AGG()

 将窗口内的数据聚合成一个数组

SELECT 
    department_id,
    ARRAY_AGG(employee_id ORDER BY employee_id) WITHIN GROUP (ORDER BY employee_id) AS employee_ids
FROM 
    employees
GROUP BY 
    department_id;

1.25 GROUPING()的注意事项

    请注意，`GROUPING()`函数通常与`ROLLUP`或`CUBE`一起使用，用于在分组聚合查询中识别完全聚合的行。`JSON_ARRAYAGG()`和`JSON_OBJECTAGG()`函数将结果聚合成 JSON 格式，这对于需要将数据以 JSON 格式输出的场景非常有用。`STRING_AGG()`函数将字符串聚合成一个单独的字符串，中间用指定的分隔符隔开。`BIT_AND()`和`BIT_OR()`函数在位级别上对数字进行逻辑与和逻辑或操作。`ARRAY_AGG()`函数将值聚合成一个数组。

GROUPING函数提到到ROLLLUP与CUBE,那么再介绍一下，这两个的操作符

WITH ROLLUP   功能在 MySQL 5.1版本 中被引入。它作为   GROUP BY   子句的一个扩展，允许在分组查询中生成汇总行（包括小计和总计行），从而实现多级汇总功能。关于 MySQL 5.1 及后续版本中的   WITH ROLLUP  ：

• MySQL 5.1：首次引入了   WITH ROLLUP   功能，支持对分组数据进行多级汇总。

• MySQL 5.6：继续支持   WITH ROLLUP  ，但不支持   WITH CUBE  。

• MySQL 5.7：支持   WITH ROLLUP  ，但不允许与   ORDER BY   同时使用。如果需要排序，需要将结果作为子查询处理。

• MySQL 8.0：进一步增强了对   WITH ROLLUP   的支持，包括在   ORDER BY   子句中使用（从 MySQL 8.0.12 开始支持）。

在MySQL 8中，`WITH ROLLUP`子句用于在分组查询中自动包含汇总行，这使得我们可以在单个查询中获取多个级别的汇总信息。以下是一些使用`WITH ROLLUP`的示例及其查询结果和解释：

示例1：单列分组汇总
假设有一个`sales`表，包含`year`（年份）和`profit`（利润）列。

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '唯一标识',
    year INT NOT NULL COMMENT '销售年份',
    country VARCHAR(50) NOT NULL COMMENT '销售国家',
    product VARCHAR(50) NOT NULL COMMENT '销售产品',
    profit DECIMAL(10, 2) NOT NULL COMMENT '产品利润'
) COMMENT='销售数据表，用于记录每年不同国家的产品销售利润';

-- 2000年数据
INSERT INTO sales (year, country, product, profit) VALUES
(2000, 'Finland', 'Computer', 1500.00),
(2000, 'Finland', 'Phone', 100.00),
(2000, 'India', 'Calculator', 150.00),
(2000, 'India', 'Computer', 1200.00),
(2000, 'USA', 'Calculator', 75.00),
(2000, 'USA', 'Computer', 1500.00);

-- 2001年数据
INSERT INTO sales (year, country, product, profit) VALUES
(2001, 'Finland', 'Phone', 10.00),
(2001, 'USA', 'Calculator', 50.00),
(2001, 'USA', 'Computer', 2700.00),
(2001, 'USA', 'TV', 250.00);

我们想要按年份分组并计算每年的总利润，同时使用`WITH ROLLUP`来获取所有年份的总利润。

SELECT year, SUM(profit) AS profit
FROM sales
GROUP BY year WITH ROLLUP;

查询结果：

解释：

• 第一行和第二行分别显示了2000年和2001年的总利润。

• 第三行显示了所有年份的总利润，`year`列用`NULL`表示这是一个汇总行。

示例2：多列分组汇总
如果我们想要按`year`、`country`和`product`分组，并使用`WITH ROLLUP`来获取每个分组级别的汇总。

SELECT year, country, product, SUM(profit) AS profit
FROM sales
GROUP BY year, country, product WITH ROLLUP;

查询结果：

```
+------+---------+------------+--------+
| year | country | product    | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer   |   1500 |
| 2000 | Finland | Phone      |    100 |
| 2000 | Finland | NULL       |   1600 |  -- Finland的总利润
| 2000 | India   | Calculator |    150 |
| 2000 | India   | Computer   |   1200 |
| 2000 | India   | NULL       |   1350 |  -- India的总利润
| 2000 | USA     | Calculator |     75 |
| 2000 | USA     | Computer   |   1500 |
| 2000 | USA     | NULL       |   1575 |  -- USA的总利润
| 2000 | NULL    | NULL       |   4525 |  -- 2000年的总利润
| 2001 | Finland | Phone      |     10 |
| 2001 | Finland | NULL       |     10 |  -- Finland的总利润
| 2001 | USA     | Calculator |     50 |
| 2001 | USA     | Computer   |   2700 |
| 2001 | USA     | TV         |    250 |
| 2001 | USA     | NULL       |   3000 |  -- USA的总利润
| 2001 | NULL    | NULL       |   3010 |  -- 2001年的总利润
| NULL | NULL    | NULL       |   7535 |  -- 所有年份、国家和产品的总利润
+------+---------+------------+--------+
```

解释：

• 每一组`year`、`country`和`product`的行后面，都有一个汇总行，其中`product`列被设置为`NULL`，表示该行是`country`级别的汇总。

• 每个`year`的所有`country`汇总行后面，都有一个`country`和`product`都为`NULL`的行，表示该行是`year`级别的汇总。

• 最后一行`NULL | NULL | NULL`表示所有年份、国家和产品的总利润汇总。

WITH ROLLUP   子句会自动将汇总行的分组字段值设置为   NULL  。如果你想要将这些汇总行的分组字段值设置为自定义的值，比如“汇总”，你需要使用   GROUPING   函数和   IF   函数来在查询中进行条件判断和替换。以下是如何修改之前的多列分组汇总示例，将汇总行的分组字段值设置为“汇总”：

SELECT
  CASE WHEN GROUPING(year) = 1 THEN '汇总' ELSE year END AS year,
  CASE WHEN GROUPING(country) = 1 THEN '汇总' ELSE country END AS country,
  CASE WHEN GROUPING(product) = 1 THEN '汇总' ELSE product END AS product,
  SUM(profit) AS profit
FROM sales
GROUP BY year, country, product WITH ROLLUP;

解释：

• `GROUPING(year)`函数返回一个整数值，如果`year`是汇总行，则返回1，否则返回0。

• `CASE`语句根据`GROUPING`函数的返回值来决定是否将字段值替换为“汇总”。

查询结果：

在这个查询结果中，所有的汇总行都使用了“汇总”作为分组字段的值，而不是`NULL`。这样，你就可以更清晰地识别出哪些行是汇总行。

在MySQL中，除了`WITH ROLLUP`之外，还有其他几个与分组和汇总相关的子句，可以帮助你进行复杂的数据分析。以下是一些常用的子句：
• WITH CUBE：
`WITH CUBE`子句类似于`WITH ROLLUP`，但它不仅生成汇总行，还会生成所有可能的分组组合的汇总。这意味着它会生成更多的汇总行，包括每个分组级别的所有组合。

SELECT year, country, product, SUM(profit) AS profit
FROM sales
GROUP BY year, country, product WITH CUBE;

• WITH GROUPING SETS：
`WITH GROUPING SETS`子句允许你指定多个分组集合，MySQL会为每个集合生成汇总行。这可以看作是`WITH ROLLUP`和`WITH CUBE`的更灵活版本。

SELECT year, country, product, SUM(profit) AS profit
FROM sales
GROUP BY GROUPING SETS ((year), (country), (product), ());

在这个例子中，`GROUPING SETS`指定了四个分组集合：仅按`year`分组、仅按`country`分组、仅按`product`分组，以及一个空集合，它将生成所有记录的总汇总。

• ROLLUP,CUBE,和 GROUPING SETS 的组合：
你可以将`ROLLUP`、`CUBE`或`GROUPING SETS`与`GROUP BY`子句中的列组合使用，以生成更复杂的汇总数据。

SELECT year, country, product, SUM(profit) AS profit
FROM sales
GROUP BY GROUPING SETS ((year, country), (product), ());

这个查询将生成按`year`和`country`分组的汇总、按`product`分组的汇总，以及总汇总。

• GROUP BY 子句的扩展：
在MySQL 8.0及以上版本中，`GROUP BY`子句可以包含不在`SELECT`列表中的列，只要这些列在聚合函数中使用或者在`HAVING`子句中引用。

SELECT year, SUM(profit) AS profit
FROM sales
GROUP BY year;

• 聚合函数：
除了`GROUP BY`子句，MySQL还提供了多种聚合函数，如`SUM()`、`AVG()`、`COUNT()`、`MAX()`、`MIN()`等，用于对分组后的数据进行汇总计算。

• HAVING子句：
`HAVING`子句用于对分组后的结果进行过滤，它通常与聚合函数一起使用。

SELECT year, SUM(profit) AS profit
FROM sales
GROUP BY year
HAVING SUM(profit) > 1000;

这个查询将返回利润总和超过1000的年份。

2.JSON函数的使用

JSON类型以及JSON函数虽然并不是从8.0开始才有的，MySQL从5.7.8版本开始支持JSON数据类型和相关的JSON函数，但是8.0对JSON功能得到了进一步增强，支持JSON文档的索引和部分更新，提升了JSON数据的处理效率。

1.创建数据表：

CREATE TABLE `complex_json_table` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_info` JSON
);

2.插入数据

INSERT INTO `complex_json_table` (`user_info`) VALUES
  ('{"name": "John", "age": 30, "address": {"city": "New York", "zip": "10001"}, "hobbies": ["Reading", "Hiking", "Gaming"], "education": [{"degree": "Bachelor", "field": "Computer Science", "year": 2010}, {"degree": "Master", "field": "Data Science", "year": 2015}]}'),
  ('{"name": "Alice", "age": 25, "address": {"city": "Los Angeles", "zip": "90001"}, "hobbies": ["Painting", "Dancing", "Traveling"], "education": [{"degree": "Bachelor", "field": "Art", "year": 2018}]}');

3.JSON函数的应用

基于`complex_json_table`中的数据，以下是MySQL 8.0中一些JSON函数的用法示例及其解释：

• 2.1 JSON_EXTRACT：

• 从JSON文档中提取值。

SELECT JSON_EXTRACT(user_info, '$.name') AS name
FROM complex_json_table;

解释：这个查询从`user_info`列中提取每个文档的`name`字段的值。

其他查询

SELECT id, JSON_EXTRACT(user_info, '$.address.city') AS city
FROM complex_json_table;

SELECT JSON_EXTRACT(user_info, '$.hobbies[0]') AS first_hobby
FROM complex_json_table;

SELECT JSON_EXTRACT(user_info, '$.education[0].degree') AS first_degree
FROM complex_json_table;

运行结果：

JSON_EXTRACT还有简写的方式 

SELECT
	user_info -> '$.name' AS NAME,
	user_info -> '$.age' AS age 
FROM
	complex_json_table;

运行结果：

箭头符号（->）：• 用于提取JSON对象中的值。不会去除结果中的引号。是对JSON_EXTRACT函数的简写；

 箭头符号（->>）：• 用于提取JSON对象中的值，并自动去除引号。是对JSON_UNQUOTE(JSON_EXTRACT())的简写。

改写：

• 2.2 JSON_SEARCH：

• 搜索JSON文档中匹配指定值的路径。

-- 使用JSON_SEARCH查询嵌套的jsonpath路径：
SELECT JSON_SEARCH(user_info, 'one', 'Data Science') AS path
FROM complex_json_table;

解释：这个查询查找`user_info`列中包含字符串"Data Science"的所有路径。

运行结果：

还可简写省略第二个参数  'one'  ，因为默认就是  'one'  。

• 2.3 JSON_SET：

• 向JSON文档添加或更新键值对。

-- 使用JSON_SET更新嵌套对象中的值
UPDATE complex_json_table
SET user_info = JSON_SET(user_info, '$.address.zip', '10002')
WHERE id = 1;

解释：这个更新语句在`user_info`列的JSON文档中添加或更新`address.zip`键，其值为`10002`。

   JSON_SET   支持一次更新多个路径和对应的值。只需提供多个 path, value 对，即可在一次操作中同时更新多个键。

SELECT JSON_SET('{"name": "Alice", "age": 25}', '$.age', 30, '$.city', 'New York') AS updated_json;

• 2.4 JSON_REMOVE：

• 从JSON文档中删除指定的键。

-- 使用JSON_REMOVE删除嵌套对象中的值
UPDATE complex_json_table
SET user_info = JSON_REMOVE(user_info, '$.hobbies')
WHERE id = 1;

解释：这个更新语句从`user_info`列的JSON文档中删除`hobbies`数组。

• 2.5 JSON_INSERT：

• 向JSON文档中插入新的键值对，如果键已存在则忽略。

-- JSON_INSERT的用法,向JSON文档中插入新的键值对，如果键已存在则忽略
SELECT JSON_INSERT(user_info, '$.education[0].newDegree', 'PhD') AS updated_json
FROM complex_json_table
WHERE id = 1;

解释：这个查询在第一个用户的教育信息中插入一个新的学位`newDegree`，值为`PhD`。

• 2.6 JSON_REPLACE：

• 替换JSON文档中的值。

-- 使用JSON_REPLACE替换JSON文档中的值
SELECT JSON_REPLACE(user_info, '$.address.city', 'New York City') AS updated_json
FROM complex_json_table
WHERE id = 1;

解释：这个查询将第一个用户的`address`对象中的`city`字段的值替换为`New York City`。

• 2.7 JSON_MERGE：

• 合并两个或多个JSON文档。

-- 使用JSON_MERGE合并两个或多个JSON文档
SELECT JSON_MERGE('{"a": 1}', user_info) AS merged_json
FROM complex_json_table
WHERE id = 1;

解释：这个查询将一个简单的JSON对象与`user_info`列中的JSON文档合并。

•2.8 JSON_MERGE_PATCH：

• 合并两个JSON文档，第二个文档中的值会覆盖第一个文档中相同键的值。

-- JSON_MERGE_PATCH合并两个JSON文档，第二个文档中的值会覆盖第一个文档中相同键的值
SELECT JSON_MERGE_PATCH(user_info, '{"address.zip": "10022"}') AS patched_json
FROM complex_json_table
WHERE id = 1;

解释：这个查询将`user_info`列中的JSON文档中的`address.zip`字段更新为`10022`。

• 2.9 JSON_TABLE：

• 将JSON数组或对象转换为虚拟表。

-- 使用JSON_TABLE将嵌套数组转换为表
SELECT jt.hobby
FROM complex_json_table,
JSON_TABLE(
  JSON_EXTRACT(user_info, '$.hobbies'),
  '$[*]' COLUMNS (hobby VARCHAR(255) PATH '$')
) AS jt;

解释：这个查询将`user_info`列中的`hobbies`数组转换为虚拟表，并选择所有的爱好。

运行结果：

JSON_TABLE函数也有简写方式

-- 当JSON数组的元素是简单的标量值时，可以直接在JSON_TABLE中使用COLUMNS定义，而不需要指定PATH
SELECT jt.hobby
FROM complex_json_table, 
JSON_TABLE(
   user_info->'$.hobbies',
   '$[*]' COLUMNS(hobby VARCHAR(255))
) AS jt;

运行结果：

1，可能是JSON路径表达式问题

在  JSON_TABLE  中，路径表达式  '$[*]'  是正确的，但需要确保  user_info->'$.hobbies'  确实是一个JSON数组。如果  user_info->'$.hobbies'  不是一个数组，而是  NULL  或非数组类型，  JSON_TABLE  会报错。解决方法：• 确保  user_info->'$.hobbies'  是一个JSON数组。可以通过以下查询检查：

SELECT user_info->'$.hobbies' FROM complex_json_table;

2.数据完整性问题

如果某些行的  user_info->'$.hobbies'  为空或不是数组，  JSON_TABLE  会报错。为了避免这种情况，可以在查询中添加一个  WHERE  条件，过滤掉不符合要求的行。解决方法：• 添加  WHERE  条件，确保  user_info->'$.hobbies'  是一个非空的JSON数组：

SELECT jt.hobby
FROM complex_json_table,
JSON_TABLE(
    user_info->'$.hobbies',
    '$[*]' COLUMNS(hobby VARCHAR(255) PATH '$')
) AS jt
WHERE JSON_TYPE(user_info->'$.hobbies') = 'ARRAY';

运行结果：

发现还是报错；

 3.COLUMNS  定义问题在

COLUMNS  定义中，路径  PATH  是可选的，但如果不指定  PATH  ，MySQL会默认使用  $  作为路径。你的查询中没有显式指定  PATH  ，这可能会导致问题。

解决方法：

• 显式指定  PATH  ，确保路径正确：

SELECT jt.hobby
FROM complex_json_table,
JSON_TABLE(
    user_info->'$.hobbies',
    '$[*]' COLUMNS(hobby VARCHAR(255) PATH '$')
) AS jt
WHERE JSON_TYPE(user_info->'$.hobbies') = 'ARRAY';

再次运行就正确了：

• 2.10 JSON_KEYS：

• 返回JSON对象中所有键的数组。

SELECT JSON_KEYS(user_info) AS keys
FROM complex_json_table
WHERE id = 1;

解释：这个查询返回第一个用户`user_info`JSON对象中所有的键。

• 2.11 JSON_OVERLAPS：

• 检查一个JSON数组是否与另一个JSON数组重叠。

SELECT JSON_OVERLAPS(user_info->'$.hobbies', '["Reading", "Swimming"]') AS overlaps
FROM complex_json_table
WHERE id = 1;

解释：这个查询检查第一个用户的`hobbies`数组是否与给定的数组`["Reading", "Swimming"]`有重叠的元素。

• 2.12 JSON_ARRAY_APPEND：

• 向JSON数组追加元素。

SELECT JSON_ARRAY_APPEND(user_info, '$.hobbies', '"New Hobby"') AS updated_json
FROM complex_json_table
WHERE id = 1;

解释：这个查询向第一个用户的`hobbies`数组追加一个新的爱好`"New Hobby"`。

• 2.13 JSON_DEPTH：

• 返回JSON文档的深度。

SELECT JSON_DEPTH(user_info) AS depth
FROM complex_json_table
WHERE id = 1;

解释：这个查询返回第一个用户`user_info`JSON文档的深度。

• 2.14 JSON_LENGTH：

• 返回JSON数组的长度或对象中键值对的数量。

SELECT JSON_LENGTH(user_info->'$.hobbies') AS hobbies_count
FROM complex_json_table
WHERE id = 1;

解释：这个查询返回第一个用户的`hobbies`数组的长度。

• 2.15 JSON_VALID：

• 检查字符串是否是有效的JSON。

SELECT JSON_VALID(user_info) AS is_valid
FROM complex_json_table
WHERE id = 1;

解释：这个查询检查第一个用户的`user_info`是否是有效的JSON格式。

• 2.16 JSON_PRETTY：

• 格式化JSON文档，使其更易读。

SELECT JSON_PRETTY(user_info) AS pretty_json
FROM complex_json_table
WHERE id = 1;

解释：这个查询格式化第一个用户的`user_info`JSON文档，使其更易于阅读。

• 2.17 JSON_STORAGE_FREE：

• 返回存储JSON时未分配的空间大小。

SELECT JSON_STORAGE_FREE(user_info) AS free_space
FROM complex_json_table
WHERE id = 1;

解释：这个查询返回存储第一个用户`user_info`JSON文档时未分配的空间大小。

• 2.18 JSON_STORAGE_SIZE：

• 返回存储JSON时分配的空间大小。

`

SELECT JSON_STORAGE_SIZE(user_info) AS size
FROM complex_json_table
WHERE id = 1;

解释：这个查询返回存储第一个用户`user_info`JSON文档时分配的空间大小。

• 2.19 JSON_CONTAINS：

• 检查一个JSON文档是否包含另一个文档。

SELECT JSON_CONTAINS(user_info, '{"name": "John"}') AS contains
FROM complex_json_table
WHERE id = 1;

解释：这个查询检查第一个用户的`user_info`JSON文档是否包含一个具有`name`为`"John"`的文档。

• 2.20 JSON_CONTAINS_PATH：

• 检查JSON文档中是否存在指定路径。

SELECT JSON_CONTAINS_PATH(user_info, 'one', '$.education')
FROM complex_json_table
WHERE id = 1;

解释：这个查询检查第一个用户的`user_info`JSON文档中是否存在路径`$.education`。

继续提供`complex_json_table`中数据的JSON函数用法示例：

• 2.21 JSON_SCHEMA_VALIDATION：

• 验证JSON文档是否符合给定的JSON Schema。

SELECT JSON_SCHEMA_VALIDATION_REPORT(user_info, '{"type": "object", "properties": {"name": {"type": "string"}, "age": {"type": "integer"}}}') AS validation_report
FROM complex_json_table
WHERE id = 1;

解释：这个查询验证第一个用户的`user_info`JSON文档是否符合指定的JSON Schema，Schema要求有一个字符串类型的`name`和一个整数类型的`age`。

• 2.22 JSON_UNQUOTE：

• 从JSON值中移除引号。

SELECT JSON_UNQUOTE(JSON_EXTRACT(user_info, '$.name')) AS name
FROM complex_json_table
WHERE id = 1;

解释：这个查询提取第一个用户的`name`字段，并移除其引号。

• 2.23 JSON_ARRAY：

• 构造一个JSON数组。

SELECT JSON_ARRAY(user_info->'$.name', user_info->'$.age') AS user_array
FROM complex_json_table
WHERE id = 1;

解释：这个查询构造一个包含第一个用户的`name`和`age`的JSON数组。

• 2.24 JSON_OBJECT：

• 构造一个JSON对象。

SELECT JSON_OBJECT('name', user_info->'$.name', 'age', user_info->'$.age') AS user_object
FROM complex_json_table
WHERE id = 1;

解释：这个查询构造一个包含第一个用户的`name`和`age`的JSON对象。

• 2.25 JSON_QUOTE：

• 为JSON值添加引号。

SELECT JSON_QUOTE(user_info->'$.name') AS quoted_name
FROM complex_json_table
WHERE id = 1;

解释：这个查询为第一个用户的`name`字段添加引号。

• 2.26 JSON_EXTRACT_SCALAR：

• 提取JSON文档中的标量值。

SELECT JSON_EXTRACT_SCALAR(user_info, '$.age') AS age
FROM complex_json_table
WHERE id = 1;

解释：这个查询提取第一个用户的`age`字段的值。

•2.27 JSON_SET_DEFAULT：

• 向JSON文档添加键值对，如果键不存在则添加，如果已存在则更新。

SELECT JSON_SET_DEFAULT(user_info, '$.defaultField', 'defaultValue') AS updated_json
FROM complex_json_table
WHERE id = 1;

解释：这个查询在第一个用户的`user_info`JSON文档中添加或更新`defaultField`键，其值为`defaultValue`。

• 2.28 JSON_TABLE with NESTED：

• 使用`JSON_TABLE`处理嵌套的JSON数组。

SELECT jt.name, jt.degree
FROM complex_json_table,
JSON_TABLE(
    JSON_EXTRACT(user_info, '$.education'),
    '$[*]' COLUMNS (
        name VARCHAR(255) PATH '$.name',
        degree VARCHAR(255) PATH '$.degree'
    )
) AS jt;

解释：这个查询将`user_info`列中的`education`数组转换为虚拟表，并选择所有的教育背景中的`name`和`degree`。

• 2.29 JSON_CONTAINS_PATH with all：

• 检查JSON文档中是否存在所有指定的路径。

SELECT JSON_CONTAINS_PATH(user_info, 'all', 'one', '$.name', '$.age') AS contains_all
FROM complex_json_table
WHERE id = 1;

解释：这个查询检查第一个用户的`user_info`JSON文档中是否存在`name`和`age`两个路径。

这些函数提供了对JSON文档进行深度操作的能力，包括构造、验证、提取和转换JSON数据，使得在MySQL中处理JSON数据变得更加灵活和强大。

3.CTE的使用

在 MySQL 8.0 中，CTE（Common Table Expressions，公用表表达式）是一种强大的功能，用于定义临时的结果集，可以在单个 SQL 查询中多次引用。以下是关于 MySQL 8.0 中 CTE 的详细介绍：

3.1.CTE 的基本概念

CTE 是一种临时的结果集，仅在单个 SQL 查询的执行范围内有效。它通过`WITH`关键字定义，并可以像普通表一样在查询中使用。CTE 的主要作用是简化复杂的查询逻辑，提高代码的可读性和可维护性。

3.2.CTE 的语法结构

CTE 的基本语法如下：

WITH cte_name AS (
    -- CTE 的定义部分，通常是 SELECT 查询
)
SELECT ...
FROM cte_name;

• `cte_name`是 CTE 的名称。

• `AS`后面的括号中是 CTE 的定义，通常是一个`SELECT`查询。

• 在主查询中，可以通过`FROM cte_name`或`JOIN cte_name`等方式引用 CTE。

3.3.CTE 的主要用途

（1）简化复杂查询
CTE 可以将复杂的子查询分解为多个简单的步骤，使查询逻辑更加清晰。例如：

WITH OrderSums AS (
    SELECT order_id, SUM(amount) AS total_amount
    FROM order_details
    GROUP BY order_id
)
SELECT o.order_id, o.order_date, os.total_amount
FROM orders o
JOIN OrderSums os ON o.order_id = os.order_id
WHERE os.total_amount > 1000;

在这个例子中，`OrderSums`CTE 负责计算每个订单的总金额，主查询则负责筛选总金额大于 1000 的订单。

（2）递归查询
递归 CTE 是 CTE 的一个重要特性，用于处理层次结构或递归数据。递归 CTE 的语法如下：

WITH RECURSIVE cte_name AS (
    -- 初始查询
    SELECT ...
    UNION ALL
    -- 递归查询
    SELECT ...
    FROM cte_name
)
SELECT ...
FROM cte_name;

例如，查询组织结构中的所有下属员工：

WITH RECURSIVE Subordinates AS (
    SELECT employee_id, manager_id, name
    FROM employees
    WHERE employee_id = 1  -- 起始员工
    UNION ALL
    SELECT e.employee_id, e.manager_id, e.name
    FROM employees e
    INNER JOIN Subordinates s ON e.manager_id = s.employee_id
)
SELECT *
FROM Subordinates;

（3）临时结果集
CTE 可以作为临时表使用，避免创建物理表。例如：

WITH employee_count_per_department AS (
    SELECT department_id, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id
)
SELECT d.department_name, ecpd.employee_count
FROM departments d
JOIN employee_count_per_department ecpd ON d.department_id = ecpd.department_id;

4.CTE 的性能考量
虽然 CTE 提高了查询的可读性，但在某些情况下可能会影响性能：

• 物化与非物化：MySQL 中的 CTE 默认是物化的，即 CTE 的结果会被存储在临时表中。对于大数据集，这可能会增加内存和磁盘 I/O 的开销。

• 递归深度：递归 CTE 的深度需要合理控制，避免过度消耗资源。

• 索引优化：确保 CTE 中涉及的表和列有适当的索引，以提高查询效率。

5.注意事项

• CTE 仅在定义它的查询范围内有效，不能跨查询使用。

• 如果需要在多个查询中重复使用 CTE，可以考虑使用视图或存储过程。

• 在使用递归 CTE 时，必须确保递归有明确的终止条件，否则会导致无限循环。

6.示例：斐波那契数列
以下是一个使用递归 CTE 生成斐波那契数列的示例：

WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS (
    SELECT 1, 0, 1
    UNION ALL
    SELECT n + 1, next_fib_n, fib_n + next_fib_n
    FROM fibonacci
    WHERE n < 10
)
SELECT * FROM fibonacci;

总结
CTE 是 MySQL 8.0 中引入的一项强大功能，它通过简化复杂查询、支持递归查询和提供临时结果集，极大地提高了 SQL 查询的可读性和可维护性。然而，在使用 CTE 时，也需要关注其性能影响，合理优化查询逻辑。

暂时先总结这么多吧