MySQL: Generate Random Data using Stored Procedure

If you have setup your MySQL cluster or standalone database server correctly, you might need to do a stress test to the server. You can use many ways to achieve this and in my case, I will use the simplest way which is create generate a fake data and insert them into table using stored procedure. Actually, you can use any other script like BASH, Perl or PHP as long as it support looping.

We need to loop certain SQL command in order to generate more and more data. Before we start, make sure you have prepare the database and table to be insert into. Following informations are variable that I used:

MySQL: Version 5.5.20 Community Server
Database: dbTest
Table: tbl_data1

Firstly, we need create the database and table. Log into MySQL console or client via appropriate database user (in this case I will just use root), and execute following MySQL command:

CREATE DATABASE dbTest;
USE dbTest;
CREATE TABLE `tbl_data1` (
`a` INT(10) NULL,
`b` INT(10) NULL,
`c` INT(10) NULL
)
ENGINE=InnoDB;

Our aim is to insert 100,000 rows random number between 0 to 1000 into the table.

DELIMITER //
CREATE PROCEDURE GenerateFakeData()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 100000 DO
INSERT INTO tbl_data1 (a,b,c) VALUES ((SELECT floor(rand() * 1000) AS randNum), (SELECT floor(rand() * 1000) AS randNum),(SELECT floor(rand() * 1000) AS randNum));
SET i = i + 1;
END WHILE;
END //
DELIMITER ;

To call the procedure above, execute following command:

USE dbTest;
CALL GenerateFakeData();

This will execute the stored procedure and the table will contains 100,000 rows of data.

SELECT * FROM dbTest.tbl_data1 LIMIT 1,10;

Example results:

+------+------+------+
| a    | b    | c    |
+------+------+------+
|  521 |  662 |  748 |
|  753 |  523 |  356 |
|  212 |  994 |  334 |
|  689 |  441 |  139 |
|  374 |  453 |  145 |
|  364 |  386 |  837 |
|   30 |  637 |   96 |
|  571 |  569 |  129 |
|  940 |  312 |  741 |
|  770 |  628 |  828 |
+------+------+------+
10 rows in set (0.00 sec)

You can start to play around with this table like do some heavy queries, play with indexes and much more.

Leave a comment

Leave a Reply

Your email address will not be published. Required fields are marked *