MySQL - Test Data Generation
Last updated: Sep 24, 2022
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.
- Creating a test database.
- Create a table.
- Create a procedure.
- 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.
create or replace procedure data_generation (in p_range int)
declare i int default 0;
while i < p_range
insert into data (name1) values (concat('1d',i));
set i = i + 1;
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.