MySQL存储过程和存储函数的使用

本文涉及的产品
云原生数据库 PolarDB 分布式版,标准版 2核8GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB PostgreSQL 版,企业版 4核16GB
推荐场景:
HTAP混合负载
简介: MySQL的存储过程和存储函数在功能和用法上有明显的区别。存储过程是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,通过指定名称和参数(如果有)来调用执行,可以返回多个值或结果集,但不直接返回值。而存储函数则是一个有返回值的特殊存储过程,它返回一个值或表对象,可以直接嵌入SQL语句中使用,如SELECT语句中。两者都是为了提高SQL代码的重用性和性能,但使用场景和方式有所不同。

MySQL存储过程和存储函数

MySQL中提供存储过程与存储函数机制,我们先将其统称为存储程序,一般的SQL语句需要先编译然后执行,存储程序是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,当用户通过指定存储程序的名字并给定参数(如果该存储程序带有参数)来调用才会执行。

1.1 存储程序优缺点

  • 优点

通常存储过程有助于提高应用程序的性能。当创建,存储过程被编译之后,就存储在数据库中。 但是,MySQL实现的存储过程略有不同。 MySQL存储过程按需编译。 在编译存储过程之后,MySQL将其放入缓存中。 MySQL为每个连接维护自己的存储过程高速缓存。 如果应用程序在单个连接中多次使用存储过程,则使用编译版本,否则存储过程的工作方式类似于查询。

1)性能:存储过程有助于减少应用程序和数据库服务器之间的流量,因为应用程序不必发送多个冗长的SQL语句,而只能发送存储过程的名称和参数。

2)复用:存储的程序对任何应用程序都是可重用的和透明的。 存储过程将数据库接口暴露给所有应用程序,以便开发人员不必开发存储过程中已支持的功能。

3)安全:存储的程序是安全的。 数据库管理员可以向访问数据库中存储过程的应用程序授予适当的权限,而不向基础数据库表提供任何权限。

  • 缺点

1)如果使用大量存储过程,那么使用这些存储过程的每个连接的内存使用量将会大大增加。 此外,如果在存储过程中过度使用大量逻辑操作,则CPU使用率也会增加,因为数据库服务器的设计不偏于逻辑运算。

2)很难调试存储过程。只有少数数据库管理系统允许调试存储过程。不幸的是,MySQL不提供调试存储过程的功能。

1.2 数据准备

  • 创建数据库:
DEFAULT CHARACTER SET utf8;
use test;
这里记得设置编码!
  • 创建测试表:
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
insert  into `class`(`id`,`name`) values 
(1,'Java'),
(2,'UI'),
(3,'产品');
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `class_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
/*Data for the table `student` */
insert  into `student`(`id`,`name`,`class_id`) values 
(1,'张三',1),
(2,'李四',1),
(3,'王五',2),
(4,'赵刘',1),
(5,'钱七',3);
  • 查询数据:
select * from class;
select * from student;

1.3 存储过程的使用

  • 语法
CREATE PROCEDURE procedure_name ([parameters[,...]])
begin
-- SQL语句
end ;
  • 示例
create procedure test1()
begin
        select 'Hello';
end;
  • 调用存储过程
call test1();

  • 查看存储过程
-- 查看db01数据库中的所有存储过程
select name from mysql.proc where db='test';
-- 查看存储过程的状态信息
show procedure status;
-- 查看存储过程的创建语句
show create procedure test1;
  • 删除存储过程
drop procedure test1;

1.2 存储过程的语法

1.2.1 变量

  • declare:声明变量
CREATE PROCEDURE test2 ()
begin
        
        declare num int default 0;                -- 声明变量,赋默认值为0
        select num+10;
        
end ;
call test2();                        -- 调用存储过程

  • set:赋值操作
CREATE PROCEDURE test3 ()
begin
        
        declare num int default 0;
        set num =20;                        -- 给num变量赋值
        select num;
        
end ;
call test3();

  • into:赋值
CREATE PROCEDURE test4 ()
begin
        
        declare num int default 0;                        
        select count(1) into num from student;
        select num;
end ;
call test4();

1.2.2 if语句

  • 需求:根据class_id判断是Java还是UI还是产品
CREATE PROCEDURE test5 ()
begin
        
        declare id int default 1;                        
        declare class_name varchar(30);
        
        if id=1 then
                set class_name='哇塞,Java大佬!';
        elseif id=2 then
                set class_name='原来是UI的啊';
        else
                set class_name='不用想了,肯定是产品小样';
        end if;
        
        select class_name;
end ;
call test5();

1.2.3 传递参数

  • 语法
create procedure procedure_name([in/out/inout] 参数名  参数类型)
  • in:该参数可以作为输入,也就是需要调用方传入值 , 默认
  • out:该参数作为输出,也就是该参数可以作为返回值
  • inout:既可以作为输入参数,也可以作为输出参数

1.2.3.1 in-输入参数

-- 定义一个输入参数
CREATE PROCEDURE test6 (in id int)
begin
        
        declare class_name varchar(30);
        
        if id=1 then
                set class_name='哇塞,Java大佬!';
        elseif id=2 then
                set class_name='原来是UI的啊';
        else
                set class_name='不用想了,肯定是产品小样';
        end if;
        
        select class_name;
end ;
call test6(3);

1.2.3.2 out-输出参数

-- 定义一个输入参数和一个输出参数
CREATE PROCEDURE test7 (in id int,out class_name varchar(100))
begin
        if id=1 then
                set class_name='哇塞,Java大佬!';
        elseif id=2 then
                set class_name='原来是UI的啊';
        else
                set class_name='不用想了,肯定是产品小样';
        end if;
        
end ;
call test7(1,@class_name);        -- 创建会话变量                
select @class_name;                -- 引用会话变量

@xxx:代表定义一个会话变量,整个会话都可以使用,当会话关闭(连接断开)时销毁 @@xxx:代表定义一个系统变量,永久生效。

1.2.4 case语句

  • 需求:传递一个月份值,返回所在的季节。
CREATE PROCEDURE test8 (in month int,out season varchar(10))
begin
        
        case 
                when month >=1 and month<=3 then
                        set season='spring';
                when month >=4 and month<=6 then
                        set season='summer';
                when month >=7 and month<=9 then
                        set season='autumn';
                when month >=10 and month<=12 then
                        set season='winter';
        end case;
end ;
call test8(9,@season);                        -- 定义会话变量来接收test8存储过程返回的值
select @season;

1.3.5 while循环

  • 需求:计算任意数的累加和
CREATE PROCEDURE test10 (in count int)
begin
        declare total int default 0;
        declare i int default 1;
        
        while i<=count do
                set total=total+i;
                set i=i+1;
        end while;
        select total;
end ;
call test10(10);

1.3.6 repeat循环

  • 需求:计算任意数的累加和
CREATE PROCEDURE test11 (count int)                -- 默认是输入(in)参数
begin
        declare total int default 0;
        repeat 
                set total=total+count;
                set count=count-1;
                until count=0                                -- 结束条件,注意不要打分号
        end repeat;
        select total;
end ;
call test11(10);

1.3.7 loop循环

  • 需求:计算任意数的累加和
CREATE PROCEDURE test12 (count int)                -- 默认是输入(in)参数
begin
        declare total int default 0;        
        sum:loop                                                        -- 定义循环标识
                set total=total+count;
                set count=count-1;
                
                if count < 1 then
                        leave sum;                                        -- 跳出循环
                end if;
        end loop sum;                                                -- 标识循环结束
        select total;
        
end ;
call test12(10);

1.3.8 游标

游标是用来存储查询结果集的数据类型,可以帮我们保存多条行记录结果,我们要做的操作就是读取游标中的数据获取每一行的数据。

  • 声明游标
declare cursor_name cursor for statement;
  • 打开游标
open cursor_name;
  • 关闭游标
close cursor_name;
  • 案例:
CREATE PROCEDURE test13 ()                -- 默认是输入(in)参数
begin
        
        declare id int(11);
        declare `name` varchar(20);
        declare class_id int(11);
        -- 定义游标结束标识符
        declare has_data int default 1;
        
        declare stu_result cursor for select * from student;
        -- 监测游标结束
        declare exit handler for not FOUND set has_data=0;
        
        -- 打开游标
        open stu_result;
        
        repeat 
                fetch stu_result into id,`name`,class_id;
                
                select concat('id: ',id,';name: ',`name`,';class_id',class_id);
                until has_data=0                -- 退出条件,注意不要打分号
        end repeat;
        
        -- 关闭游标
        close stu_result;
        
end ;
call test13();

1.3 存储过程和存储函数的区别

  • 存储函数的限制比较多,例如不能用临时表,只能用表变量,而存储过程的限制较少,存储过程的实现功能要复杂些,而函数的实现功能针对性比较强。
  • 返回值不同。存储函数必须有返回值,且仅返回一个结果值;存储过程可以没有返回值,但是能返回结果集(out,inout)。
  • 调用时的不同。存储函数嵌入在SQL中使用,可以在select 存储函数名(变量值);存储过程通过call语句调用 call 存储过程名。
  • 参数的不同。存储函数的参数类型类似于IN参数,没有类似于OUTINOUT的参数。存储过程的参数类型有三种,inoutinout
  • in:数据只是从外部传入内部使用(值传递),可以是数值也可以是变量
  • out:只允许过程内部使用(不用外部数据),给外部使用的(引用传递:外部的数据会被先清空才会进入到内部),只能是变量
  • inout:外部可以在内部使用,内部修改的也可以给外部使用,典型的引用 传递,只能传递变量。

1.3.1 临时表

临时表顾名思义就是临时要用创建的表,临时表的作用仅限于本次会话,等连接关闭后重新打开连接临时表将不存在

  • 创建一张临时表:
create temporary table temp_table(
        id int,
        name varchar(10)
);
insert into temp_table values (1,'1');
select * from temp_table ;

temporary:代表创建的表是一张临时表;

  • 注意:临时表示查询不到的
show tables;   -- 不会显示临时表的存在
  • 测试存储过程创建临时表:
create procedure pro1()
begin
        create temporary table temp_table(
                id int
        );
        
        insert into temp_table values(1);
        
        select * from temp_table;
end;
call pro1();

运行没有任何问题
  • 测试存储函数创建临时表
create function fun2()
returns int
begin
        declare id int ;
        create table temp_table(                                
                id int
        );
        
        insert into temp_table values(1);
        
        select id from into id temp_table;        
        return id;
end;

发现报错。

1.4 谈谈为什么大部分公司为什么不用存储过程(函数)?

1.4.1 原因一

参考1.1小结说的存储过程缺点

1.4.2 原因二

咱们分析三层架构就知道了,咱们的业务逻辑应该放到咱们的业务层,也就Tomcat,而不是把业务滞留到数据库来处理,将业务和数据库严重耦合在一起了!这是导致公司开发不使用存储过程的一个重要原因

1.4.3 原因三

咱们平时对业务性能进行扩容非常好,搭建集群、使用缓存提高响应速度等等。总之,大多数情况下并不是业务层是整个项目性能的瓶颈,而是数据库!我们应该尽可能的优化数据库方面的性能,而且业务层性能扩容相对于数据库性能扩容要方便的多。因此我们应该尽可能的优化数据库方面的性能,降低数据层的压力,把所有压力能分单到其他地方就分担,而不是让数据库增加压力!

好了,本节就说到这里了,童鞋们看完记得点赞

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
8天前
|
存储 JSON 关系型数据库
mysql中find_in_set()函数用法详解及增强函数
总结而言,`FIND_IN_SET()`是MySQL中处理由逗号分隔的字符串列表的一种便捷方法,尤其适用于列表相对较短且不经常更改的场景。然而,对于更为复杂的需要高性能和可扩展性的数据库设计,它可能不是最优选择,应考虑使用更加正规化的数据库结构。
11 2
mysql中find_in_set()函数用法详解及增强函数
|
12天前
|
关系型数据库 MySQL
mysql使用 CONCAT(字段,字段) 函数拼接
mysql使用 CONCAT(字段,字段) 函数拼接
|
3天前
|
存储 SQL 监控
为什么要避免使用存储过程、触发器和函数
为什么要避免使用存储过程、触发器和函数
|
28天前
|
JSON 关系型数据库 MySQL
MySQL常用函数解读:从基础到进阶的全方位指南
MySQL常用函数解读:从基础到进阶的全方位指南
|
8天前
|
存储 SQL 关系型数据库
实时计算 Flink版产品使用问题之要配置MySQL集群存储节点,该如何配置
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
26天前
|
存储 关系型数据库 分布式数据库
PolarDB产品使用问题之在将RDS迁移到PolarDB后,原先由root用户创建的视图、存储过程等是否可以继续使用的
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
28天前
|
SQL 关系型数据库 MySQL
mysql中GROUP_CONCAT函数详解
mysql中GROUP_CONCAT函数详解
20 0
|
1月前
|
存储 SQL 关系型数据库
在MySQL中使用存储过程返回更新前的记录
在MySQL中使用存储过程返回更新前的记录
15 0
|
1月前
|
存储 SQL 关系型数据库
MySQL快速回顾:计算字段与函数
MySQL快速回顾:计算字段与函数
|
1月前
|
存储 关系型数据库 MySQL
索引的威力--记一次MySQL存储过程优化
在MySQL存储过程中,一个`INSERT INTO SELECT`语句起初执行超过130秒,优化后,执行时间降低到1秒内,实现了100倍的性能提升。问题在于`NOT IN`子查询导致的慢查询,最终通过创建单列索引获得了最佳效果。文章还介绍了索引创建的基本语法,并讨论了单列索引与组合索引的优缺点。作者强调,随着数据量增加,索引对于查询性能的重要性,计划未来采用读写分离来进一步优化处理大量插入和查询的场景。

相关产品

  • 云数据库 RDS MySQL 版