MariaDB 教程 / 用 VIEW 定义可重复使用的查询

视图(VIEW)把 SELECT 定义保存成带名称的数据库对象,供后续语句查询。普通视图不会单独保存一份结果行副本;使用前应检查权限上下文、依赖对象和可更新性,不能把它当作自动缓存的表。

区分保存的查询定义与单语句 CTE

多个报表反复使用同一份已支付订单金额计算时,可以考虑定义视图。CTE 只在所属 SQL 中提供中间名称,创建后的视图则可以被后续语句访问。应按依赖它的查询设计公开列与名称,而不是只把一段很长的 SQL 随意藏起来。

对象 保存或表示的内容 生命周期
普通 VIEW SELECT 定义 直到执行 DROP VIEW
CTE 语句内的中间查询名称 所属 SQL 语句内
表 实际记录 按存储与删除规则管理

视图并不是绕过权限的通用入口。谁能创建、谁能访问、底层操作使用哪个账号的权限,都要检查。语法与权限条件见 MariaDB 官方 CREATE VIEW 文档。

准备基础表并检查视图名称

使用 course_user 连接学习服务器,先执行 SELECT VERSION(), DATABASE();、SHOW TABLES; 和 SHOW FULL TABLES;。本节使用 c34_customers、c34_products、c34_orders、c34_items 四张表,以及视图 c34_paid_order_totals。已有同名对象时,先检查定义与记录,不要覆盖用途未明的数据。

下面的完整准备代码在 MariaDB 客户端中执行,也可按 SQL 文件与 SOURCE 教程保存 UTF-8 文件并用实际路径加载。学习账号需要本节数据库内的建表、查询、创建视图等必要权限;若权限不足,应确认具体缺少的权限,而不是改用无限制账号运行未知脚本。

USE shopcourse;
CREATE TABLE c34_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c34_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c34_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c34_order_customer FOREIGN KEY (customer_id)
  REFERENCES c34_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c34_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c34_item_order FOREIGN KEY (order_id)
  REFERENCES c34_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c34_item_product FOREIGN KEY (product_id)
  REFERENCES c34_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c34_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c34_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c34_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c34_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

姓名、城市和商品名保留原始韩文字符串。商品 1~4 分别是笔记本、圆珠笔、马克杯和台灯;客户 1、3 的城市表示首尔,2 表示釜山,4 表示大田。使用编号核对结果,保持原始 UTF-8 字面量一致。

首次练习使用 CREATE VIEW,不用 CREATE OR REPLACE 覆盖未审查的已有定义。如果是在有依赖者的环境修改现有视图,应先保存 SHOW CREATE VIEW 的完整输出,并核对调用它的报表与权限。表与视图共享名称空间,因此检查名称时也不能只假定同名对象一定是视图。

创建并查询 INVOKER 视图

CREATE SQL SECURITY INVOKER VIEW c34_paid_order_totals AS
SELECT o.order_id,o.customer_id,o.ordered_at,
 SUM(i.quantity*i.unit_price) AS goods_total
FROM c34_orders o JOIN c34_items i ON i.order_id=o.order_id
WHERE o.status='paid' GROUP BY o.order_id,o.customer_id,o.ordered_at;
SELECT order_id,goods_total FROM c34_paid_order_totals ORDER BY order_id;
SHOW CREATE VIEW c34_paid_order_totals;

预期订单金额为 101→11500.00、102→8000.00、103→19500.00。查询视图时显式指定 ORDER BY,不依赖内部定义或一次执行看到的顺序。SHOW CREATE VIEW 用于核对实际保存的定义,而不只是回看本地脚本。

SQL SECURITY INVOKER 表示调用视图时,底层访问使用调用者的权限检查。因此调用者不仅需要视图上的访问权限,也需要定义所需的源对象权限。本例读取 c34_orders 和 c34_items,不能以为只给视图 SELECT 就必然能运行。

这里按订单汇总,并使用 INNER JOIN,所以只有有明细的 paid 订单进入视图。它保存的是这项查询规则,不是创建时三行结果的固定快照;以后源记录变更,查询结果可随可见数据变化。

在同一连接观察原始数据变化与回滚

START TRANSACTION;
UPDATE c34_items SET quantity=3 WHERE order_id=101 AND product_id=1;
SELECT goods_total FROM c34_paid_order_totals WHERE order_id=101;
ROLLBACK;
SELECT goods_total FROM c34_paid_order_totals WHERE order_id=101;

订单 101 的商品 1 数量从 2 改成 3,事务内查询视图时,金额应从 11500.00 变成 15000.00。ROLLBACK 后,再查同一个订单应恢复为 11500.00。请把整个实验放在同一连接,按顺序执行并核对这两个结果。

变化来自底层 c34_items 的数量,而不是某份独立的视图历史数据被更新。普通视图没有需要手工刷新的结果副本。若其他连接同时读取,它能看到什么还取决于隔离级别、事务状态与提交边界,不能把本连接看到的未提交值直接推广到所有连接。

如果回滚后结果没有恢复,先检查工具是否自动提交或更换了连接,以及实际更新的表与条件。不要为了“修好视图”重新创建定义;先确定问题发生在数据修改、连接行为还是查询规则。

检查权限上下文、可更新性与性能

这个视图使用 GROUP BY 与 SUM,不能当作任意修改底层行的入口。部分简单视图可以更新,但不代表每个视图都可以。聚合结果对应多条明细,直接把 goods_total 改成某个值,也没有自动定义应该修改哪一条数量或单价。

可更新条件见官方 通过视图插入和更新的说明。应把报告读取与基础数据修改路径分开;需要更新时使用明确的源表条件与事务流程,而不是强行通过聚合视图写回。

DEFINER 视图还涉及定义者账号与相应安全上下文。备份恢复到另一个环境时,若依赖的定义者不存在或权限不同,查询可能失败;审查视图时应同时检查这些属性,不要把故障一概当成 SELECT 语法问题。

创建视图不会自动缓存结果或保证加速。复杂计算仍可能需要扫描、连接与聚合;应根据实际执行计划和代表性数据评估性能。视图的主要作用与执行成本需要分别判断。

谨慎修改定义,并先删除视图再清理表

改名、删除列或改变输出含义,可能使依赖查询失败。选择明确列名而不是 SELECT *,审查使用者后再修改定义。如果出现对象或列不存在,先检查基础表变化、真实视图定义与权限,再决定如何恢复。

本节的 DROP VIEW 仅移除视图定义,不删除四张基础表中的记录。清理前先确认目标确实是本节可丢弃的视图,而不是别的报表正在使用的对象:

DROP VIEW shopcourse.c34_paid_order_totals;

删除后可以再次核对四张表的行数与记录,确认数据仍在。若基础表也要清理,先保存需要的内容,再执行下面的子表优先顺序。DROP TABLE 会删除定义与全部记录,普通 ROLLBACK 无法恢复;清理应与数据回滚实验分开。

DROP TABLE shopcourse.c34_items;
DROP TABLE shopcourse.c34_orders;
DROP TABLE shopcourse.c34_products;
DROP TABLE shopcourse.c34_customers;