-- 创建隐藏索引
CREATE INDEX idx_name ON users(name) INVISIBLE;

-- 修改索引可见性
ALTER TABLE users ALTER INDEX idx_name INVISIBLE;
ALTER TABLE users ALTER INDEX idx_name VISIBLE;

-- 查看索引
SHOW INDEX FROM users;
-- Visible: YES/NO 显示可见性

-- MySQL 8.0 之前：降序索引被忽略
-- MySQL 8.0：正确支持降序索引

CREATE TABLE t (
    a INT,
    b INT,
    INDEX idx (a ASC, b DESC)
);

-- 优化器可以利用索引正确执行
SELECT * FROM t ORDER BY a ASC, b DESC;

-- 基于函数创建索引
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    order_no VARCHAR(32),
    created_at DATETIME
);

-- 为 email 前缀创建索引
CREATE INDEX idx_email_prefix ON users(LEFT(email, 10));

-- 为 JSON 字段创建索引
CREATE INDEX idx_data ON orders((CAST(data->>'$.status' AS UNSIGNED)));

-- 递归 CTEs 查询组织架构
WITH RECURSIVE org_tree AS (
    -- 起始条件：CEO
    SELECT 
        id, 
        name, 
        manager_id, 
        1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 递归条件：下属
    SELECT 
        e.id,
        e.name,
        e.manager_id,
        o.level + 1
    FROM employees e
    INNER JOIN org_tree o ON e.manager_id = o.id
)
SELECT * FROM org_tree;

-- 简化复杂查询
WITH active_users AS (
    SELECT id, name, email
    FROM users
    WHERE status = 1
),
user_orders AS (
    SELECT user_id, COUNT(*) as order_count
    FROM orders
    GROUP BY user_id
)
SELECT 
    u.id,
    u.name,
    COALESCE(o.order_count, 0) as order_count
FROM active_users u
LEFT JOIN user_orders o ON u.id = o.user_id;

-- 查询每个部门工资排名
SELECT 
    name,
    department,
    salary,
    ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC) as row_num,
    RANK() OVER(PARTITION BY department ORDER BY salary DESC) as rank,
    DENSE_RANK() OVER(PARTITION BY department ORDER BY salary DESC) as dense_rank
FROM employees;

-- 结果示例
-- department=A, salary=10000 -> row_num=1, rank=1, dense_rank=1
-- department=A, salary=9000  -> row_num=2, rank=2, dense_rank=2
-- department=A, salary=9000  -> row_num=3, rank=2, dense_rank=2

-- 查询订单金额环比
SELECT 
    order_date,
    amount,
    LAG(amount, 1) OVER(ORDER BY order_date) as prev_amount,
    amount - LAG(amount, 1) OVER(ORDER BY order_date) as diff,
    ROUND(
        (amount - LAG(amount, 1) OVER(ORDER BY order_date)) / 
        LAG(amount, 1) OVER(ORDER BY order_date) * 100, 2
    ) as growth_rate
FROM orders;

-- 查询累计销售额
SELECT 
    order_date,
    daily_amount,
    SUM(daily_amount) OVER(ORDER BY order_date) as cumulative,
    AVG(daily_amount) OVER(ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as rolling_7d_avg
FROM daily_sales;

-- 创建 JSON 字段
CREATE TABLE products (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100),
    specs JSON
);

-- 插入 JSON 数据
INSERT INTO products VALUES (
    1, 
    '手机',
    '{"color": "黑色", "memory": "8GB", "storage": "256GB"}'
);

-- JSON 函数
SELECT 
    JSON_EXTRACT(specs, '$.memory') as memory,
    specs->>'$.memory' as memory_unquote
FROM products;

-- 更新 JSON
UPDATE products
SET specs = JSON_SET(specs, '$.memory', '12GB')
WHERE id = 1;

-- 将 JSON 数组转为表
SELECT *
FROM JSON_TABLE(
    '[{"name":"产品A","price":100}, {"name":"产品B","price":200}]',
    '$[*]' COLUMNS (
        name VARCHAR(50) PATH '$.name',
        price INT PATH '$.price'
    )
) as products;

-- 创建角色
CREATE ROLE app_read, app_write, app_admin;

-- 授予权限
GRANT SELECT ON app_db.* TO app_read;
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO app_write;
GRANT ALL ON app_db.* TO app_admin;

-- 创建用户并分配角色
CREATE USER 'dev_user'@'%' IDENTIFIED BY 'password';
GRANT app_read, app_write TO 'dev_user'@'%';

-- 设置默认角色
SET DEFAULT ROLE app_read FOR 'dev_user'@'%';

-- 查看用户权限
SHOW GRANTS FOR 'dev_user'@'%';

-- 查看角色权限
SHOW GRANTS FOR app_read;

-- 激活角色
SET ROLE app_read;
SET ROLE ALL;

-- 直接使用窗口函数
SELECT DISTINCT 
    department,
    FIRST_VALUE(name) OVER(PARTITION BY department ORDER BY salary DESC) as highest_paid
FROM employees;

-- 创建资源组
CREATE RESOURCE GROUP big_query TYPE=USER;

-- 将线程分配到资源组
SET RESOURCE GROUP big_query FOR 1234;

-- 支持 utf8mb4_0900_ai_ci 排序规则
CREATE TABLE t (
    name VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci
);

-- MySQL 8.0 DDL 支持原子性
-- DROP TABLE t1, t2; 
-- 如果失败，会回滚整个操作

# 1. 检查兼容性
mysql_upgrade -u root -p

# 2. 检查系统变量
mysqlcheck -u root -p --check-upgrade

# 3. 备份数据
mysqldump -u root -p --all-databases > /backup/all.sql