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=13. 创建函数：
- 创建一个函数，用来产生随机字符串，当做员工编号。
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就会被记录到日志中了。
腾讯云开发者
