mysql视图和存储过程

Mysql视图和存储过程

---

前言

MySQL作为一个强大的关系型数据库管理系统，提供了许多高级功能，其中包括视图、存储过程和函数。这些功能使得数据库的管理和操作变得更加灵活和高效。在本文中，我们将探讨MySQL视图、存储过程和函数的定义、用途以及如何创建和使用它们。

一、MySQL视图

1、什么是MySQL视图？

在 MySQL 里，“视图”有两层含义：

• 一个是 view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是 create view … ，而它的查询方法与表一样。

通俗来讲，视图只保存了查询的 SQL 逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条 SQL 查询语句上

• 另一个是 InnoDB 在实现 MVCC 时用到的一致性读视图，即 consistent read view，用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现。

它没有物理结构，作用是事务执行期间用来定义“我能看到什么数据”。

2、MySQL视图的特点

• 查询数据：视图用于查询和访问数据库中的数据。你可以像查询表一样使用SELECT语句来查询视图，获取符合条件的数据集。
• 数据修改限制：视图是基于查询结果生成的虚拟表，当你尝试对视图进行修改操作时，可能会受到限制。一般情况下，对视图的修改操作（如INSERT,UPDATE、DELETE）可能会导致底层表的数据修改，但并非所有类型的视图都允许进行修改操作。
• 数据安全性：使用视图可以控制用户对数据的访问权限。你可以在视图中定义过滤条件、隐藏敏感数据或限制特定的列的访问权限，从而增强数据的安全性。
• 简化复杂查询：通过创建视图，可以将复杂的查询和联接操作封装到一个视图中。这样可以简化查询语句的编写，并提高查询的可维护性和可重用性。
• 数据独立性：当底层表的结构发生变化时，视图的定义保持不变。这意味着你可以在底层表修改后继续使用视图，而不需要修改查询语句或应用程序。

总之，MySQL视图被设计用于查询数据，提供了一种虚拟表的机制，具有数据安全性、简化复杂查询和数据独立性等特点。然而，在使用视图时，需要注意其对查询性能的影响以及对修改操作的限制。

3、使用步骤

(1).创建

代码如下（示例）：

CREATE [OR REPLACE] VIEW 视图名称[(列表名称)] 
AS 
SELECT 语句 
[WITH [CASCADED | LOCAL] CHECK OPTION]

[OR REPLACE] 可不加，除非是想替换某视图

(2).查询

代码如下（示例）：

//查看创建视图语句
SHOW CREATE VIEW 视图名称;  //所得的 Create View 列

//查看创建视图结构
DESCRIBE 视图名称;

//查看视图的属性信息
SHOW table status like "视图名称%";

//查看视图数据
SELECT * FROM 视图名称;

//查看视图数据就像查表一样，后面也可以加 where 条件 ( 视图是一种虚拟存在的表 )

(3).修改

代码如下（示例）：

ALTER VIEW 视图名称[(列表名称)]
AS 
SELECT 语句 
[WITH [CASCADED | LOCAL] CHECK OPTION]

//SELECT语句内做修改操作
alter view user_1 as select id, name from user where name like "张%";

(4).删除

代码如下（示例）：

DROP VIEW [IF EXISTS] 视图名称 

二、MySQL存储过程和函数

1、概念

存储过程（Stored Procedures）：存储过程是一组预先编译好的SQL语句集合，它们被存储在数据库中并可以在需要时被调用执行。存储过程可以接受参数并返回结果，使得数据库逻辑可以在数据库服务器上执行，减少了网络通信的开销，并提高了性能和安全性。

思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

特点

• 封装，复用
• 可以接收参数，也可以返回数据，（此点有些像一般函数的作用）
• 减少网络交互，效率提升

函数（Functions）：函数是一段可重复使用的SQL代码，它接受输入参数并返回一个值。MySQL提供了许多内置函数，同时也允许用户创建自定义函数。函数可以用于在查询中进行计算、转换数据或执行其他逻辑操作，使得数据库的应用逻辑更加灵活和可扩展。

思想上都很简单，就是数据库 SQL 语言层面的代码封装与重用。

2、对比存储过程和存储函数

此外，存储函数可以放在查询语句中使用，存储过程不行。反之存储过程的功能更加强大，包括能够执行对表的操作(比如创建表,删除表等)和事务操作，这些功能是存储函数不具备的。

3、使用

（1）创建存储过程

DELIMITER ///    // 以///作为结束标记

CREATE PROCEDURE 存储过程名称  ([参数列表])  //可以无参
BEGIN 
 SQL 语句;
END ///

DELIMITER ;  // 恢复；为结束标记

（2）存储过程调用

CALL 名称 ([参数]);

（3）存储过程查看

// 库下存储过程有：
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx';

// 查看创建语句
SHOW CREATE PROCEDURE 存储过程名称;

// information_schema 查看系统表，条件 routine_schema 指定数据库名字

// 查询指定数据库的存储过程及状态信息
select * from information_schema.routines where routine_schema = 'test';

（3）存储过程删除

DROP PROCEDURE [IF EXISTS] 存储过程名称;

存储过程使用起来简单方便，但缺点也是很明显的。
1、可移植性差。存储过程不能跨数据库移植，比如在MySQL、Oracle 和SQL Server里编写的存储过程，在换成其他数据库时都需要重新编写。
2、调试困难。只有少数DBMS支持存储过程的调试。对于复杂的存储过程来说，开发和维护都不容易。虽然也有一些第三方工具可以对存储过程进行调试，但要收费。
3、存储过程的版本管理很困难。比如数据表索弓|发生变化了，可能会导致存储过程失效。我们在开发软件的时候往往需要进行版本管理，但是存储过程本身没有版本控制，版本迭代更新的时候很麻烦。
4、它不适合高并发的场景。高并发的场景需要减少数据库的压力，有时数据库会采用分库分表的方式，而且对可扩展性要求很高，在这种情况下，存储过程会变得难以维护，增加数据库的压力，显然就不适用了。

4、小结

存储过程既方便，也有局限性。很多公司在开发项目选择使用存储过程，对于我们开发人员来说，不论怎样，掌握存储过程都是必备的技能之-。