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

一、是什么

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

二、能干嘛

其實就是相當于我們java封裝的方法啦，可以實現(xiàn)某個功能的代碼集，可以復用，很方便。比如我現(xiàn)在要往一個表里插入1000萬的數(shù)據(jù)，如果要用函數(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ù)：

創(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ù)，一重啟就失效了，所以可以在配置文件的[mysqld]標簽下加上這么一行：

log_bin_trust_function_creators=1

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

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

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遇到第一個分號的時候就認為結束了，所以這個相當重新定義結束符號。

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

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

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

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

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

select now() from dual;

就會顯示當前時間，是因為MySQL自帶了now()函數(shù)，那么如果我執(zhí)行：

select rand_string(2) from dual;

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

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

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ù)，這里關閉了自動提交，因為存儲過程里面也很多語句，沒執(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. 調用存儲過程：

調用的sql如下：

delimiter ;
call insert_dept(100, 10);

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

執(zhí)行結果如下：

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

delimiter ;
call insert_emp(100001, 500000);

執(zhí)行結果：

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

select * from emp;

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