第8章视图、存储过程与触发器

8.1 视图（VIEW）

视图（View）是一张虚拟表，本身不存储数据，而是存储一条 SELECT 查询语句。每次查询视图时，数据库执行背后的 SELECT 语句，动态生成结果。

视图的优点

简化查询：把复杂的多表 JOIN 查询封装成视图，调用者只需 SELECT * FROM view_name
安全隔离：只暴露视图列，隐藏敏感字段（如 password_hash），用于权限控制
逻辑抽象：底层表结构变化时，只需修改视图定义，调用方无需改动

SQL-- 创建视图：封装"已发布文章+作者信息"的复杂查询
CREATE VIEW v_published_posts AS
SELECT
  p.id,
  p.title,
  p.slug,
  u.username    AS author,
  u.email       AS author_email,
  p.view_count,
  p.published_at
FROM posts p
INNER JOIN users u ON p.user_id = u.id
WHERE p.status = 'published';

-- 使用视图（像普通表一样查询）
SELECT * FROM v_published_posts
WHERE author = 'alice'
ORDER BY published_at DESC
LIMIT 10;

-- 创建安全视图（隐藏密码等敏感字段）
CREATE VIEW v_safe_users AS
SELECT id, username, email, role, created_at
FROM users;  -- 不包含 password_hash 列

-- 修改视图（重新定义）
CREATE OR REPLACE VIEW v_published_posts AS
SELECT p.id, p.title, u.username AS author -- 修改后的定义
FROM posts p JOIN users u ON p.user_id = u.id
WHERE p.status = 'published';

-- 删除视图
DROP VIEW IF EXISTS v_published_posts;

可更新视图

满足特定条件的视图可以执行 INSERT/UPDATE/DELETE（操作会转换为对底层表的操作）。但若视图包含 JOIN、GROUP BY、DISTINCT、聚合函数、子查询等，则通常不可更新。

8.2 物化视图（Materialized View）

物化视图（Materialized View）与普通视图不同，它实际存储查询结果。查询物化视图就像查询普通表，速度非常快；但数据不会自动更新，需要手动或定时刷新。

ℹ️

MySQL 不原生支持物化视图，可用定时任务将查询结果写入普通表来模拟。PostgreSQL 原生支持。

SQL-- PostgreSQL：创建物化视图
CREATE MATERIALIZED VIEW mv_user_stats AS
SELECT
  user_id,
  COUNT(*) AS post_count,
  SUM(view_count) AS total_views
FROM posts
GROUP BY user_id
WITH DATA;  -- WITH DATA 立即填充数据；WITH NO DATA 只创建结构

-- 刷新物化视图（用最新数据替换存储内容）
REFRESH MATERIALIZED VIEW mv_user_stats;

-- 并发刷新（不阻塞查询，需要先在物化视图上建唯一索引）
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_user_stats;

-- 在物化视图上建索引（与普通表相同）
CREATE INDEX idx_mv_user_stats_user_id ON mv_user_stats(user_id);

8.3 存储过程（Stored Procedure）

存储过程是存储在数据库中的一段命名的程序代码，包含 SQL 语句和控制流（IF/LOOP 等），可接受参数并执行复杂逻辑。

✅

存储过程的优点 减少网络往返（复杂逻辑在数据库内执行）；可重用；可以控制权限（只授权调用过程）。

⚠️

存储过程的缺点 调试困难；版本控制不方便；业务逻辑分散（部分在数据库，部分在应用）；迁移数据库时需重写。

MySQL 存储过程语法

SQL-- 修改分隔符（避免 ; 与过程内部语句冲突）
DELIMITER $$

CREATE PROCEDURE sp_transfer_money(
  IN  from_user_id  INT,
  IN  to_user_id    INT,
  IN  amount        DECIMAL(10,2),
  OUT success       BOOLEAN
)
BEGIN
  DECLARE current_balance DECIMAL(10,2);

  -- 开始事务
  START TRANSACTION;

  -- 检查余额
  SELECT balance INTO current_balance
  FROM accounts
  WHERE user_id = from_user_id
  FOR UPDATE;

  IF current_balance >= amount THEN
    UPDATE accounts SET balance = balance - amount WHERE user_id = from_user_id;
    UPDATE accounts SET balance = balance + amount WHERE user_id = to_user_id;
    COMMIT;
    SET success = TRUE;
  ELSE
    ROLLBACK;
    SET success = FALSE;
  END IF;
END $$

DELIMITER ;

-- 调用存储过程
CALL sp_transfer_money(1, 2, 100.00, @result);
SELECT @result;

8.4 函数（Function）

函数与存储过程类似，但函数必须有返回值，且通常不执行事务控制，可以在 SELECT 语句中直接调用。

特性	存储过程	函数
返回值	通过 OUT 参数输出，可无返回	必须有 RETURN 返回值
调用方式	CALL procedure_name()	可在 SELECT/WHERE 中使用
事务控制	可以（COMMIT/ROLLBACK）	不能（MySQL）
适用场景	复杂业务流程、数据迁移	计算逻辑复用、SELECT 中用

SQLDELIMITER $$

-- MySQL 自定义函数：计算用户等级
CREATE FUNCTION fn_user_level(total_spent DECIMAL(10,2))
RETURNS VARCHAR(20)
DETERMINISTIC   -- 相同输入总是返回相同结果
BEGIN
  IF total_spent >= 10000 THEN
    RETURN '钻石会员';
  ELSEIF total_spent >= 5000 THEN
    RETURN '黄金会员';
  ELSEIF total_spent >= 1000 THEN
    RETURN '白银会员';
  ELSE
    RETURN '普通会员';
  END IF;
END $$

DELIMITER ;

-- 在 SELECT 中使用
SELECT
  user_id,
  SUM(total_price) AS total_spent,
  fn_user_level(SUM(total_price)) AS level
FROM orders
WHERE status != 'cancelled'
GROUP BY user_id;

8.5 触发器（Trigger）

触发器是绑定在表上的自动执行程序，当指定的 DML 事件（INSERT/UPDATE/DELETE）发生时自动触发，无需手动调用。

BEFORE 触发器在操作执行前触发。可以修改 NEW 的值（改变即将写入的数据），或通过 SIGNAL 抛出错误阻止操作。适用于数据验证、自动计算。
AFTER 触发器在操作成功执行后触发。无法阻止原操作，适用于记录审计日志、更新冗余数据、同步统计表。
NEW 伪记录 INSERT 和 UPDATE 触发器中，NEW 代表即将写入/已写入的新行。BEFORE 触发器中可修改 NEW.列名来改变写入值。
OLD 伪记录 UPDATE 和 DELETE 触发器中，OLD 代表被修改/删除的原始行数据。

实战 1：自动记录审计日志

SQL-- 创建审计日志表
CREATE TABLE audit_log (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  table_name VARCHAR(50),
  action     ENUM('INSERT', 'UPDATE', 'DELETE'),
  old_data   JSON,
  new_data   JSON,
  changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER $$

-- users 表 UPDATE 后记录审计日志
CREATE TRIGGER trig_users_after_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
  INSERT INTO audit_log (table_name, action, old_data, new_data)
  VALUES (
    'users',
    'UPDATE',
    JSON_OBJECT('email', OLD.email, 'role', OLD.role),
    JSON_OBJECT('email', NEW.email, 'role', NEW.role)
  );
END $$

DELIMITER ;

实战 2：BEFORE 触发器自动计算字段

SQLDELIMITER $$

-- 插入订单时自动计算 total_price = quantity × unit_price
CREATE TRIGGER trig_orders_before_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  SET NEW.total_price = NEW.quantity * NEW.unit_price;
END $$

DELIMITER ;

-- 现在插入时无需传 total_price，触发器自动计算
INSERT INTO orders (user_id, product_name, quantity, unit_price)
VALUES (1, '键盘', 2, 299.00);
-- total_price 自动设为 598.00

查看和删除触发器

SQL-- 查看数据库中所有触发器
SHOW TRIGGERS FROM blog_db;
SHOW TRIGGERS LIKE 'users';  -- 只看 users 表的触发器

-- 删除触发器
DROP TRIGGER IF EXISTS trig_users_after_update;

8.6 游标（Cursor）

游标（Cursor）允许在存储过程中逐行处理查询结果（类似程序中的 for 循环遍历结果集）。

⚠️

游标性能差，通常可以用 SET 或批量 SQL 替代 游标逐行处理，在大数据量时性能极差。绝大多数"逐行操作"的需求可以改写为一条 UPDATE/INSERT...SELECT 语句，性能提升数十倍。游标主要用于真正需要"行级复杂逻辑"且无法用集合操作表达的场景。

SQLDELIMITER $$

CREATE PROCEDURE sp_process_users()
BEGIN
  DECLARE done BOOLEAN DEFAULT FALSE;
  DECLARE v_id INT;
  DECLARE v_email VARCHAR(255);

  -- 定义游标
  DECLARE cur_users CURSOR FOR
    SELECT id, email FROM users;

  -- 定义 NOT FOUND 处理器（遍历结束时触发）
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur_users;

  read_loop: LOOP
    FETCH cur_users INTO v_id, v_email;
    IF done THEN
      LEAVE read_loop;
    END IF;
    -- 对每行做业务处理...
  END LOOP;

  CLOSE cur_users;
END $$

DELIMITER ;

← 上一章事务与并发控制下一章 →数据库设计：范式与 ER 图