专栏首页JavaEEMySQL函数&存储过程

MySQL函数&存储过程

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

一、是什么

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

二、能干嘛

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

1. 建库建表:

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. 设置参数:

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

this function has none of deterministic……

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

show variables like 'log_bin_trust_function_creators';

执行结果

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

set global log_bin_trust_function_creators=1;

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

log_bin_trust_function_creators=1

3. 创建函数:

  • 创建一个函数,用来产生随机字符串,当做员工编号。
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中。

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

我们知道,执行:

select now() from dual;

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

select rand_string(2) from dual;

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

执行结果

  • 创建一个函数,用来生成随机数,当做部门编号:
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函数,那么就是执行:

drop function rand_num;

4. 创建存储过程:

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 $$

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

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如下:

delimiter ;
call insert_dept(100, 10);

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

执行结果如下:

执行结果

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

delimiter ;
call insert_emp(100001, 500000);

执行结果:

执行结果

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

select * from emp;

执行结果

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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • <提升效率>Mysql函数(function)|存储过程(procedure)函数存储过程小结

    function_procedure 函数 mysql内置的函数很好用,同样mysql也支持用户自定义函数 1.为避免和函数中的语句结束符;冲突,将语句结束...

    zhaoolee
  • Mysql存储过程和存储函数

    1.5.2. CASE - WHEN - THEN - ELSE - END CASE

    爱撒谎的男孩
  • mysql存储过程和存储函数的使用

    create procedure name(IN | OUT |INOUT str STRING) #定义存储过程名字

    93年的老男孩
  • MySQL存储过程

    可惜啊!MySQL目前并不支持在SQL语句中存在流控制语句,例如上面的IF NOT EXISTS THEN END IF;让人痛心疾首。但是我们可以使用存储过程...

    Dabelv
  • Mysql存储过程

    存储过程简单来说,就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件。虽然他们的作用不仅限于批处理。

    Java架构师历程
  • MySQL存储过程

    SQL语句需要先编译然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的...

    流柯
  • mysql存储过程

    CONCAT(person_no,"号犯人住", i , "号床位"); ---字符串拼接

    用户5927264
  • mysql存储过程

    (如果存储过程存在删除后创建!如果不写只能执行一次) 2.DELIMITER //

    用户5899361
  • MySQL 存储过程

      MySQL 5.0 版本开始支持存储过程。存储过程(Stored Procedure)是一种在数据库中存储复杂程序,以便外部程序调用的一种数据库对象。存储过...

    Demo_Null

扫码关注云+社区

领取腾讯云代金券