mysql压力测试脚本实例

CREATE TABLE dept( /*部门表*/
deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0,
dname VARCHAR(20)  NOT NULL  DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

CREATE TABLE emp
(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
)ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
losal DECIMAL(17,2)  NOT NULL,
hisal DECIMAL(17,2)  NOT NULL
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

delimiter $$

drop  function rand_string $$

create function rand_string(n INT)
returns varchar(255)
begin
 declare chars_str varchar(100) default
   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
 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 $$

delimiter ;
select rand_string(6);

delimiter $$
drop  function rand_num $$

create function rand_num( )
returns int(5)
begin
 declare i int default 0;
 set i = floor(10+rand()*500);
return i;
  end $$

delimiter ;
select rand_num();

delimiter $$
drop procedure insert_emp $$
 
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 values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
  until i = max_num
 end repeat;
   commit;
 end $$
delimiter ;

call insert_emp(100001,1800000);

delimiter $$
drop procedure insert_dept $$

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 values ((start+i) ,rand_string(10),rand_string(8));
  until i = max_num
 end repeat;
   commit;
 end $$

delimiter ;
call insert_dept(100,10);

delimiter $$
drop procedure insert_salgrade $$
create procedure insert_salgrade(in start int(10),in max_num int(10))
begin
declare i int default 0;
 set autocommit = 0;
 ALTER TABLE emp DISABLE KEYS; 
 repeat
 set i = i + 1;
 insert into salgrade values ((start+i) ,(start+i),(start+i));
  until i = max_num
 end repeat;
   commit;
 end $$
delimiter ;
#测试不需要了
#call insert_salgrade(10000,1000000);