MySQL 高级用法完全指南：从窗口函数到性能调优的实战手册

函数名() OVER (
    [PARTITION BY 列1, 列2, ...]  -- 分区（类似 GROUP BY 的分组）
    [ORDER BY 列 [ASC|DESC], ...] -- 区内排序
    [ROWS BETWEEN ... AND ...]    -- 帧窗口（物理范围）
)

-- 创建演示数据
CREATE TABLE employees (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    name        VARCHAR(50),
    department  VARCHAR(50),
    salary      DECIMAL(10,2),
    hire_date   DATE
);

INSERT INTO employees (name, department, salary, hire_date) VALUES
('张三', '研发部', 15000, '2020-01-15'),
('李四', '研发部', 18000, '2019-03-20'),
('王五', '研发部', 12000, '2021-06-10'),
('赵六', '销售部', 9000,  '2020-08-01'),
('钱七', '销售部', 11000, '2018-12-05'),
('孙八', '销售部', 8500,  '2022-02-14'),
('周九', '人事部', 8000,  '2021-09-01'),
('吴十', '人事部', 9500,  '2019-07-20');

-- 完整窗口函数示例：排名、占比、累计、环比
SELECT
    name,
    department,
    salary,
    hire_date,

    -- ① 区内排名（三种排名方式对比）
    RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rank_normal,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_dense,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,

    -- ② 区内薪资占比：该员工薪资 / 部门薪资总和
    ROUND(
        salary / SUM(salary) OVER (PARTITION BY department) * 100, 2
    ) AS salary_pct_in_dept,

    -- ③ 区内最高、最低薪资
    MAX(salary) OVER (PARTITION BY department) AS dept_max_salary,
    MIN(salary) OVER (PARTITION BY department) AS dept_min_salary,

    -- ④ 区内薪资平均值
    ROUND(AVG(salary) OVER (PARTITION BY department), 2) AS dept_avg_salary,

    -- ⑤ 全公司排名（不分部门）
    RANK() OVER (ORDER BY salary DESC) AS rank_all,

    -- ⑥ 累计薪资（按入职日期顺序）
    SUM(salary) OVER (ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total_salary

FROM employees
ORDER BY department, salary DESC;

-- 场景：电商订单，按月统计并计算环比增长
CREATE TABLE monthly_orders (
    month       DATE PRIMARY KEY,  -- 每月1日
    order_count INT,
    revenue     DECIMAL(12,2)
);

INSERT INTO monthly_orders (month, order_count, revenue) VALUES
('2024-01-01', 1200, 50000),
('2024-02-01', 980,  42000),
('2024-03-01', 1350, 61000),
('2024-04-01', 1100, 48000),
('2024-05-01', 1500, 72000),
('2024-06-01', 1650, 79000);

-- 环比计算：与上月对比
SELECT
    month,
    order_count,
    revenue,

    -- 上月订单数
    LAG(order_count, 1) OVER (ORDER BY month) AS prev_month_orders,
    -- 下月订单数
    LEAD(order_count, 1) OVER (ORDER BY month) AS next_month_orders,

    -- 环比增长率：(本月-上月)/上月 * 100
    CONCAT(
        ROUND(
            (order_count - LAG(order_count,1) OVER (ORDER BY month))
            / LAG(order_count,1) OVER (ORDER BY month) * 100, 2
        ), '%'
    ) AS mom_growth,

    -- 相比三个月前的变化
    ROUND(
        (order_count - LAG(order_count,3) OVER (ORDER BY month))
        / LAG(order_count,3) OVER (ORDER BY month) * 100, 2
    ) AS mom3_growth_pct

FROM monthly_orders;

-- 场景：计算最近3个月的滚动平均订单量
SELECT
    month,
    order_count,

    -- 移动平均：本行及前2行的均值
    ROUND(
        AVG(order_count) OVER (
            ORDER BY month
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ), 2
    ) AS rolling_avg_3month,

    -- 本行及前后各1行（对称窗口）
    ROUND(
        AVG(order_count) OVER (
            ORDER BY month
            ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
        ), 2
    ) AS rolling_avg_centered

FROM monthly_orders;

-- ❌ 子查询地狱（深层嵌套，难以维护）
SELECT *
FROM (
    SELECT *, region,
        (SELECT SUM(sales) FROM orders o2 WHERE o2.region = o1.region) AS region_total
    FROM orders o1
    WHERE status = 'completed'
) t1
WHERE region_total > 100000;

-- ✅ CTE 版本：逻辑清晰，可逐步调试
WITH
active_orders AS (
    SELECT * FROM orders WHERE status = 'completed'
),
regional_totals AS (
    SELECT region, SUM(sales) AS total_sales
    FROM active_orders
    GROUP BY region
),
top_regions AS (
    SELECT region FROM regional_totals WHERE total_sales > 100000
)
SELECT o.*
FROM active_orders o
INNER JOIN top_regions tr ON o.region = tr.region
ORDER BY o.sales DESC;

CREATE TABLE org_chart (
    id         INT PRIMARY KEY,
    name       VARCHAR(50),
    title      VARCHAR(50),
    manager_id INT  -- 上级ID，CEO 为 NULL
);

INSERT INTO org_chart VALUES
(1, '陈总裁', 'CEO',              NULL),
(2, '张副总', '技术副总裁',         1),
(3, '刘副总', '运营副总裁',         1),
(4, '王总监', '后端研发总监',       2),
(5, '赵总监', '前端研发总监',       2),
(6, '孙经理', 'PHP开发组经理',     4),
(7, '周工',   'PHP高级工程师',      6),
(8, '吴工',   'PHP中级工程师',      6),
(9, '郑工',   '前端开发工程师',     5);

-- 递归 CTE：自顶向下遍历整棵树
WITH RECURSIVE org_tree AS (
    -- ① 锚点：CEO（根节点）
    SELECT id, name, title, manager_id, 1 AS level, name AS path
    FROM org_chart
    WHERE manager_id IS NULL

    UNION ALL

    -- ② 递归：不断找直接下属
    SELECT
        o.id, o.name, o.title, o.manager_id,
        ot.level + 1,
        CONCAT(ot.path, ' → ', o.name) AS path  -- 展示完整汇报链
    FROM org_chart o
    INNER JOIN org_tree ot ON o.manager_id = ot.id
)
SELECT
    REPEAT('    ', level - 1) AS indent,  -- 缩进：层级越深，空白越多
    level,
    name,
    title,
    path AS reporting_chain
FROM org_tree
ORDER BY path;

level | name  | title         | reporting_chain
1     | 陈总裁 | CEO           | 陈总裁
2     | 张副总 | 技术副总裁     | 陈总裁 → 张副总
3     | 王总监 | 后端研发总监   | 陈总裁 → 张副总 → 王总监
4     | 孙经理 | PHP开发组经理  | 陈总裁 → 张副总 → 王总监 → 孙经理
5     | 周工   | PHP高级工程师  | 陈总裁 → 张副总 → 王总监 → 孙经理 → 周工
...

CREATE TABLE comments (
    id         INT PRIMARY KEY,
    article_id INT,
    content    VARCHAR(500),
    parent_id  INT  -- 回复的上一级ID，顶级评论为 NULL
);

-- 查询评论树（将嵌套结构展开为扁平列表，含深度）
WITH RECURSIVE comment_tree AS (
    -- 锚点：顶级评论
    SELECT id, article_id, content, parent_id, 0 AS depth, CAST(id AS CHAR(200)) AS path
    FROM comments
    WHERE parent_id IS NULL AND article_id = 1

    UNION ALL

    -- 递归：回复
    SELECT
        c.id, c.article_id, c.content, c.parent_id,
        ct.depth + 1,
        CONCAT(ct.path, ',', c.id) AS path
    FROM comments c
    INNER JOIN comment_tree ct ON c.parent_id = ct.id
)
SELECT depth, id, content, path FROM comment_tree ORDER BY path;

-- 场景一：查询每位员工及其直接主管
SELECT
    e1.name  AS employee,
    e1.title AS emp_title,
    e2.name  AS manager,
    e2.title AS mgr_title
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

-- 场景二：查询同一部门内薪资高于平均值的员工
SELECT
    e1.name     AS employee,
    e1.department,
    e1.salary,
    ROUND(AVG(e2.salary), 2) AS dept_avg_salary
FROM employees e1
INNER JOIN employees e2 ON e1.department = e2.department
GROUP BY e1.id, e1.name, e1.department, e1.salary
HAVING e1.salary > AVG(e2.salary);

-- 场景三：查询连续日期缺口（数据补齐）
-- 场景：用户记录了有数据的日期，但报表需要展示每一天
CREATE TABLE sales_log (
    sale_date DATE,
    amount    DECIMAL(10,2)
);

-- 生成连续日期序列并左连接原始数据（日期缺口一目了然）
SELECT
    d.date_seq  AS missing_date,
    s.amount
FROM (
    SELECT DATE_SUB(CURDATE(), INTERVAL n DAY) AS date_seq
    FROM (
        SELECT @row := @row + 1 AS n
        FROM any_table, (SELECT @row := -6) r  -- 生成最近7天
        LIMIT 7
    ) t
) d
LEFT JOIN sales_log s ON s.sale_date = d.date_seq
ORDER BY d.date_seq;

-- ① 创建库存表和订单表
CREATE TABLE products (
    product_id  INT PRIMARY KEY,
    name        VARCHAR(100),
    price       DECIMAL(10,2),
    stock       INT DEFAULT 0
);

CREATE TABLE orders (
    order_id    INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    product_id  INT,
    quantity    INT,
    total_price DECIMAL(10,2),
    status      VARCHAR(20) DEFAULT 'pending',
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_logs (
    log_id      INT PRIMARY KEY AUTO_INCREMENT,
    order_id    INT,
    action      VARCHAR(50),
    message     VARCHAR(200),
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO products VALUES (1, 'iPhone 16', 7999.00, 50);

-- ② 存储过程：带错误处理和日志记录
DELIMITER $$

CREATE PROCEDURE sp_create_order(
    IN  p_customer_id INT,
    IN  p_product_id  INT,
    IN  p_quantity    INT,
    OUT p_order_id    INT,
    OUT p_status      VARCHAR(100)
)
BEGIN
    DECLARE v_price     DECIMAL(10,2);
    DECLARE v_stock     INT;
    DECLARE v_order_id  INT DEFAULT 0;
    DECLARE v_error     VARCHAR(200) DEFAULT '';

    -- 声明异常处理器
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        GET DIAGNOSTICS CONDITION 1 v_error = MESSAGE_TEXT;
        SET p_status = CONCAT('执行失败: ', v_error);
    END;

    -- 开始事务
    START TRANSACTION;

    -- 锁定行（防止并发超卖）
    SELECT price, stock INTO v_price, v_stock
    FROM products
    WHERE product_id = p_product_id
    FOR UPDATE;  -- 关键：行级锁，并发安全

    -- 库存校验
    IF v_stock < p_quantity THEN
        SET p_status = CONCAT('库存不足：当前 ', v_stock, '，需求 ', p_quantity);
        SET p_order_id = 0;
        ROLLBACK;
    ELSE
        -- 创建订单
        INSERT INTO orders (customer_id, product_id, quantity, total_price, status)
        VALUES (p_customer_id, p_product_id, p_quantity, v_price * p_quantity, 'confirmed');

        SET v_order_id = LAST_INSERT_ID();

        -- 扣减库存
        UPDATE products
        SET stock = stock - p_quantity
        WHERE product_id = p_product_id;

        -- 记录操作日志
        INSERT INTO order_logs (order_id, action, message)
        VALUES (v_order_id, 'CREATE_ORDER',
                CONCAT('创建订单，商品ID=', p_product_id, '，数量=', p_quantity));

        COMMIT;

        SET p_order_id = v_order_id;
        SET p_status  = '订单创建成功';
    END IF;
END$$

DELIMITER ;

-- ③ 调用存储过程
CALL sp_create_order(101, 1, 3, @order_id, @msg);
SELECT @order_id AS 订单ID, @msg AS 状态;

-- ④ 查看执行结果
SELECT * FROM orders WHERE order_id = @order_id;
SELECT * FROM order_logs WHERE order_id = @order_id;
SELECT stock FROM products WHERE product_id = 1;  -- 库存应从50变为47

-- 场景：根据订单金额计算会员折扣价
DELIMITER $$

CREATE FUNCTION fn_calc_discount(
    p_amount   DECIMAL(10,2),
    p_level    TINYINT   -- 1=普通 2=银卡 3=金卡 4=钻石
)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE v_discount_rate DECIMAL(3,2);

    CASE p_level
        WHEN 1 THEN SET v_discount_rate = 1.00;     -- 普通：无折扣
        WHEN 2 THEN SET v_discount_rate = 0.95;     -- 银卡：95折
        WHEN 3 THEN SET v_discount_rate = 0.88;     -- 金卡：88折
        WHEN 4 THEN SET v_discount_rate = 0.80;     -- 钻石：8折
        ELSE         SET v_discount_rate = 1.00;
    END CASE;

    RETURN ROUND(p_amount * v_discount_rate, 2);
END$$

DELIMITER ;

-- 在查询中直接使用
SELECT
    order_id,
    total_price AS 原价,
    customer_level,
    fn_calc_discount(total_price, customer_level) AS 折后价,
    total_price - fn_calc_discount(total_price, customer_level) AS 节省金额
FROM orders
JOIN customers USING (customer_id);

-- 场景：根据不同条件动态构建查询
DELIMITER $$

CREATE PROCEDURE sp_search_orders(
    IN p_status   VARCHAR(20),   -- NULL 表示不限
    IN p_min_amt  DECIMAL(10,2),
    IN p_max_amt  DECIMAL(10,2),
    IN p_limit    INT
)
BEGIN
    SET @sql = 'SELECT * FROM orders WHERE 1=1';

    IF p_status IS NOT NULL THEN
        SET @sql = CONCAT(@sql, " AND status = '", p_status, "'");
    END IF;

    IF p_min_amt IS NOT NULL THEN
        SET @sql = CONCAT(@sql, ' AND total_price >= ', p_min_amt);
    END IF;

    IF p_max_amt IS NOT NULL THEN
        SET @sql = CONCAT(@sql, ' AND total_price <= ', p_max_amt);
    END IF;

    SET @sql = CONCAT(@sql, ' ORDER BY created_at DESC LIMIT ', p_limit);

    -- 预处理并执行动态 SQL
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$

DELIMITER ;

-- 调用：查找所有待支付订单，最多返回10条
CALL sp_search_orders('pending', NULL, NULL, 10);

CREATE INDEX idx_user ON users (status, created_at, email);

-- ✅ 命中索引的场景（必须包含最左列 status）
SELECT * FROM users WHERE status = 'active';                    -- 命中 A
SELECT * FROM users WHERE status = 'active' AND created_at > '2025-01-01';  -- 命中 A+B
SELECT * FROM users WHERE status = 'active' AND created_at > '2025-01-01' AND email = 'x@y.com'; -- 命中 A+B+C

-- ❌ 不命中索引（跳过了最左列 A）
SELECT * FROM users WHERE created_at > '2025-01-01';              -- 不命中
SELECT * FROM users WHERE email = 'x@y.com';                      -- 不命中

-- 常见新闻列表页：只需 id、title、created_at、status
-- 查询只需这4个字段，idx_news_cover 覆盖全部，无需回表
CREATE INDEX idx_news_cover ON news (status, created_at DESC, id, title);

SELECT id, title, created_at, status
FROM news
WHERE status = 'published'
ORDER BY created_at DESC
LIMIT 20;

-- 前5个字符的索引，节省空间，适用于以该字段做 LIKE 前缀查询
CREATE INDEX idx_email_prefix ON users (email(5));

-- 验证前缀长度是否够用（区分度测试）
SELECT
    COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS selectivity_5,
    COUNT(DISTINCT LEFT(email, 8)) / COUNT(*) AS selectivity_8,
    COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS selectivity_10
FROM users;
-- 区分度越高（前缀越长），查询越精确

-- ❌ 低效：为每个字段单独建索引，查询时 MySQL 每次只选一个
INDEX idx_status  ON orders(status);
INDEX idx_created ON orders(created_at);
INDEX idx_customer ON orders(customer_id);

-- ✅ 高效：一个复合索引覆盖所有查询条件
INDEX idx_order_search ON orders(customer_id, status, created_at);

-- 这条查询三个字段都在索引中，MySQL 一次定位，无需回表
SELECT customer_id, status, created_at
FROM orders
WHERE customer_id = 101
  AND status = 'shipped'
  AND created_at BETWEEN '2025-01-01' AND '2025-03-31';

-- ❌ 函数包裹列，无法使用索引（全表扫描）
SELECT * FROM orders
WHERE YEAR(created_at) = 2025
  AND MONTH(created_at) = 3;

-- ✅ 范围查询，保持列独立，索引正常命中
SELECT * FROM orders
WHERE created_at >= '2025-03-01'
  AND created_at <  '2025-04-01';

-- ❌ 性别（2种）、状态（3种）放复合索引最左 → 区分度极低，索引意义不大
INDEX idx_gender_status ON users(gender, status, created_at);

-- ✅ 交换顺序：高区分度列（status）放前面
INDEX idx_status ON users(status, created_at);

EXPLAIN ANALYZE  -- MySQL 8.0+，实际执行并返回估算 vs 实际成本
SELECT
    o.order_id,
    u.username,
    p.product_name,
    o.quantity,
    o.total_price
FROM orders o
INNER JOIN users u  ON o.customer_id = u.id
INNER JOIN products p ON o.product_id = p.id
WHERE o.status = 'shipped'
  AND o.created_at >= '2025-01-01';

-- 开启慢查询日志（生产环境）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 超过1秒记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 查看慢查询
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 案例：优化前慢查询（执行时间 3.2s）
-- 原 SQL
SELECT *
FROM orders o
JOIN users u ON o.customer_id = u.id
WHERE o.status = 'cancelled'
  AND o.created_at >= '2025-01-01'
ORDER BY o.created_at DESC
LIMIT 100;

-- EXPLAIN 分析：
-- type=ALL（全表扫描）, rows=500000+, extra=Using filesort

-- ✅ 优化后（执行时间 0.08s）
-- 1. 添加复合索引覆盖 WHERE + ORDER BY
ALTER TABLE orders ADD INDEX idx_cancel_time (status, created_at DESC);

-- 2. 覆盖索引，直接返回所需字段，无需回表
SELECT o.order_id, o.customer_id, o.total_price, o.created_at,
       u.username, u.email
FROM orders o
INNER JOIN users u ON o.customer_id = u.id
WHERE o.status = 'cancelled'
  AND o.created_at >= '2025-01-01'
ORDER BY o.created_at DESC
LIMIT 100;

-- 验证：type=range, key=idx_cancel_time, rows=120, extra=Using index

-- ❌ 危险写法：IN 子查询在大表上性能差
SELECT * FROM orders
WHERE customer_id IN (
    SELECT id FROM customers
    WHERE region = '华东' AND level >= 3
);
-- MySQL 对内表的外键列无索引时，会将子查询物化为临时表，全表扫描

-- ✅ 方案一：EXISTS（相关子查询，找到第一条即停）
SELECT * FROM orders o
WHERE EXISTS (
    SELECT 1 FROM customers c
    WHERE c.id = o.customer_id
      AND c.region = '华东'
      AND c.level >= 3
);

-- ✅ 方案二：JOIN（MySQL 优化器处理更灵活）
SELECT DISTINCT o.*
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE c.region = '华东' AND c.level >= 3;

-- ✅ 方案三：派生表（子查询先执行，结果集通常较小）
SELECT o.*
FROM orders o
INNER JOIN (
    SELECT id FROM customers
    WHERE region = '华东' AND level >= 3
) c ON o.customer_id = c.id;

-- 生产环境推荐配置
CREATE DATABASE production_db
    DEFAULT CHARACTER SET utf8mb4          -- 完整 UTF-8，支持 emoji 和所有 Unicode 字符
    DEFAULT COLLATE utf8mb4_unicode_ci;    -- Unicode 排序规则，支持多语言自然排序

CREATE TABLE products (
    -- 主键：自增 BIGINT 更稳妥（INT 最大 21 亿）
    id              BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

    -- 价格：DECIMAL 精确存储（避免浮点误差），VARCHAR 存金额是大忌
    price           DECIMAL(10,2) NOT NULL DEFAULT 0.00,

    -- 短文本：VARCHAR(255) 够用，CHAR 是定长，空间浪费
    product_name    VARCHAR(255) NOT NULL,

    -- 长文本：TEXT 不建立索引，如需全文搜索用 FULLTEXT 索引
    description     TEXT,

    -- 状态：ENUM 语义清晰，数据库层校验合法值
    status          ENUM('draft','active','offline','deleted') DEFAULT 'draft',

    -- 时间戳：TIMESTAMP 自动更新，比 DATETIME 节省 4 字节
    created_at      TIMESTAMP   DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP   DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    -- 地理信息：MySQL 8.0+ 原生空间数据类型
    location        POINT,
    delivery_zone   POLYGON,

    -- JSON 数据：MySQL 5.7+ 原生支持，无需单独建表
    -- 适合灵活扩展字段（如商品规格、用户偏好）
    specs           JSON,

    -- IP地址：存储为无符号 INT，节省空间，支持排序
    last_login_ip   INT UNSIGNED
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 场景：商品规格（尺寸、颜色、重量随时变化，不适合固定列）
CREATE TABLE products (
    id    INT PRIMARY KEY AUTO_INCREMENT,
    name  VARCHAR(255),
    specs JSON  -- 动态规格，不固定字段结构
);

INSERT INTO products (name, specs) VALUES ('iPhone 16', '
{
    "colors": ["黑色", "白色", "蓝色"],
    "storage": ["128GB", "256GB", "512GB"],
    "weight": "170g",
    "display": { "size": "6.1英寸", "type": "OLED" }
}
');

-- JSON 查询函数（MySQL 8.0+）
SELECT
    name,
    JSON_EXTRACT(specs, '$.weight')                      AS weight,
    JSON_EXTRACT(specs, '$.colors[0]')                   AS first_color,
    JSON_EXTRACT(specs, '$.display.type')                 AS display_type,
    JSON_VALUE(specs, '$.display.size')                  AS display_size,  -- 直接返回字符串
    JSON_KEYS(specs)                                     AS all_keys       -- 返回所有 key 数组
FROM products;

-- 更新 JSON 字段（局部更新，不影响其他字段）
UPDATE products
SET specs = JSON_SET(specs, '$.colors', JSON_ARRAY('黑色', '银色', '金色'))
WHERE id = 1;

-- 搜索 JSON 数组中的值（索引支持）
ALTER TABLE products ADD INDEX idx_storage ((CAST(specs->'$.storage' AS CHAR(50) ARRAY)));

-- ① 基础函数
SELECT
    NOW()         AS 当前时间,         -- 2025-04-11 23:21:00
    CURDATE()     AS 当前日期,         -- 2025-04-11
    CURTIME()     AS 当前时间,         -- 23:21:00
    UTC_TIMESTAMP() AS UTC时间;       -- UTC时区

-- ② 日期加减：查询最近 N 天的数据
SELECT * FROM orders
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)   -- 最近7天
  AND created_at <= DATE_ADD(NOW(), INTERVAL 1 DAY);   -- 明天23:59前

-- ③ 日期差计算
SELECT
    DATEDIFF('2025-12-31', CURDATE())    AS 距离年末天数,
    TIMESTAMPDIFF(HOUR, created_at, NOW()) AS 订单距今小时数,
    TIMESTAMPDIFF(DAY, created_at, NOW())  AS 订单距今天数;

-- ④ 日期截断（MySQL 8.0+）
SELECT
    created_at,
    DATE(created_at)                        AS 当天日期,
    DATE_FORMAT(created_at, '%Y-%m')        AS 年月,
    DATE_TRUNC('MONTH', created_at)        AS 月初（需 MySQL 8.0.28+）;

-- ⑤ 每月第 N 天 / 每周第 N 天
SELECT
    DAYOFWEEK(created_at)  AS 周几(1=周日),
    DAYOFMONTH(created_at) AS 几号,
    LAST_DAY(created_at)   AS 月末,
    DAYNAME(created_at)     AS 星期名称;

<?php
// ❌ 危险：字符串拼接，100% 可被 SQL 注入
$sql = "SELECT * FROM users WHERE email = '" . $_POST['email'] . "'";
mysqli_query($db, $sql);

// ✅ 安全：预处理语句（PDO）
$pdo = new PDO('mysql:host=localhost;dbname=shop', 'root', 'password');
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);  // 关闭模拟预编译

$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email LIMIT 1');
$stmt->execute(['email' => $_POST['email']]);
$user = $stmt->fetch(PDO::FETCH_ASSOC);

// ✅ 安全：mysqli 预处理
$stmt = $mysqli->prepare('SELECT * FROM users WHERE email = ? LIMIT 1');
$stmt->bind_param('s', $_POST['email']);
$stmt->execute();
$result = $stmt->get_result();

-- 创建应用专用账户（只能访问指定数据库和表）
CREATE USER 'app_reader'@'%' IDENTIFIED BY 'Str0ngP@ss!';
GRANT SELECT ON shop.products TO 'app_reader'@'%';   -- 只读

CREATE USER 'app_writer'@'%' IDENTIFIED BY 'Str0ngP@ss!';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.orders TO 'app_writer'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.products TO 'app_writer'@'%';
GRANT EXECUTE ON PROCEDURE shop.sp_create_order TO 'app_writer'@'%';  -- 只授权指定存储过程

FLUSH PRIVILEGES;

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

-- 场景：为 5000 万行的 orders 表添加索引，直接 ALTER 会锁表
-- 使用 pt-online-schema-change（Percona Toolkit）在线变更

-- ① 安装 Percona Toolkit
-- yum install percona-toolkit

-- ② 在线加索引（不锁表，实时同步数据）
pt-online-schema-change \
    --alter "ADD INDEX idx_customer_date (customer_id, created_at)" \
    D=t_shop,t=orders \
    --execute \
    --charset=utf8mb4 \
    --chunk-size=1000 \
    --max-load="Threads_running=50" \
    --critical-load="Threads_running=100"

-- ③ 原理：
--    1. 创建新表（新索引结构）
--    2. 逐步从原表复制数据到新表
--    3. 同步期间原表的所有写操作通过触发器同步到新表
--    4. 切换新旧表（毫秒级锁）
--    5. 删除原表