MySQL高级操作之视图、自定义函数、存储过程、触发器、事务_mysql创建数据库使用自定义函数、存储过程、触发器、视图、索引-CSDN博客

本文详细介绍了MySQL的高级操作，包括视图的创建、区别与作用，自定义函数的创建与删除，存储过程的语法与优点，触发器的查看与创建，以及事务的自动提交、开启、提交和回滚等操作。通过这些内容，读者可以深入理解MySQL的高级特性和数据库管理的精髓。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

视图

MySQL视图是一个虚拟表

语法

CREATE VIEW <视图名> AS <SELECT 语句>

语法说明

<视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。
<SELECT 语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。
对于创建视图中的SELECT语句存在如下限制：
用户除了拥有 CREATE VIEW 权限外，还具有操作中涉及的基础表和其他视图的相关权限。
SELECT 语句不能引用系统或用户变量。
SELECT 语句不能包含 FROM 子句中的子查询。
SELECT 语句不能引用预处理语句参数。

视图定义中引用的表或视图必须存在。但是，创建完视图后，可以删除定义引用的表或视图。可使用 CHECK TABLE 语句检查视图定义是否存在这类问题。
视图定义中允许使用 ORDER BY 语句，但是若从特定视图进行选择，而该视图使用了自己的 ORDER BY 语句，则视图定义中的 ORDER BY 将被忽略。
视图定义中不能引用 TEMPORARY 表（临时表），不能创建 TEMPORARY 视图。 WITH CHECK OPTION 的意思是，修改视图时，检查插入的数据是否符合 WHERE 设置的条件
视图与表是一对一关系情况：如果没有其它约束（如视图中没有的字段，在基本表中是必填字段情况），是可以进行增删改数据操作；
视图与表是一对多关系情况：如果只修改一张表的数据，且没有其它约束（如视图中没有的字段，在基本表中是必填字段情况），是可以进行改数据操作。

视图和临时表的区别

视图只是一条预编译的 SQL 语句，并不保存实际数据；临时表是保存在 tempdb 中的实际的表
物理空间的分配不一样，视图不分配空间，临时表会分配空间
视图是一个快照是一个虚表；临时表是客观存在的表类型对象 Create TEMPORARY table
使用show tables;时，会出现视图，而临时表不存在；
退出重连mysql后，视图还存在，而临时表已经释放了

作用

提高了重用性，就像一个函数。
对数据库重构，却不影响程序的运行。
提高了安全性能。可以对不同的用户，设定不同的视图。
让数据更加清晰。想要什么样的数据，就创建什么样的视图。

实例

mysql> select * from person_tbl;
+----+----------+
| id | name     |
+----+----------+
|  1 | king     |
|  2 | clarence |
|  3 | tom      |
+----+----------+
3 rows in set (0.00 sec)

mysql> create view view_person as select * from person_tbl;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from view_person;
+----+----------+
| id | name     |
+----+----------+
|  1 | king     |
|  2 | clarence |
|  3 | tom      |
+----+----------+
3 rows in set (0.00 sec)

mysql> insert into person_tbl(name) values('milo');
Query OK, 1 row affected (0.00 sec)

mysql> select * from view_person;
+----+----------+
| id | name     |
+----+----------+
|  1 | king     |
|  2 | clarence |
|  3 | tom      |
|  4 | milo     |
+----+----------+
4 rows in set (0.00 sec)

mysql> update person_tbl set name='admin' where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from view_person;
+----+----------+
| id | name     |
+----+----------+
|  1 | admin    |
|  2 | clarence |
|  3 | tom      |
|  4 | milo     |
+----+----------+
4 rows in set (0.00 sec)
mysql> exit;
#重新连接后
mysql> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| copy_clone       |
| copy_source      |
| learn            |
| person_tbl       |
| t2               |
| view_person      |
+------------------+

mysql> select * from view_person;
+----+----------+
| id | name     |
+----+----------+
|  1 | admin    |
|  2 | clarence |
|  3 | tom      |
|  4 | milo     |
+----+----------+

mysql> show create view view_person;
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View        | Create View                                                                                                                                                                   | character_set_client | collation_connection |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| view_person | CREATE ALGORITHM=UNDEFINED DEFINER=`clarence`@`%` SQL SECURITY DEFINER VIEW `view_person` AS select `person_tbl`.`id` AS `id`,`person_tbl`.`name` AS `name` from `person_tbl` | utf8                 | utf8_general_ci      |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+

自定义函数

语法

创建

CREATE FUNCTION 函数名([参数列表]) RETURNS 数据类型 
BEGIN
	SQL 语句; 
	RETURN 值; 
END;

删除

DROP FUNCTION 函数名;

实例

#带传参的函数 
DELIMITER $$ 				#定义一个结束标识符
DROP FUNCTION IF EXISTS `mytest`.`myfunction`$$  #如果这个函数已经存在了，就删除掉，mytest是数据库的名字，函数是跟数据 库相关联的，myfunction 是函数的名字；
CREATE FUNCTION myfunction(cname VARCHAR(15)) RETURNS INT 
BEGIN
	DECLARE c INT DEFAULT 0; 
	SELECT id FROM `user` WHERE `name`=cname INTO c; 
	RETURN c; 
END $$ 
DELIMITER ; 
#调用函数 
SELECT myfunction("king");

注意：mysql 默认是以 ; 作为结束执行语句，与触发器中需要的分行起冲突，为解决此问题可用 DELIMITER，如：DELIMITER $$，可以将结束符号变成$$。当触发器创建完成后，可以用 DELIMITER ;来将结束符号变成。

函数与存储过程的区别

函数只会返回一个值，不允许返回一个结果集。函数强调返回值，所以函数不允许返回多个值的情况，即使是查询语句。

存储过程

SQL 语句需要先编译然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的 SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。

存储过程是可编程的函数，在数据库中创建并保存，可以由 SQL 语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟，它允许控制数据的访问方式。

MySQL 5.0 以前并不支持存储过程，MySQL 5.0 开始支持存储过程，大大提高数据库的处理速度，同时也提高了数据库编程的灵活性。

语法

创建存储过程

CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体> 

[过程参数[,…] ] 格式为 [ IN | OUT | INOUT ] <参数名> <类型>

过程名: 存储过程的名称，默认在当前数据库中创建。若需要在特定数据库中创建存储过程，则要在名称前面加上数据库的名称，即 db_name.sp_name。需要注意的是，名称应当尽量避免选取与 MySQL 内置函数相同的名称，否则会发生错误。
过程参数: 存储过程的参数列表。其中，<参数名>为参数名，<类型>为参数的类型（可以是任何有效的 MySQL 数据类型）。当有多个参数时，参数列表中彼此间用逗号分隔。存储过程可以没有参数（此时存储过程的名称后仍需加上一对括号），也可以有 1 个或多个参数。
过程体：存储过程的主体部分，也称为存储过程体，包含在过程调用的时候必须执行的 SQL 语句。这个部分以关键字 BEGIN 开始，以关键字 END 结束。若存储过程体中只有一条 SQL 语句，则可以省略 BEGIN-END 标志。

调用存储过程

CALL PROCEDURE <过程名> ( [过程参数[,…] ] )

删除存储过程

DROP PROCEDURE <过程名>

参数类型

存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用","分割开。 MySQL 存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT。

IN：参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
OUT：该值可在存储过程内部被改变，并可返回
INOUT：调用时指定，并且可被改变和返回

实例

#存储过程 IN 参数
mysql> DELIMITER // 
mysql> CREATE PROCEDURE in_param(IN p_in int) 
-> BEGIN 
-> 	SELECT p_in; 
-> 	SET p_in=2; 
-> 	SELECT p_in; 
-> END; 
-> //
mysql> DELIMITER ;
mysql> SET @p_in=1;
mysql> CALL in_param(@p_in);
+------+ 
| p_in | 
+------+
| 1 	| 
+------+
1 row in set (0.00 sec) 
+------+ 
| p_in | 
+------+ 
| 2 | 
+------+ 
1 row in set (0.00 sec)

存储过程 OUT 参数
mysql> DELIMITER // 
mysql> CREATE PROCEDURE out_param(OUT p_out int) 
-> BEGIN 
-> 	SELECT p_out; 
-> 	SET p_out=2; 
-> 	SELECT p_out; 
-> END; 
-> //

mysql> DELIMITER ;
mysql> SET @p_out=1;
mysql> CALL out_param(@p_out);
+-------+
| p_out |
+-------+
|  NULL |
+-------+
1 row in set (0.00 sec)

+-------+
| p_out |
+-------+
|     2 |
+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @p_out;
+--------+
| @p_out |
+--------+
|      2 |
+--------+

#存储过程 INOUT 参数
mysql> DELIMITER //
mysql> CREATE PROCEDURE inout_param(INOUT p_inout int) 
    ->  BEGIN 
    ->  SELECT p_inout; 
    ->  SET p_inout=2; 
    ->  SELECT p_inout; 
    ->  END; 
    ->  //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> SET @p_inout=1;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL inout_param(@p_inout);
+---------+
| p_inout |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

+---------+
| p_inout |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @p_inout;
+----------+
| @p_inout |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

优点

增强 SQL 语言的功能和灵活性：存储过程可以用控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
标准组件式编程：存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的 SQL 语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。
较快的执行速度：如果某一操作包含大量的 Transaction-SQL 代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的 Transaction-SQL 语句在每次运行时都要进行编译和优化，速度相对要慢一些。
减少网络流量：针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的 Transaction-SQL 语句被组织进存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大减少网络流量并降低了网络负载。
作为一种安全机制来充分利用：通过对执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

触发器

查看触发器信息

SELECT * FROM information_schema.triggers; 	#在 triggers 表中查看触发器信息

SHOW TRIGGERS #查看触发器信息

语法

CREATE TRIGGER <触发器名> < BEFORE | AFTER > 
<INSERT | UPDATE | DELETE > 
ON <表名> FOR EACH Row<触发器主体>

CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmt trigger_name：触发器的名称
tirgger_time：触发时机，为 BEFORE 或者 AFTER
trigger_event：触发事件，为 INSERT、DELETE 或者 UPDATE
tb_name：表示建立触发器的表明，就是在哪张表上建立触发器
trigger_stmt：触发器的程序体，可以是一条 SQL 语句或者是用 BEGIN 和 END 包含的多条语句
MySQL 可以创建以下六种触发器： BEFORE INSERT, BEFORE DELETE, BEFORE UPDATE AFTER INSERT, AFTER DELETE, AFTER UPDATE

DELIMITER ||

CREATE TRIGGER trigger_user BEFORE DELETE
	ON `user` FOR EACH ROW
	BEGIN
		INSERT INTO logs VALUES(NOW());
	END
	||

DELIMITER ;

事务

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行
事务用来管理 insert,update,delete 语句。

事务的自动提交

若参数 autocommit=0，事务则在用户本次对数据进行操作时自动开启，在用户执行 commit 命令时提交，用户本次对数据库开始进行操作到用户执行 commit 命令之间的一系列操作为一个完整的事务周期。若不执行 commit 命令，系统则默认事务回滚。总而言之，当前情况下事务的状态是自动开启手动提交。
若参数 autocommit=1（系统默认值），事务的开启与提交又分为两种状态：
- 手动开启手动提交：当用户执行 start transaction 命令时（事务初始化），一个事务开启，当用户执行 commit 命令时当前事务提交。从用户执行 start transaction 命令到用户执行 commit 命令之间的一系列操作为一个完整的事务周期。若不执行 commit 命令，系统则默认事务回滚。
- 自动开启自动提交：如果用户在当前情况下（参数 autocommit=1）未执行 start transaction 命令而对数据库进行了操作，系统则默认用户对数据库的每一个操作为一个孤立的事务，也就是说用户每进行一次操作系都会即时提交或者即时回滚。这种情况下用户的每一个操作都是一个完整的事务周期。

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+

开启事务

start transaction; 
或者
begin transaction;

提交事务

commit;

回滚事务

rollback;

事务的特性（ACID）

原子性（Atomicity，或称不可分割性）
- 一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性（Consistency）
- 在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性（Isolation，又称独立性）
- 数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括
  - 读未提交（Read uncommitted）
  - 读提交（read committed）
  - 可重复读（repeatable read）
  - 串行化（Serializable）。
- Mysql 默认的事务隔离级别为 repeatable-read。
持久性（Durability）
- 事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

命令

#查看事务隔离级别
SHOW VARIABLES LIKE 'tx_isolation';
#查看全局的事务隔离级别
SHOW GLOBAL VARIABLES LIKE 'tx_isolation';
#使用系统变量查询
SELECT @@global.tx_isolation; 
SELECT @@session.tx_isolation; 
SELECT @@tx_isolation;

设置事务的隔离级别

#方式一
#GLOBAL：设置全局的事务隔离级别
#SESSION：设置当前 session 的事务隔离级别，如果语句没有指定 GLOBAL 或 SESSION，默认值为 SESSI ON
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL 
{ 
	REPEATABLE READ 
	| READ COMMITTED 
	| READ UNCOMMITTED 
	| SERIALIZABLE 
}

#方式二
可以在 my.ini 文件中使用 transaction-isolation 选项来设置服务器的缺省事务隔离级别。 
该选项值可以是： 
– READ-UNCOMMITTED 
– READ-COMMITTED 
– REPEATABLE-READ 
– SERIALIZABLE 
例如： 
[mysqld] 
transaction-isolation = READ-COMMITTED

MySQL 事务隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
读已提交（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

脏读：当前事务(A)中可以读到其他事务(B)未提交的数据（脏数据），这种现象是脏读。
不可重复读：在事务 A 中先后两次读取同一个数据，两次读取的结果不一样，这种现象称为不可重复读。脏读与不可重复读的区别在于：前者读到的是其他事务未提交的数据，后者读到的是其他事务已提交的数据。
幻读：在事务 A 中按照某个条件先后两次查询数据库，两次查询结果的条数不同，这种现象称为幻读。不可重复读与幻读的区别可以通俗的理解为：前者是数据变了，后者是数据的行数变了。

不可重复读和幻读的区别及解决方案

不可重复读是读取了其他事务更改的数据，针对 update 操作
解决：使用行级锁，锁定该行，事务 A 多次读取操作完成后才释放该锁，这个时候才允许其他事务更改刚才的数据。
幻读是读取了其他事务新增的数据，针对 insert 与 delete 操作
解决：使用表级锁，锁定整张表，事务 A 多次读取数据总量之后才释放该锁，这个时候才允许其他事务新增数据。