跳到主要内容

MySQL 视图

视图（View）是一个虚拟表，其内容由查询定义。本章将详细介绍视图的创建、使用和最佳实践。

什么是视图？​

视图是一个虚拟表，它基于 SQL 查询结果构建，不存储实际数据：

┌─────────────────────────────────────────────────────────────┐
│                    视图的概念                                │
├─────────────────────────────────────────────────────────────┤
│                                                            │
│   物理表                   视图                             │
│   ┌─────────────┐         ┌─────────────┐                 │
│   │   users     │         │  v_users    │                 │
│   │─────────────│         │─────────────│                 │
│   │ id          │  ────>  │ id          │                 │
│   │ name        │  查询    │ name        │                 │
│   │ email       │         │ email       │                 │
│   │ password    │         │ status      │                 │
│   │ status      │         └─────────────┘                 │
│   └─────────────┘           (不包含密码)                    │
│                                                            │
│   特点：                                                    │
│   1. 视图不存储数据，每次查询时动态生成                      │
│   2. 视图可以简化复杂查询                                   │
│   3. 视图可以隐藏敏感数据                                   │
│   4. 视图可以提供数据独立性                                 │
│                                                            │
└─────────────────────────────────────────────────────────────┘

视图的优点​

优点说明
简化查询封装复杂 SQL，简化调用
数据安全隐藏敏感列，控制数据访问
逻辑独立表结构变化不影响应用
数据一致性统一的数据访问入口

视图的缺点​

缺点说明
性能影响每次查询都需要执行定义的 SQL
更新限制不是所有视图都支持更新
调试困难视图嵌套时难以排查问题

创建视图​

基本语法​

CREATE VIEW 视图名 AS
SELECT 语句;

创建简单视图​

-- 创建用户基本信息视图（隐藏密码等敏感信息）
CREATE VIEW v_user_info AS
SELECT 
    id,
    username,
    email,
    status,
    created_at
FROM users;

-- 使用视图
SELECT * FROM v_user_info;

-- 带条件的查询
SELECT * FROM v_user_info WHERE status = 'active';

创建带别名的视图​

CREATE VIEW v_user_summary (
    user_id,
    user_name,
    order_count,
    total_amount
) AS
SELECT 
    u.id,
    u.username,
    COUNT(o.id),
    COALESCE(SUM(o.total_amount), 0)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

SELECT * FROM v_user_summary;

创建多表关联视图​

-- 创建订单详情视图
CREATE VIEW v_order_detail AS
SELECT 
    o.id AS order_id,
    o.order_no,
    u.username,
    u.email,
    o.product_name,
    o.quantity,
    o.price,
    o.total_amount,
    o.status,
    o.created_at
FROM orders o
INNER JOIN users u ON o.user_id = u.id;

-- 使用视图查询
SELECT * FROM v_order_detail WHERE status = 'completed';

创建计算视图​

CREATE VIEW v_user_statistics AS
SELECT 
    u.id,
    u.username,
    COUNT(o.id) AS order_count,
    SUM(o.total_amount) AS total_spent,
    AVG(o.total_amount) AS avg_order_amount,
    MAX(o.created_at) AS last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

SELECT * FROM v_user_statistics WHERE order_count > 5;

创建条件视图​

-- VIP 用户视图
CREATE VIEW v_vip_users AS
SELECT 
    id,
    username,
    email,
    salary
FROM users
WHERE salary > 15000
WITH CHECK OPTION;  -- 确保插入/更新符合条件

-- 活跃订单视图
CREATE VIEW v_active_orders AS
SELECT * FROM orders
WHERE status IN ('pending', 'paid', 'shipped');

OR REPLACE 选项​

-- 如果视图存在则替换
CREATE OR REPLACE VIEW v_user_info AS
SELECT 
    id,
    username,
    email,
    age,
    status,
    created_at
FROM users;

查看视图​

查看视图定义​

-- 查看视图结构
DESC v_user_info;

-- 查看视图定义
SHOW CREATE VIEW v_user_info;

-- 从 information_schema 查看
SELECT 
    TABLE_NAME,
    VIEW_DEFINITION,
    CHECK_OPTION,
    IS_UPDATABLE
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = 'your_database';

查看所有视图​

-- 查看数据库中的所有视图
SELECT 
    TABLE_NAME,
    TABLE_TYPE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database'
  AND TABLE_TYPE = 'VIEW';

-- 使用 SHOW 命令
SHOW FULL TABLES WHERE Table_type = 'VIEW';

修改视图​

使用 ALTER VIEW​

ALTER VIEW v_user_info AS
SELECT 
    id,
    username,
    email,
    phone,
    status,
    created_at,
    updated_at
FROM users;

使用 CREATE OR REPLACE​

CREATE OR REPLACE VIEW v_user_info AS
SELECT 
    id,
    username,
    email,
    phone,
    status,
    salary,
    created_at
FROM users;

删除视图​

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

-- 删除多个视图
DROP VIEW IF EXISTS v_user_info, v_order_detail;

可更新视图​

可更新视图的条件​

视图必须满足以下条件才能进行 INSERT、UPDATE、DELETE 操作：

  1. 没有聚合函数（SUM、COUNT、AVG 等）
  2. 没有 DISTINCT、GROUP BY、HAVING
  3. 没有 UNION、UNION ALL
  4. 没有子查询（FROM 子句中）
  5. 没有常量列
  6. 视图中的列都来自一个表

更新视图示例​

-- 创建可更新视图
CREATE VIEW v_active_users AS
SELECT id, username, email, status
FROM users
WHERE status = 'active';

-- 通过视图更新数据
UPDATE v_active_users 
SET email = '[email protected]' 
WHERE id = 1;

-- 通过视图插入数据
INSERT INTO v_active_users (id, username, email, status)
VALUES (100, '新用户', '[email protected]', 'active');

-- 通过视图删除数据
DELETE FROM v_active_users WHERE id = 100;

WITH CHECK OPTION​

确保通过视图修改的数据仍然满足视图条件：

-- 创建带检查选项的视图
CREATE VIEW v_high_salary_users AS
SELECT id, username, email, salary
FROM users
WHERE salary > 10000
WITH CHECK OPTION;

-- 更新成功（满足条件）
UPDATE v_high_salary_users 
SET salary = 15000 
WHERE id = 1;

-- 更新失败（不满足条件）
UPDATE v_high_salary_users 
SET salary = 5000 
WHERE id = 1;
-- ERROR: CHECK OPTION failed

-- 插入失败（不满足条件）
INSERT INTO v_high_salary_users (id, username, email, salary)
VALUES (101, 'test', '[email protected]', 5000);
-- ERROR: CHECK OPTION failed

检查选项类型​

-- CASCADED：检查所有底层视图条件（默认）
CREATE VIEW v_view1 AS
SELECT * FROM users WHERE salary > 5000
WITH CHECK OPTION;

CREATE VIEW v_view2 AS
SELECT * FROM v_view1 WHERE salary < 20000
WITH CHECK OPTION;  -- 同时检查两个条件

-- LOCAL：只检查当前视图条件
CREATE VIEW v_view3 AS
SELECT * FROM v_view1 WHERE salary < 20000
WITH LOCAL CHECK OPTION;  -- 只检查当前条件

视图应用场景​

1. 简化复杂查询​

-- 复杂的多表查询
CREATE VIEW v_product_sales AS
SELECT 
    p.id AS product_id,
    p.name AS product_name,
    c.name AS category_name,
    COUNT(o.id) AS sales_count,
    SUM(o.quantity) AS total_quantity,
    SUM(o.total_amount) AS total_revenue
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
LEFT JOIN order_items oi ON p.id = oi.product_id
LEFT JOIN orders o ON oi.order_id = o.id AND o.status = 'completed'
GROUP BY p.id, p.name, c.name;

-- 简单查询
SELECT * FROM v_product_sales 
WHERE total_revenue > 10000 
ORDER BY total_revenue DESC;

2. 数据安全​

-- 只暴露部分数据给特定用户
CREATE VIEW v_public_users AS
SELECT 
    id,
    username,
    -- 不包含 email 和手机号
    status,
    created_at
FROM users;

-- 给只读用户授权视图访问
GRANT SELECT ON mydb.v_public_users TO 'readonly'@'%';
-- 不授权原始表访问

3. 数据抽象​

-- 隐藏表结构变化
-- 假设原来 users 表拆分为 users_profile 和 users_auth

CREATE VIEW v_users AS
SELECT 
    p.id,
    p.username,
    p.email,
    p.phone,
    a.status,
    a.last_login,
    p.created_at
FROM users_profile p
INNER JOIN users_auth a ON p.id = a.user_id;

-- 应用程序无需修改，继续使用 v_users

4. 报表视图​

-- 日报表视图
CREATE VIEW v_daily_report AS
SELECT 
    DATE(created_at) AS report_date,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_sales,
    COUNT(DISTINCT user_id) AS unique_customers
FROM orders
WHERE status = 'completed'
GROUP BY DATE(created_at);

SELECT * FROM v_daily_report 
WHERE report_date >= '2024-01-01'
ORDER BY report_date DESC;

5. 分区数据视图​

-- 假设有按月分表
-- orders_202401, orders_202402, ...

CREATE VIEW v_all_orders AS
SELECT * FROM orders_202401
UNION ALL
SELECT * FROM orders_202402
UNION ALL
SELECT * FROM orders_202403;

SELECT * FROM v_all_orders WHERE user_id = 100;

视图性能优化​

视图执行原理​

查询流程：
SELECT * FROM v_user_info WHERE id = 1;

1. 解析视图定义
   └─> SELECT id, username, email FROM users

2. 合并查询条件
   └─> SELECT id, username, email FROM users WHERE id = 1

3. 执行优化后的查询

性能优化建议​

-- 1. 在基础表上建立合适的索引
CREATE INDEX idx_users_status ON users(status);

CREATE VIEW v_active_users AS
SELECT id, username, email FROM users WHERE status = 'active';

-- 2. 避免视图嵌套过深
-- 不好
CREATE VIEW v_view1 AS SELECT ...;
CREATE VIEW v_view2 AS SELECT * FROM v_view1;
CREATE VIEW v_view3 AS SELECT * FROM v_view2;  -- 嵌套太深

-- 好：直接定义
CREATE VIEW v_final AS SELECT ...;

-- 3. 使用 EXPLAIN 分析视图查询
EXPLAIN SELECT * FROM v_user_info WHERE id = 1;

-- 4. 对于复杂视图，考虑物化视图（MySQL 不直接支持，可用定时任务更新实体表）
CREATE TABLE mv_user_statistics AS
SELECT ...;

-- 定时更新
-- 使用事件或定时任务

视图与临时表​

特性视图临时表
存储不存储数据存储数据
更新自动反映源表变化需手动更新
性能每次查询执行查询快速
生命周期永久会话结束删除
索引依赖源表索引可独立建索引

小结​

本章我们学习了：

  1. 视图概念：虚拟表，不存储实际数据
  2. 创建视图：CREATE VIEW、OR REPLACE
  3. 查看视图：DESC、SHOW CREATE VIEW
  4. 修改删除：ALTER VIEW、DROP VIEW
  5. 可更新视图：条件和 WITH CHECK OPTION
  6. 应用场景：简化查询、数据安全、数据抽象
  7. 性能优化：索引、避免嵌套、EXPLAIN 分析

练习​

  1. 创建一个视图，展示用户及其订单统计信息
  2. 创建一个带 WITH CHECK OPTION 的视图，测试更新限制
  3. 创建一个报表视图，统计每日销售数据
  4. 分析一个复杂视图的执行计划

参考资源​