数据库视图操作

视图概述

什么是视图

MySQL视图（View）是一个虚拟表，其内容由SELECT查询语句定义。视图与真实的表数据一致，但视图并不在数据库中以存储的数据值形式存在。

视图引用自定义查询表的字段，并且在引用视图时动态生成。对其所引用的基础表来说，MySQL视图的作用类似于筛选器。

视图的核心特性

1. 数据来源灵活

  • 定义视图的筛选可以来自当前或其它数据库的一个或多个表
  • 可以基于其它视图创建新视图（视图嵌套）
  • 支持复杂的多表关联查询

2. 查询限制少

  • 视图查询没有任何限制
  • 通过视图进行数据修改时的限制也很少（取决于视图类型）

3. 存储方式

  • 视图是存储在数据库中的SQL查询语句
  • 视图定义存储在数据字典中
  • 不存储实际数据，每次查询时动态计算

视图的主要应用场景

1. 安全原因

视图可以隐藏一些敏感的信息，提供数据访问的安全层：

1
2
3
4
5
-- 示例：隐藏敏感字段
CREATE VIEW user_public AS 
SELECT id, username, email, created_at 
FROM users;
-- 隐藏了password、phone等敏感字段

2. 简化查询

使复杂的查询易于理解和使用：

1
2
3
4
5
6
7
8
9
10
11
-- 复杂查询封装为视图
CREATE VIEW sales_summary AS
SELECT 
    p.name,
    p.price,
    s.quantity,
    p.price * s.quantity AS total,
    s.sale_date
FROM products p
JOIN sales s ON p.id = s.product_id
WHERE s.sale_date >= DATE_SUB(NOW(), INTERVAL 30 DAY);

3. 数据抽象

为应用程序提供统一的数据接口，屏蔽底层表结构变化：

1
2
3
4
5
6
7
8
9
-- 即使底层表结构改变，视图接口保持不变
CREATE VIEW customer_info AS
SELECT 
    c.id,
    c.name,
    a.address,
    a.city
FROM customers c
LEFT JOIN addresses a ON c.id = a.customer_id;

1. 创建视图

1.1 创建视图的基本语法

1
CREATE VIEW 视图名 AS SELECT 语句;

语法说明：

  • CREATE VIEW：创建视图的关键字
  • 视图名：视图的名称，遵循标识符命名规则
  • AS：关键字，连接视图名和查询语句
  • SELECT 语句：定义视图内容的查询语句

1.2 创建单表视图

单表视图是最简单的视图类型，基于单个表创建。

示例：创建用户权限视图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 切换到目标数据库
mysql> USE bgx;
Database changed

-- 创建视图，隐藏敏感信息
mysql> CREATE VIEW u_grant AS 
    -> SELECT user, host, authentication_string 
    -> FROM mysql.user;
Query OK, 0 rows affected (0.01 sec)

-- 查询视图数据
mysql> SELECT * FROM u_grant;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *4927A5B79C852EA2CE585A7679C5C26DF683F18C |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

应用场景：

  • 简化常用查询
  • 隐藏不需要的字段
  • 提供数据访问接口

1.3 创建多表视图

多表视图基于多个表的关联查询创建，是视图的高级应用。

环境准备

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
-- 创建数据库
mysql> CREATE DATABASE shop;
Query OK, 1 row affected (0.00 sec)

mysql> USE shop;
Database changed

-- 创建产品表
mysql> CREATE TABLE product(
    -> id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
    -> name VARCHAR(60) NOT NULL,
    -> price DOUBLE NOT NULL
    -> );
Query OK, 0 rows affected (0.01 sec)

-- 插入产品数据
mysql> INSERT INTO product(name, price) VALUES
    -> ('apple', 5),
    -> ('balane', 6),
    -> ('pear', 7);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

-- 创建销售表
mysql> CREATE TABLE purchase(
    -> id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
    -> name VARCHAR(60) NOT NULL,
    -> quantity INT NOT NULL DEFAULT 0,
    -> gen_time DATETIME NOT NULL
    -> );
Query OK, 0 rows affected (0.00 sec)

-- 插入销售数据
mysql> INSERT INTO purchase(name, quantity, gen_time) VALUES
    -> ('apple', 7, NOW()),
    -> ('pear', 10, NOW());
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

基础查询验证

在创建视图之前，先验证查询语句的正确性：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 查询产品卖出金额
mysql> SELECT 
    ->     product.name,
    ->     product.price,
    ->     purchase.quantity,
    ->     product.price * purchase.quantity AS total_value
    -> FROM product, purchase 
    -> WHERE product.name = purchase.name;
+-------+-------+----------+-------------+
| name  | price | quantity | total_value |
+-------+-------+----------+-------------+
| apple |     5 |        7 |          35 |
| pear  |     7 |       10 |          70 |
+-------+-------+----------+-------------+
2 rows in set (0.00 sec)

创建多表视图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 创建产品销售汇总视图
mysql> CREATE VIEW totol_product AS 
    -> SELECT 
    ->     product.name,
    ->     product.price,
    ->     purchase.quantity,
    ->     product.price * purchase.quantity AS Total 
    -> FROM purchase, product  
    -> WHERE purchase.name = product.name;
Query OK, 0 rows affected (0.00 sec)

-- 查询视图
mysql> SELECT * FROM totol_product;
+-------+-------+----------+-------+
| name  | price | quantity | Total |
+-------+-------+----------+-------+
| apple |     5 |        7 |    35 |
| pear  |     7 |       10 |    70 |
+-------+-------+----------+-------+
2 rows in set (0.00 sec)

视图的动态特性演示

视图的一个重要特性是动态性，当基础表数据变化时，视图查询结果会自动更新：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 新增销售记录
mysql> INSERT INTO purchase(name, quantity, gen_time) VALUES
    -> ('balane', 20, NOW());
Query OK, 1 row affected (0.00 sec)

-- 再次查询视图，自动包含新数据
mysql> SELECT * FROM totol_product;
+--------+-------+----------+-------+
| name   | price | quantity | Total |
+--------+-------+----------+-------+
| apple  |     5 |        7 |    35 |
| balane |     6 |       20 |   120 |
| pear   |     7 |       10 |    70 |
+--------+-------+----------+-------+
3 rows in set (0.00 sec)

关键点：

  • 视图不存储数据，每次查询都是实时计算
  • 基础表数据变化，视图结果自动反映变化
  • 视图提供了数据的实时视图

1.4 视图创建最佳实践

1. 使用明确的列名

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 推荐：明确指定列名
CREATE VIEW sales_report AS
SELECT 
    p.product_name AS product,
    p.unit_price AS price,
    s.quantity,
    s.sale_date AS date
FROM products p
JOIN sales s ON p.id = s.product_id;

-- 不推荐：使用SELECT *
CREATE VIEW sales_report AS
SELECT * FROM products p JOIN sales s ON p.id = s.product_id;

2. 添加注释说明

1
2
3
4
5
6
7
8
9
10
11
-- 创建带注释的视图
CREATE VIEW customer_summary AS
SELECT 
    c.id,
    c.name,
    COUNT(o.id) AS order_count,
    SUM(o.total) AS total_amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name
COMMENT '客户订单汇总视图';

3. 使用WITH CHECK OPTION（可更新视图）

1
2
3
4
5
6
-- 创建带检查选项的视图
CREATE VIEW active_users AS
SELECT * FROM users WHERE status = 'active'
WITH CHECK OPTION;

-- 这样可以防止通过视图插入不符合条件的数据

2. 查看视图

2.1 查看视图列表

方法一：使用SHOW TABLES

1
2
3
4
5
6
7
8
9
10
11
12
-- 查看当前数据库的所有表和视图
mysql> USE bgx;
Database changed

mysql> SHOW TABLES;
+----------------+
| Tables_in_bgx  |
+----------------+
| u_grant        |  -- 这是视图
| users          |  -- 这是表
+----------------+
2 rows in set (0.00 sec)

注意：SHOW TABLES 会同时显示表和视图，无法直接区分。

方法二：查询information_schema

1
2
3
4
5
-- 查看所有视图
SELECT TABLE_NAME, TABLE_TYPE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'bgx' 
  AND TABLE_TYPE = 'VIEW';

2.2 查看视图详细信息

使用SHOW TABLE STATUS

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 查看视图的详细信息
mysql> SHOW TABLE STATUS FROM bgx WHERE Name = 'u_grant'\G
*************************** 1. row ***************************
           Name: u_grant
         Engine: NULL          -- 视图没有存储引擎
        Version: 10
     Row_format: NULL
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: NULL
       Checksum: NULL
 Create_options: NULL
        Comment: VIEW          -- 标识为视图

关键信息：

  • Engine: NULL：视图没有存储引擎
  • Comment: VIEW：标识这是一个视图
  • 其他存储相关字段为NULL

2.3 查看视图定义信息

使用SHOW CREATE VIEW

1
2
3
4
5
6
7
8
-- 查看视图的创建语句
mysql> SHOW CREATE VIEW bgx.u_grant\G
*************************** 1. row ***************************
                View: u_grant
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `u_grant` AS select `mysql`.`user`.`user` AS `user`,`mysql`.`user`.`host` AS `host`,`mysql`.`user`.`authentication_string` AS `authentication_string` from `mysql`.`user`
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
1 row in set (0.00 sec)

返回信息说明：

  • View：视图名称
  • Create View：完整的视图创建语句
  • character_set_client：客户端字符集
  • collation_connection：连接排序规则

ALGORITHM参数说明：

  • UNDEFINED：MySQL自动选择算法（默认）
  • MERGE：将视图定义合并到查询中
  • TEMPTABLE：创建临时表存储结果

SQL SECURITY参数说明：

  • DEFINER：以视图定义者的权限执行（默认）
  • INVOKER：以调用者的权限执行

2.4 查看视图表结构

使用DESC或DESCRIBE

1
2
3
4
5
6
7
8
9
10
-- 查看视图结构
mysql> DESC bgx.u_grant;
+-----------------------+------------------+------+-----+---------+-------+
| Field                 | Type             | Null | Key | Default | Extra |
+-----------------------+------------------+------+-----+---------+-------+
| user                  | char(32)          | YES  |     | NULL    |       |
| host                  | char(255)         | YES  |     | NULL    |       |
| authentication_string | text              | YES  |     | NULL    |       |
+-----------------------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

使用SHOW COLUMNS

1
2
3
4
5
6
7
8
9
10
-- 另一种查看视图结构的方式
mysql> SHOW COLUMNS FROM bgx.u_grant;
+-----------------------+------------------+------+-----+---------+-------+
| Field                 | Type             | Null | Key | Default | Extra |
+-----------------------+------------------+------+-----+---------+-------+
| user                  | char(32)          | YES  |     | NULL    |       |
| host                  | char(255)         | YES  |       | NULL    |       |
| authentication_string | text              | YES  |     | NULL    |       |
+-----------------------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

2.5 查看视图依赖关系

1
2
3
4
5
6
-- 查看视图依赖的表
SELECT 
    TABLE_NAME AS view_name,
    VIEW_DEFINITION
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = 'bgx';

3. 修改视图

3.1 删除后重新创建

这是最直接的方法，先删除旧视图，然后创建新视图。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 删除旧视图
mysql> DROP VIEW bgx.u_grant;
Query OK, 0 rows affected (0.00 sec)

-- 创建新视图（修改字段）
mysql> CREATE VIEW bgx.u_grant AS
    -> SELECT user, host FROM mysql.user;
Query OK, 0 rows affected (0.00 sec)

-- 验证修改结果
mysql> SELECT * FROM bgx.u_grant;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
3 rows in set (0.01 sec)

适用场景：

  • 需要大幅修改视图定义
  • 视图结构完全改变
  • 开发测试环境

注意事项：

  • 删除视图会导致依赖该视图的对象失效
  • 需要重新授权相关权限

3.2 使用ALTER VIEW修改

使用ALTER VIEW可以在不删除视图的情况下修改视图定义，这是推荐的方式。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 使用ALTER VIEW修改视图
mysql> ALTER VIEW bgx.u_grant AS
    -> SELECT user, host, authentication_string FROM mysql.user;
Query OK, 0 rows affected (0.00 sec)

-- 验证修改结果
mysql> SELECT * FROM bgx.u_grant;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *4927A5B79C852EA2CE585A7679C5C26DF683F18C |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

ALTER VIEW语法：

1
ALTER VIEW 视图名 AS SELECT 语句;

ALTER VIEW的优势：

  • 保持视图的连续性，不中断依赖关系
  • 保留视图的权限设置
  • 原子性操作，要么全部成功，要么全部失败
  • 不影响使用该视图的应用程序

3.3 修改视图的完整语法

1
2
3
4
5
6
7
ALTER 
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW 视图名 [(列名列表)]
    AS SELECT 语句
    [WITH [CASCADED | LOCAL] CHECK OPTION];

参数说明：

  • ALGORITHM：视图算法
  • DEFINER：视图定义者
  • SQL SECURITY：安全模式
  • WITH CHECK OPTION：检查选项（用于可更新视图）

3.4 视图修改最佳实践

1. 先备份视图定义

1
2
3
4
5
6
7
8
-- 查看并保存当前视图定义
SHOW CREATE VIEW bgx.u_grant\G

-- 或者导出到文件
SELECT VIEW_DEFINITION 
INTO OUTFILE '/tmp/u_grant_backup.sql'
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = 'bgx' AND TABLE_NAME = 'u_grant';

2. 在测试环境验证

1
2
3
4
5
6
-- 在测试环境先验证修改
CREATE DATABASE test_db;
USE test_db;
-- 复制相关表结构
-- 创建测试视图
-- 验证修改后的视图

3. 使用事务（如果支持）

1
2
3
4
5
-- 某些情况下可以使用事务
START TRANSACTION;
ALTER VIEW ...;
-- 验证结果
COMMIT;  -- 或 ROLLBACK;

4. 删除视图

4.1 删除视图的基本语法

1
DROP VIEW [IF EXISTS] view_name [, view_name] ...;

语法说明：

  • DROP VIEW：删除视图的关键字
  • IF EXISTS：可选，如果视图不存在也不报错
  • view_name：要删除的视图名称
  • 可以一次删除多个视图

4.2 删除单个视图

1
2
3
4
5
6
-- 删除视图
mysql> USE bgx;
Database changed

mysql> DROP VIEW u_grant;
Query OK, 0 rows affected (0.00 sec)

4.3 删除多个视图

1
2
-- 一次删除多个视图
DROP VIEW IF EXISTS view1, view2, view3;

4.4 使用IF EXISTS避免错误

1
2
3
4
5
6
7
-- 如果视图不存在会报错
mysql> DROP VIEW non_exist_view;
ERROR 1051 (42S02): Unknown table 'bgx.non_exist_view'

-- 使用IF EXISTS避免错误
mysql> DROP VIEW IF EXISTS non_exist_view;
Query OK, 0 rows affected, 1 warning (0.00 sec)

4.5 删除视图的注意事项

1. 检查视图依赖

在删除视图前，检查是否有其他对象依赖该视图：

1
2
3
4
5
6
7
-- 查看依赖该视图的对象
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    VIEW_DEFINITION
FROM information_schema.VIEWS
WHERE VIEW_DEFINITION LIKE '%u_grant%';

2. 检查应用程序依赖

  • 确认没有应用程序直接使用该视图
  • 检查存储过程、函数中是否引用了该视图
  • 检查其他视图是否基于该视图创建

3. 备份视图定义

1
2
3
4
5
6
7
-- 删除前备份视图定义
SHOW CREATE VIEW bgx.u_grant\G

-- 或者导出
SELECT VIEW_DEFINITION 
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = 'bgx' AND TABLE_NAME = 'u_grant';

4. 权限要求

1
2
-- 需要DROP权限
GRANT DROP ON database_name.* TO 'user'@'host';

5. 视图的高级应用

5.1 可更新视图

某些视图支持INSERT、UPDATE、DELETE操作：

1
2
3
4
5
6
7
8
9
-- 创建可更新视图
CREATE VIEW active_products AS
SELECT * FROM products WHERE status = 'active'
WITH CHECK OPTION;

-- 通过视图更新数据
UPDATE active_products 
SET price = 99.99 
WHERE id = 1;

可更新视图的限制：

  • 不能包含聚合函数
  • 不能包含DISTINCT、GROUP BY、HAVING
  • 不能包含子查询
  • 必须包含所有NOT NULL列

5.2 视图性能优化

1. 使用MERGE算法

1
2
3
-- 使用MERGE算法提高性能
CREATE ALGORITHM=MERGE VIEW sales_view AS
SELECT * FROM sales WHERE status = 'active';

2. 避免复杂计算

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 不推荐：在视图中进行复杂计算
CREATE VIEW complex_view AS
SELECT 
    *,
    (SELECT COUNT(*) FROM orders WHERE customer_id = c.id) AS order_count
FROM customers c;

-- 推荐：使用JOIN
CREATE VIEW customer_orders AS
SELECT 
    c.*,
    COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id;

5.3 视图安全最佳实践

1. 使用SQL SECURITY INVOKER

1
2
3
-- 以调用者权限执行，提高安全性
CREATE SQL SECURITY INVOKER VIEW user_data AS
SELECT id, name, email FROM users;

2. 隐藏敏感信息

1
2
3
4
5
6
7
8
-- 隐藏敏感字段
CREATE VIEW public_user_info AS
SELECT 
    id,
    username,
    created_at
FROM users;
-- 隐藏了password、email、phone等敏感信息

3. 行级安全控制

1
2
3
4
-- 基于用户权限的视图
CREATE VIEW my_orders AS
SELECT * FROM orders 
WHERE user_id = CURRENT_USER();

6. 视图与表的区别

特性 表（Table） 视图（View）
数据存储 物理存储数据 不存储数据，只存储定义
索引 可以创建索引 不能直接创建索引
触发器 支持触发器 不支持触发器
修改限制 可以直接修改 修改有限制（可更新视图）
性能 查询性能稳定 性能取决于基础查询复杂度
存储空间 占用存储空间 不占用数据存储空间

7. 总结

视图的优势

  1. 简化复杂查询：将复杂的多表查询封装为简单的视图
  2. 数据安全：隐藏敏感信息，控制数据访问
  3. 逻辑独立性：应用程序与底层表结构解耦
  4. 数据一致性：提供统一的数据视图
  5. 灵活性：可以根据不同需求创建不同的视图

视图的注意事项

  1. 性能考虑：复杂视图可能影响查询性能
  2. 维护成本：视图定义需要与基础表保持同步
  3. 依赖管理：删除基础表会影响相关视图
  4. 权限管理：需要合理设置视图的访问权限

架构师建议

  1. 合理使用视图：不要过度使用，避免视图嵌套过深
  2. 性能监控：定期检查视图查询性能
  3. 文档维护：记录视图的用途和依赖关系
  4. 版本控制：将视图定义纳入版本控制系统
  5. 测试验证：修改视图前充分测试

相关文章：