MySQL 8.0一些隐藏特性

MySQL 8.0 有许多隐藏特性

窗口函数

窗口函数能够在特定的行集（窗口）内进行计算，而不用将结果分组为单个输出行。这在需要对结果集进行复杂计算时极为有用。

 

-- 查询员工表中，每个部门内员工的工资排名
SELECT 
    department_id,
    employee_id,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as salary_rank
FROM 
    employees;

在这个例子里，RANK() 是窗口函数，PARTITION BY 按部门划分窗口，ORDER BY 对每个部门内的员工按工资降序排序。

公用表表达式（CTE）递归

CTE 递归允许在单个 SQL 查询中定义递归查询，可用于处理层次结构数据，像组织结构图、文件系统目录等。

 

-- 递归查询员工及其下属
WITH RECURSIVE employee_hierarchy AS (
    SELECT 
        employee_id,
        manager_id,
        employee_name
    FROM 
        employees
    WHERE 
        manager_id IS NULL
    UNION ALL
    SELECT 
        e.employee_id,
        e.manager_id,
        e.employee_name
    FROM 
        employees e
    JOIN 
        employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT 
    *
FROM 
    employee_hierarchy;

此例中，第一个 SELECT 语句定义了递归的起始点（顶级员工），UNION ALL 后面的 SELECT 语句则递归地查找下属员工。

降序索引

在 MySQL 8.0 里，可以创建降序索引，这在处理降序排序查询时能提升性能。

 

-- 创建一个包含降序索引的表
CREATE TABLE sales (
    sale_date DATE,
    amount DECIMAL(10, 2),
    INDEX idx_sale_date_amount (sale_date, amount DESC)
);

在这个例子中，amount 列上的索引是降序的，这对按 sale_date 分组并按 amount 降序排序的查询有帮助。

角色管理

MySQL 8.0 引入了角色管理功能，可简化权限管理。角色是一组权限的集合，可将角色分配给用户。

 

-- 创建一个名为 'sales_manager' 的角色
CREATE ROLE sales_manager;
-- 授予角色对 'sales' 表的 SELECT、INSERT、UPDATE 权限
GRANT SELECT, INSERT, UPDATE ON sales TO sales_manager;
-- 将角色分配给用户 'john_doe'
GRANT sales_manager TO 'john_doe'@'localhost';

隐藏索引

隐藏索引不会被查询优化器使用，但可以保留在表中。这在测试索引对性能的影响时非常有用，不用删除索引。

 

-- 创建一个隐藏索引
CREATE INDEX idx_hidden ON employees (last_name) INVISIBLE;
-- 可以将隐藏索引变为可见
ALTER INDEX idx_hidden ON employees VISIBLE;

不可见列

不可见列在查询结果中默认不显示，但仍可通过显式指定列名来访问。

 

-- 创建一个包含不可见列的表
CREATE TABLE users (
    user_id INT,
    username VARCHAR(50),
    password VARCHAR(255) INVISIBLE
);
-- 查询表，默认不显示 password 列
SELECT * FROM users;
-- 显式查询 password 列
SELECT user_id, username, password FROM users;