Mysql stress test script instance _ MySQL

This article mainly introduces the mysql stress test script. The example shows the complete method for implementing the MySQL stress test. if you need it, refer to the example below to describe the mysql stress test script, share it with you for your reference. The details are as follows:

CREATE TABLE DEPT

The code is as follows:

Create table dept (/* Department TABLE */
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 employees

The code is as follows:

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;

Wage level table

The code is as follows:

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,120 0 );
Insert into salgrade VALUES (1400 );
Insert into salgrade VALUES (2000 );
Insert into salgrade VALUES (4,2001, 3000 );
Insert into salgrade VALUES (5, 3001, 9999 );

Generate random strings
Define a new command to end compliance

The code is as follows:

Delimiter $

Delete a custom function

The code is as follows:

Drop function rand_string $

Here I have created a function.

The code is as follows:

Create function rand_string (n INT)
Returns varchar (255)
Begin
Declare chars_str varchar (100) default
'Abcdefghijklmnopqrstuvwxyzabcdefjhijklmnopqrstuvwxy ';
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 );

Randomly generate Department numbers

The code is as follows:

Delimiter $
Drop function rand_num $

Here we have customized a function.

The code is as follows:

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 ();

Insert records into the emp table (massive data)

The code is as follows:

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 the previously written function, with 1800000 records starting from 100001.

The code is as follows:

Call insert_emp (20171,1800000 );

Insert records to the dept table

The code is as follows:

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 );

Insert data to the salgrade table

The code is as follows:

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;
# Testing is not required
# Call insert_salgrade (cost, cost 00 );

I hope this article will help you design MySQL database programs.