上一篇文章中說到了定位慢sql，拿到了慢sql后，我們要怎么重現(xiàn)問題呢？那么就需要造數(shù)據(jù)。函數(shù)和存儲過程就可以幫助我們造大量的數(shù)據(jù)，用來重現(xiàn)生產(chǎn)環(huán)境的問題。

一、是什么

函數(shù)和存儲過程都是sql的集合，就是用sql寫的一段代碼。函數(shù)與存儲過程的區(qū)別就是函數(shù)有返回值，存儲過程沒有返回值。

二、能干嘛

其實(shí)就是相當(dāng)于我們java封裝的方法啦，可以實(shí)現(xiàn)某個功能的代碼集，可以復(fù)用，很方便。比如我現(xiàn)在要往一個表里插入1000萬的數(shù)據(jù)，如果要用函數(shù)或者存儲過程來實(shí)現(xiàn)，該怎么做呢？

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. 設(shè)置參數(shù)：

創(chuàng)建函數(shù)的時候，可能會報錯：

this function has none of deterministic……

我們得開啟一個參數(shù)，首先執(zhí)行如下語句可以查看該參數(shù)：

show variables like 'log_bin_trust_function_creators';

可以看到現(xiàn)在是off狀態(tài)的，執(zhí)行以下sql將其開啟：

set global log_bin_trust_function_creators=1;

不過之前也說過，通過這種方式設(shè)置的參數(shù)，一重啟就失效了，所以可以在配置文件的[mysqld]標(biāo)簽下加上這么一行：

log_bin_trust_function_creators=1

3. 創(chuàng)建函數(shù)：

• 創(chuàng)建一個函數(shù)，用來產(chǎn)生隨機(jī)字符串，當(dāng)做員工編號。

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 $$聲明了兩個美元符$$表示程序的結(jié)束。因?yàn)閒unction里面會有很多行sql，如果還是分號表示結(jié)束的話，那可能function遇到第一個分號的時候就認(rèn)為結(jié)束了，所以這個相當(dāng)重新定義結(jié)束符號。

• 然后創(chuàng)建一個名為rand_string，輸入?yún)?shù)為int類型的n，返回值為varchar類型；

• 接著定義了一個字符串chars_str以及返回值return_str；

• 最后循環(huán)從chars_str中截取字符設(shè)置到return_str中。

那么如何驗(yàn)證這個函數(shù)有沒有創(chuàng)建成功呢？

我們知道，執(zhí)行：

select now() from dual;

就會顯示當(dāng)前時間，是因?yàn)镸ySQL自帶了now()函數(shù)，那么如果我執(zhí)行：

select rand_string(2) from dual;

會返回字符串，那說明函數(shù)創(chuàng)建成功了。

• 創(chuàng)建一個函數(shù)，用來生成隨機(jī)數(shù)，當(dāng)做部門編號：

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函數(shù)，那么就是執(zhí)行：

drop function rand_num;

4. 創(chuàng)建存儲過程：

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

這個存儲過程就是往員工表插入數(shù)據(jù)，這里關(guān)閉了自動提交，因?yàn)榇鎯^程里面也很多語句，沒執(zhí)行一次就提交一次很麻煩，所以等存儲過程執(zhí)行完手動提交。然后再創(chuàng)建往部門表插數(shù)據(jù)的存儲過程，如下：

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. 調(diào)用存儲過程：

調(diào)用的sql如下：

delimiter ;
call insert_dept(100, 10);

首先將結(jié)束符改回分號，然后調(diào)用兩個存儲過程，100表示編號從100開始，10表示插入10條數(shù)據(jù)。

執(zhí)行結(jié)果如下：

然后再往emp表插入50萬數(shù)據(jù)：

delimiter ;
call insert_emp(100001, 500000);

執(zhí)行結(jié)果：

插50萬數(shù)據(jù)22秒就搞定了，還是很快的，接下來查詢emp表的數(shù)據(jù)：

select * from emp;

查50萬數(shù)據(jù)，耗時1.39秒，如果把慢查日志的閥值設(shè)置為1s，那么該sql就會被記錄到日志中了。