视图与存储过程

这一课你将能做到

1. 创建、查询、替换、删除视图，并说清哪些视图可更新、哪些不行（含聚合的视图不可 DML）
2. 编写一个带参数校验、含事务的存储过程并用 CALL 执行
3. 用条件聚合（CASE + SUM）在 MySQL 里实现行转列报表

为什么要学

「用户 AC 统计」这条 SQL 你在前面几课已经写了四遍。报表类查询的特点是反复出现、略有变化、复杂易错——视图就是给这类查询起名字；而「给达标的用户批量升级」这种多步写入，封装成存储过程后一条 CALL 搞定，还天然在数据库侧保证原子性。更重要的是，面试常问「视图和表的区别」「什么场景用存储过程」，答不好会显得没碰过真实系统。

人话：视图是一段「保存下来的 SELECT」，每次查询它都实时重算；存储过程是「存在数据库里的函数」，适合定时任务和批量修正。视图不存数据、过程不返回结果集才是常态——理解这两点就不会用歪。

先建立图像

         ┌─────────────── 虚拟表（视图）───────────────┐
         │  v_user_ac_stat                            │
         │  username │ total_sub │ ac_cnt │ ac_rate   │
         └────────────────────┬───────────────────────┘
                              │ 每次查询时实时展开执行
              ┌───────────────┴───────────────┐
              ▼                               ▼
         cf_user（基表）                 cf_submission（基表）
         真实存储数据                    真实存储数据

存储过程：CALL sp_promote_users(2)
         ┌─ 参数校验 ─► 开事务 ─► UPDATE ─► 提交 ─┐
         └─ 整个过程在数据库侧一次完成 ──────────┘

跟着做

步骤 0：数据准备

USE sql_practice;
TRUNCATE TABLE cf_submission;
TRUNCATE TABLE cf_practice_record;
TRUNCATE TABLE cf_user;
TRUNCATE TABLE cf_problem;
-- 执行第 1 课步骤 4 的四段 INSERT，恢复初始数据

步骤 1：引入课程目录表（本课新角色）

CREATE TABLE IF NOT EXISTS cf_track (
  id       INT UNSIGNED NOT NULL AUTO_INCREMENT,
  slug     VARCHAR(80)  NOT NULL,
  title    VARCHAR(200) NOT NULL,
  category VARCHAR(40)  NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_slug (slug)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS cf_lesson (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  track_id    INT UNSIGNED NOT NULL,
  slug        VARCHAR(80)  NOT NULL,
  title       VARCHAR(200) NOT NULL,
  order_index INT NOT NULL,
  duration    INT NOT NULL DEFAULT 20 COMMENT '时长(分钟)',
  PRIMARY KEY (id),
  UNIQUE KEY uk_track_slug (track_id, slug),
  CONSTRAINT fk_lesson_track FOREIGN KEY (track_id) REFERENCES cf_track (id)
) ENGINE=InnoDB;

INSERT INTO cf_track (id, slug, title, category) VALUES
  (1, 'sql-advanced-essentials', 'SQL 进阶必备', 'database');

INSERT INTO cf_lesson (id, track_id, slug, title, order_index, duration) VALUES
  (1, 1, 'sqa-intro',       'SQL 进阶地图',      1, 25),
  (2, 1, 'sqa-ddl',         'DDL 与表结构',      2, 30),
  (3, 1, 'sqa-dml',         'DML 操作',          3, 25),
  (4, 1, 'sqa-query',       '查询与聚合',        4, 30),
  (5, 1, 'sqa-functions',   '内置函数',          5, 25),
  (6, 1, 'sqa-subqueries',  '子查询',            6, 30),
  (7, 1, 'sqa-window',      '窗口函数与 CTE',    7, 40),
  (8, 1, 'sqa-views',       '视图与存储过程',    8, 30),
  (9, 1, 'sqa-constraints', '约束与完整性',      9, 30),
  (10,1, 'sqa-transactions','事务与安全',       10, 35),
  (11,1, 'sqa-performance', '性能优化',         11, 40),
  (12,1, 'sqa-practice',    '综合练习',         12, 45);

步骤 2：创建第一个视图

CREATE OR REPLACE VIEW v_user_ac_stat AS
SELECT
  u.id                                   AS user_id,
  u.username,
  COUNT(s.id)                            AS total_sub,
  COALESCE(SUM(s.status = 'accepted'),0) AS ac_cnt,
  ROUND(COALESCE(
    SUM(s.status = 'accepted') / NULLIF(COUNT(s.id), 0) * 100
  , 0), 1)                               AS ac_rate
FROM cf_user u
LEFT JOIN cf_submission s ON s.user_id = u.id
GROUP BY u.id, u.username;

SELECT * FROM v_user_ac_stat ORDER BY ac_rate DESC;

预期：lin、zhao、zhou 各 66.7，wang 50.0，sun 0.0，qian 0.0（无提交被 NULLIF 防住，不会出现 NULL 或除零）。CREATE OR REPLACE 意味着视图定义可以原地替换，改报表不用先删后建。

步骤 3：视图的元信息与目录视图

SHOW CREATE VIEW v_user_ac_stat\G

SELECT TABLE_NAME, IS_UPDATABLE
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'sql_practice';

再建一个「课程目录」视图，体会视图封装 JOIN 的价值：

CREATE OR REPLACE VIEW v_track_overview AS
SELECT
  t.title             AS track_title,
  COUNT(l.id)         AS lesson_count,
  SUM(l.duration)     AS total_minutes
FROM cf_track t
LEFT JOIN cf_lesson l ON l.track_id = t.id
GROUP BY t.id, t.title;

SELECT * FROM v_track_overview;
-- 预期：SQL 进阶必备 / 12 / 385

步骤 4：可更新视图 vs 不可更新视图

-- 简单视图（单表、无聚合）：可以 UPDATE，改动直达基表
CREATE OR REPLACE VIEW v_active_users AS
SELECT id, username, email, level, status
FROM cf_user WHERE status = 'active';

UPDATE v_active_users SET level = 4 WHERE id = 1;   -- 成功
SELECT level FROM cf_user WHERE id = 1;             -- 基表同步变 4

-- 聚合视图：不可 DML
CREATE OR REPLACE VIEW v_ac_stat_readonly AS
SELECT user_id, COUNT(*) AS cnt
FROM cf_submission GROUP BY user_id;

UPDATE v_ac_stat_readonly SET cnt = 5 WHERE user_id = 1;
-- ERROR 1288 The target table ... is not updatable

规则：视图定义里出现聚合、GROUP BY、DISTINCT、UNION 之一，就不可更新。

步骤 5：存储过程——批量升级达标用户

DROP PROCEDURE IF EXISTS sp_promote_users;

DELIMITER //
CREATE PROCEDURE sp_promote_users(IN p_min_ac INT)
BEGIN
  IF p_min_ac IS NULL OR p_min_ac < 1 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'p_min_ac 必须大于等于 1';
  END IF;

  START TRANSACTION;
    UPDATE cf_user u
    SET u.level = LEAST(u.level + 1, 5)
    WHERE u.status = 'active'
      AND (SELECT COUNT(*) FROM cf_submission s
           WHERE s.user_id = u.id AND s.status = 'accepted') >= p_min_ac;
  COMMIT;
END //
DELIMITER ;

CALL sp_promote_users(2);
SELECT username, level FROM cf_user WHERE status = 'active';
-- 预期：lin 5（4+1）、zhao 5（封顶）、zhou 5、wang 1（仅 1 次 AC 不动）、qian 1

CALL sp_promote_users(0);
-- ERROR 1644 p_min_ac 必须大于等于 1（SIGNAL 主动抛错）

DELIMITER // 是告诉客户端「把 // 当语句结束符」，让过程中的分号不被截断——它不是 SQL 语法，是客户端指令。SIGNAL 用来把非法参数变成显式错误，比静默返回好得多。

步骤 6：条件聚合——MySQL 版行转列

SELECT
  u.username,
  COALESCE(SUM(s.status = 'accepted'), 0)                        AS ac_cnt,
  COALESCE(SUM(s.status = 'wrong_answer'), 0)                    AS wa_cnt,
  COALESCE(SUM(s.status IN ('runtime_error','time_limit')), 0)   AS err_cnt
FROM cf_user u
LEFT JOIN cf_submission s ON s.user_id = u.id
GROUP BY u.id, u.username
ORDER BY ac_cnt DESC;

预期第一行 lin：2/1/0。每个 SUM CASE 就是一列——MySQL 没有原生 PIVOT 语法，条件聚合就是标准解。

讲清楚要点

1. 视图不存数据：每次查询视图都会实时展开执行定义里的 SELECT（除非显式建物化表）。好处是永远最新，代价是每次都有成本——把重型视图当缓存用是常见误解。
2. 视图的两个真实价值：一是复用（复杂 JOIN + 聚合起个名，报表到处 SELECT），二是权限收口（GRANT SELECT ON v_user_ac_stat TO report_ro;，让报表账号只能看统计列、看不到基表敏感字段）。
3. 可更新视图的边界：单表、无聚合/DISTINCT/GROUP BY/UNION 的视图才能 UPDATE/DELETE；INSERT 还要求视图包含基表所有无默认值的 NOT NULL 列。聚合视图一律只读。
4. 视图可以用基表索引，但自己没有索引：查询视图最终落到基表上，基表索引照常生效；视图本身的过滤条件（如 WHERE status='active'）会被优化器合并进外层查询（MERGE 算法）。含聚合的视图走 TEMPTABLE 算法，会先物化临时表，嵌套多了性能敏感。
5. 存储过程的适用边界：适合数据库侧的批量任务（夜间修正、数据搬迁、定时报表落表）和多语句原子操作；日常 CRUD 和业务规则放在应用层（Spring Service），因为应用层有测试、版本管理、链路追踪，数据库侧逻辑出了问题难调试难迁移。
6. SIGNAL + HANDLER 是过程健壮性的最低配：参数不合法就 SIGNAL 抛错；用 DECLARE EXIT HANDLER FOR SQLEXCEPTION 可在异常时 ROLLBACK 并重新抛出，保证过程里的失败不留半截数据。

你可能会懵的地方

• 视图能加快查询吗？ 一般不能。视图只是命名的查询，MERGE 算法下性能与手写 SQL 相同，TEMPTABLE 算法还可能更慢。视图买的是可读性和权限，不是速度。

• 基表加了列，视图会怎样？ 定义里写死列名的视图不受影响；用 SELECT * 定义的视图在基表变更后语义漂移（新列可能混进来）。规范是视图里显式列出列名，永远不用 SELECT *。

• DELIMITER 到底是什么？ mysql 客户端的指令，告诉它「语句结束符暂时换成 //」，让过程体里的分号能安全通过。图形客户端（Navicat/DataGrip）通常自动处理，写过程时按客户端文档来。

• 视图嵌套视图可以吗？ 语法允许，但每层嵌套都可能引入 TEMPTABLE 物化，性能和可读性都劣化。规范：视图最多一层依赖，逻辑复杂时用 CTE 而不是视图套视图。

• 为什么说「业务逻辑别写进存储过程」？ 三个硬伤：无法纳入应用的单元测试与 Git 版本流；调试要靠数据库端工具；换数据库（MySQL→PG）时要全部重写。过程只做「数据库自己家的事」。

错法对照

错法	问题	正解
视图定义用 SELECT *	基表加列后视图语义漂移	显式列出列名
用视图当「查询缓存」	每次实时重算，不省任何成本	真要缓存就落表 + 定时刷新
聚合视图上做 UPDATE	ERROR 1288	直接更新基表
业务规则塞进存储过程	难测试、难版本化、难迁移	业务留应用层，过程只做批处理
过程里不校验参数	静默产出错误数据	入口 SIGNAL 校验，异常 ROLLBACK

卡点急救

1. ERROR 1356 View references invalid table(s)：视图依赖的基表被删或改名了。恢复基表，或 DROP VIEW 后重建。
2. ERROR 1288 not updatable：目标视图含聚合/DISTINCT/GROUP BY/UNION，改为操作基表。
3. ERROR 1442 Can't update table in stored function/trigger：在触发器里更新了触发它的表。改用存储过程显式编排，或重新设计触发链。

面试常问

• 视图和表的区别？ 表物理存储数据，视图只存查询定义、每次查询实时展开。视图用于复用复杂查询和权限隔离；可更新性受定义限制（含聚合即只读）。MySQL 没有物化视图，需要「存结果的视图」就建表 + 定时刷新。

• 什么场景会用存储过程？ 数据库侧批量任务（夜间对账、状态修正）、强原子性的多表写入、离线报表落表。同时要说明业务逻辑保持在应用层的理由（测试、版本、迁移），展示对边界的理解。

• 视图会导致性能问题吗？ 会：TEMPTABLE 算法的聚合视图先物化临时表，多层视图嵌套放大这个问题。排查时看 EXPLAIN 里 derived 表的成本，能下推的过滤条件尽量写在外层查询里。

自测三问

1. 为什么含 GROUP BY 的视图不可更新？MySQL 给出的错误码是多少？
2. 视图的 MERGE 与 TEMPTABLE 算法分别什么时候触发？对性能各有什么影响？
3. 一个「给达标用户加积分」的多步操作，放存储过程还是应用层？说出你的判断依据。

小结一句话

视图是「有名字的查询」（买可读性与权限、不买性能），存储过程是「存在库里的批量任务」——两者边界清楚，业务逻辑仍归应用层。

延伸

• MySQL 8.0 Reference Manual — Views
• MySQL 8.0 Reference Manual — Stored Programs
• 下一课：约束与完整性——主键、唯一、外键、CHECK，数据库替你把住数据质量的最后一道门。