前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MySQL函数&存储过程

MySQL函数&存储过程

作者头像
贪挽懒月
发布2021-06-02 09:37:38
2.6K0
发布2021-06-02 09:37:38
举报
文章被收录于专栏:JavaEE

上一篇文章中说到了定位慢sql,拿到了慢sql后,我们要怎么重现问题呢?那么就需要造数据。函数和存储过程就可以帮助我们造大量的数据,用来重现生产环境的问题。

一、是什么

函数和存储过程都是sql的集合,就是用sql写的一段代码。函数与存储过程的区别就是函数有返回值,存储过程没有返回值。

二、能干嘛

其实就是相当于我们java封装的方法啦,可以实现某个功能的代码集,可以复用,很方便。比如我现在要往一个表里插入1000万的数据,如果要用函数或者存储过程来实现,该怎么做呢?

1. 建库建表:

代码语言:javascript
复制
create database bigData;
use bigData;

# 部门表
create table dept(
    id int unsigned primary key auto_increment,
    deptno mediumint unsigned not null default 0,
    dname varchar(20) not null default "",
    loc varchar(13) not null default ""
);

# 员工表
create table emp(
   id int unsigned primary key auto_increment,
   empno mediumint unsigned not null default 0,
   ename varchar(20) not null default "",
   job varchar(9) not null default "",
   mgr mediumint unsigned  not null default 0,
   hiredate date not null,
   sal decimal(7,2) not null,
   comm decimal(7,2) not null,
   deptno mediumint unsigned not null default 0
);

2. 设置参数:

创建函数的时候,可能会报错:

代码语言:javascript
复制
this function has none of deterministic……

我们得开启一个参数,首先执行如下语句可以查看该参数:

代码语言:javascript
复制
show variables like 'log_bin_trust_function_creators';

执行结果

可以看到现在是off状态的,执行以下sql将其开启:

代码语言:javascript
复制
set global log_bin_trust_function_creators=1;

不过之前也说过,通过这种方式设置的参数,一重启就失效了,所以可以在配置文件的[mysqld]标签下加上这么一行:

代码语言:javascript
复制
log_bin_trust_function_creators=1

3. 创建函数:

  • 创建一个函数,用来产生随机字符串,当做员工编号。
代码语言:javascript
复制
delimiter $$
create function rand_string(n int) returns varchar(255)
begin
       declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyz';
       declare return_str varchar(255) default '';
       declare i int default 0;
       while i < n do
                set return_str = concat(return_str, substring(chars_str, floor(1+rand() * 52), 1));
                set i = i + 1;
       end while;
       return return_str;
end $$

解释一下这个function:

  • 首先用delimiter
声明了两个美元符

表示程序的结束。因为function里面会有很多行sql,如果还是分号表示结束的话,那可能function遇到第一个分号的时候就认为结束了,所以这个相当重新定义结束符号。

  • 然后创建一个名为rand_string,输入参数为int类型的n,返回值为varchar类型;
  • 接着定义了一个字符串chars_str以及返回值return_str;
  • 最后循环从chars_str中截取字符设置到return_str中。

那么如何验证这个函数有没有创建成功呢?

我们知道,执行:

代码语言:javascript
复制
select now() from dual;

就会显示当前时间,是因为MySQL自带了now()函数,那么如果我执行:

代码语言:javascript
复制
select rand_string(2) from dual;

会返回字符串,那说明函数创建成功了。

执行结果

  • 创建一个函数,用来生成随机数,当做部门编号:
代码语言:javascript
复制
delimiter $$
create function rand_num() returns int(5)
begin
   declare i int default 0;
   set i = floor(100 + rand() * 10);
   return i;
end $$

假如要删除rand_num函数,那么就是执行:

代码语言:javascript
复制
drop function rand_num;

4. 创建存储过程:

代码语言:javascript
复制
delimiter $$
create procedure insert_emp(in start int(10), in max_num int(10))
begin
       declare i int default 0;
       set autocommit = 0;
       repeat
       set i = i + 1;
       insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
       values((start + i), rand_string(6), 'salesman', 0001, curdate(), 2000, 4000, rand_num());
       until i = max_num
       end repeat;
       commit;
end $$

这个存储过程就是往员工表插入数据,这里关闭了自动提交,因为存储过程里面也很多语句,没执行一次就提交一次很麻烦,所以等存储过程执行完手动提交。然后再创建往部门表插数据的存储过程,如下:

代码语言:javascript
复制
delimiter $$
create procedure insert_dept(in start int(10), in max_num int(10))
begin
       declare i int default 0;
       set autocommit = 0;
       repeat
       set i = i + 1;
       insert into dept (deptno, dname, loc) values ((start + i), rand_string(10), rand_string(8));
       until i = max_num
       end repeat;
       commit;
end $$

5. 调用存储过程:

调用的sql如下:

代码语言:javascript
复制
delimiter ;
call insert_dept(100, 10);

首先将结束符改回分号,然后调用两个存储过程,100表示编号从100开始,10表示插入10条数据。

执行结果如下:

执行结果

然后再往emp表插入50万数据:

代码语言:javascript
复制
delimiter ;
call insert_emp(100001, 500000);

执行结果:

执行结果

插50万数据22秒就搞定了,还是很快的,接下来查询emp表的数据:

代码语言:javascript
复制
select * from emp;

执行结果

查50万数据,耗时1.39秒,如果把慢查日志的阀值设置为1s,那么该sql就会被记录到日志中了。

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 一、是什么
  • 二、能干嘛
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档