MySQL 8 教程

示例：查看默认字符集设置

-- 查看MySQL服务器的默认字符集
SHOW VARIABLES LIKE 'character_set_server';

-- 查看MySQL服务器的默认排序规则
SHOW VARIABLES LIKE 'collation_server';

-- MySQL 8.0输出示例
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| character_set_server | utf8mb4            |
+----------------------+--------------------+

+------------------+--------------------+
| Variable_name    | Value              |
+------------------+--------------------+
| collation_server | utf8mb4_0900_ai_ci |
+------------------+--------------------+

示例：自增变量的使用

-- 创建一个带有自增主键的表
CREATE TABLE test_auto_increment (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

-- 插入几条数据
INSERT INTO test_auto_increment (name) VALUES ('Alice'), ('Bob'), ('Charlie');

-- 查看插入的数据
SELECT * FROM test_auto_increment;

-- 输出示例
+----+---------+
| id | name    |
+----+---------+
|  1 | Alice   |
|  2 | Bob     |
|  3 | Charlie |
+----+---------+

-- 重启MySQL服务器后，继续插入数据
INSERT INTO test_auto_increment (name) VALUES ('David');

-- 查看插入的数据，id会从4开始，而不是重新从1开始
SELECT * FROM test_auto_increment;

-- 输出示例
+----+---------+
| id | name    |
+----+---------+
|  1 | Alice   |
|  2 | Bob     |
|  3 | Charlie |
|  4 | David   |
+----+---------+

示例：原子DDL操作

-- 开始事务
START TRANSACTION;

-- 执行一个复杂的DDL操作
ALTER TABLE employees
    ADD COLUMN department_id INT,
    ADD CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(id),
    MODIFY COLUMN salary DECIMAL(12,2);

-- 如果执行成功，提交事务
COMMIT;
-- 如果执行失败，回滚事务
-- ROLLBACK;

示例：创建和使用降序索引

-- 创建一个包含降序索引的表
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2),
    created_at DATETIME,
    INDEX idx_price_desc (price DESC),
    INDEX idx_created_price (created_at DESC, price ASC)
);

-- 查询时使用降序索引
EXPLAIN SELECT * FROM products ORDER BY price DESC LIMIT 10;

-- 输出示例（部分）
+----+-------------+----------+------------+-------+---------------+----------------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key            | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+----------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | products | NULL       | index | NULL          | idx_price_desc | 5       | NULL |   10 |   100.00 | Using index; Using filesort |
+----+-------------+----------+------------+-------+---------------+----------------+---------+------+------+----------+--------------------------+

示例：创建和管理直方图

-- 为表的列创建直方图
ANALYZE TABLE products UPDATE HISTOGRAM ON price WITH 10 BUCKETS;

-- 查看直方图信息
SELECT * FROM information_schema.column_statistics WHERE table_name = 'products';

-- 删除直方图
ANALYZE TABLE products DROP HISTOGRAM ON price;

-- 自动为所有适合的列创建直方图
SET GLOBAL default_statistics_target = 100;
ANALYZE TABLE products;

示例：GROUP BY子句的使用

-- MySQL 8.0之前，这个查询的结果会自动按照department_id排序
-- MySQL 8.0开始，结果不会自动排序
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

-- 如果需要排序，必须显式使用ORDER BY子句
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
ORDER BY department_id;

-- 如果需要恢复旧的行为，可以设置系统变量
SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,GROUP_BY_OLD';

示例：使用非递归CTE

-- 使用CTE简化复杂查询
WITH high_salary_employees AS (
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE salary > 10000
)
SELECT department_id, COUNT(*) AS num_high_salary
FROM departments d
JOIN high_salary_employees e ON d.department_id = e.department_id
GROUP BY department_id;

-- 多个CTE的使用
WITH
    dept_stats AS (
        SELECT department_id, AVG(salary) AS avg_salary, COUNT(*) AS num_employees
        FROM employees
        GROUP BY department_id
    ),
    high_perf_depts AS (
        SELECT department_id
        FROM dept_stats
        WHERE avg_salary > 8000 AND num_employees > 10
    )
SELECT * FROM departments WHERE department_id IN (SELECT department_id FROM high_perf_depts);

示例：使用递归CTE查询层次结构数据

-- 假设有一个组织结构表
CREATE TABLE organization (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT
);

-- 使用递归CTE查询某个员工的所有下属
WITH RECURSIVE subordinates AS (
    -- 基础查询：指定起始员工
    SELECT employee_id, name, manager_id, 0 AS level
    FROM organization
    WHERE employee_id = 1
    UNION ALL
    -- 递归查询：查找下属
    SELECT o.employee_id, o.name, o.manager_id, s.level + 1
    FROM organization o
    JOIN subordinates s ON o.manager_id = s.employee_id
)
SELECT * FROM subordinates ORDER BY level;

示例：使用MD5函数

-- 计算字符串的MD5哈希值
SELECT MD5('password123');

-- 输出示例
+----------------------------------+
| MD5('password123')               |
+----------------------------------+
| 482c811da5d5b4bc6d497ffa98491e38 |
+----------------------------------+

示例：使用SHA函数

-- 计算字符串的SHA-1哈希值
SELECT SHA('password123');

-- 输出示例
+------------------------------------------+
| SHA('password123')                       |
+------------------------------------------+
| 40bd001563085fc35165329ea1ff5c5ecbdbbeef |
+------------------------------------------+

示例：使用SHA2函数

-- 计算字符串的SHA-256哈希值
SELECT SHA2('password123', 256);

-- 输出示例
+------------------------------------------------------------------+
| SHA2('password123', 256)                                         |
+------------------------------------------------------------------+
| ef92b778bafe771e89245b89ecbc08a44a4e166c06659911881f383d4473e94f |
+------------------------------------------------------------------+

-- 计算字符串的SHA-512哈希值
SELECT SHA2('password123', 512);

-- 输出示例（部分）
+------------------------------------------------------------------------------------------------------------------------------------+
| SHA2('password123', 512)                                                                                                           |
+------------------------------------------------------------------------------------------------------------------------------------+
| bd1cfa194c3a603e7187f6a92dddc01380058364c0c0e5c8c4a8724e5e1bd981... |
+------------------------------------------------------------------------------------------------------------------------------------+

示例：使用窗口函数

-- 使用ROW_NUMBER()函数为每行分配一个唯一的序号
SELECT employee_id, department_id, salary,
       ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num
FROM employees;

-- 使用RANK()函数计算排名（相同值会有相同排名，下一个排名会跳跃）
SELECT employee_id, department_id, salary,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_num
FROM employees;

-- 使用DENSE_RANK()函数计算密集排名（相同值会有相同排名，下一个排名不会跳跃）
SELECT employee_id, department_id, salary,
       DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank_num
FROM employees;

-- 计算累计和
SELECT order_date, amount,
       SUM(amount) OVER (ORDER BY order_date) AS cumulative_sum
FROM orders;

-- 计算移动平均
SELECT order_date, amount,
       AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM orders;

示例：持久化全局变量

-- 使用SET PERSIST语句持久化全局变量
SET PERSIST max_connections = 200;

-- 查看持久化的变量值
SELECT * FROM performance_schema.persisted_variables WHERE variable_name = 'max_connections';

-- 删除持久化的变量值，使变量恢复为配置文件中的值
RESET PERSIST max_connections;

-- 删除所有持久化的变量值
RESET PERSIST ALL;

示例：角色管理

-- 创建角色
CREATE ROLE 'read_only', 'data_entry', 'administrator';

-- 为角色授予权限
GRANT SELECT ON database_name.* TO 'read_only';
GRANT SELECT, INSERT, UPDATE ON database_name.* TO 'data_entry';
GRANT ALL PRIVILEGES ON database_name.* TO 'administrator';

-- 创建用户
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password1';
CREATE USER 'user2'@'localhost' IDENTIFIED BY 'password2';

-- 将角色授予用户
GRANT 'read_only' TO 'user1'@'localhost';
GRANT 'data_entry' TO 'user2'@'localhost';

-- 设置用户的默认角色
SET DEFAULT ROLE 'read_only' FOR 'user1'@'localhost';
SET DEFAULT ROLE 'data_entry' FOR 'user2'@'localhost';

-- 查看角色的权限
SHOW GRANTS FOR 'read_only';

-- 查看用户的权限（包括通过角色获得的权限）
SHOW GRANTS FOR 'user1'@'localhost' USING 'read_only';

-- 撤销角色的权限
REVOKE INSERT ON database_name.* FROM 'data_entry';

-- 从用户处撤销角色
REVOKE 'read_only' FROM 'user1'@'localhost';

-- 删除角色
DROP ROLE 'read_only';

示例：配置和管理日志

-- 查看错误日志配置
SHOW VARIABLES LIKE 'log_error';

-- 查看二进制日志配置
SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_format';

-- 查看慢查询日志配置
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

-- 启用慢查询日志
SET GLOBAL slow_query_log = ON;

-- 修改慢查询阈值（单位：秒）
SET GLOBAL long_query_time = 2;

-- 查看当前的二进制日志文件列表
SHOW BINARY LOGS;

-- 刷新二进制日志
FLUSH BINARY LOGS;

-- 清除二进制日志（保留最近的2个）
PURGE BINARY LOGS TO 'binlog.000010';

示例：创建和管理不可见索引

-- 创建不可见索引
CREATE INDEX idx_name ON employees(name) INVISIBLE;

-- 将现有索引设置为不可见
ALTER TABLE employees ALTER INDEX idx_salary INVISIBLE;

-- 将不可见索引设置为可见
ALTER TABLE employees ALTER INDEX idx_salary VISIBLE;

-- 查看索引是否可见
SELECT index_name, is_visible
FROM information_schema.statistics
WHERE table_schema = 'database_name' AND table_name = 'employees';

-- 即使索引不可见，强制使用该索引（用于测试）
SELECT /*+ INDEX(employees idx_name) */ * FROM employees WHERE name = 'John';

示例：创建和管理资源组

-- 创建资源组
CREATE RESOURCE GROUP high_priority
    TYPE = USER
    VCPU = 0,1
    THREAD_PRIORITY = 10;

CREATE RESOURCE GROUP low_priority
    TYPE = USER
    VCPU = 2,3
    THREAD_PRIORITY = -10;

-- 将用户会话分配到资源组
SET RESOURCE GROUP high_priority;

-- 将存储过程分配到资源组
ALTER PROCEDURE my_procedure
    RESOURCE GROUP = high_priority;

-- 查看资源组配置
SELECT * FROM information_schema.resource_groups;

-- 修改资源组
ALTER RESOURCE GROUP high_priority
    VCPU = 0,1,2;

-- 删除资源组
DROP RESOURCE GROUP low_priority;