MySQL - Test Data Generation

MySQL - Test Data Generation

Colleagues hello to all.

In today's article, we'll talk about how to generate test data in MySQL.

Sooner or later, in the work of a database administrator, a situation arises when it is necessary to check the database for performance, but when the database has very little data then it will not be possible to check the performance. To solve such a problem, we need where then take this data. The option may be that we can make some kind of script that will generate data and insert this data into tables, but this is not the best solution. 

To solve our problem, we can make a procedure that will generate data and insert it into our table.

 

Article content:

  1. Creating a test database.
  2. Create a table.
  3. Create a procedure.
  4. Checking the operation of the procedure.

 

1. Creating a test database.

First, we need to create a database that will store the test data. Let's create a database named test_data;

sql> CREATE DATABASE test_data;


 

2. Creating a table.

The next step is to create a table in our test database called data;

sql> create table data (id int not null auto_increment primary key, name1 varchar(10));


 

3. Creating a procedure.

Now it's the turn to create a procedure that will generate test data for us.

delimiter $$
create or replace procedure data_generation (in p_range int)
begin
declare i int default 0;
while i < p_range
insert into data (name1) values ​​(concat('1d',i));
set i = i + 1;
end while;
$$
delimiter ;


 

4. Checking the operation of the procedure.

Now, in order to check the procedure that we have created, we just need to call it and pass one parameter, this parameter will be responsible for how many rows we need to insert into the table.

Run the procedure and indicate that we want to generate 100 lines.

sql> call data_generation(100);

As a result, we see that the data is successfully generated.


 

Thank you all, I hope that my article helped you in some way.

 

SIMILAR ARTICLES

MYSQL - How to change user password
MYSQL - How to change user password
MYSQL
MySQL / MariaDB How to get the id of the last inserted record in a table - LAST_INSERT_ID
MySQL / MariaDB How to get the id of the last inserted record in a table - LAST_INSERT_ID
MYSQL
MySQL/MariaDB How to convert character case - UPPER and LOWER functions
MySQL/MariaDB How to convert character case - UPPER and LOWER functions
MYSQL