Several ways to insert test data in batches in MySQL

Foreword

During the development process, whether we use it to test performance or make the page display more beautiful in the production environment, or to learn and verify a certain knowledge point, we often need some test data. At this time, if we type by hand, ten or twenty lines are fine. If there are too many, it will be fatal. Next, we will introduce two commonly used MySQL test data batch generation methods.

  • Storage method + function
  • Navicat data generation

Table

Two tables have been prepared. Character table:

  • id: self-increasing
  • role_name: random string, no duplicates allowed
  • orders: any number from 1-1000

user table:

  • id: self-increasing
  • username: random string, no repetitions allowed
  • password: random string, duplicates allowed
  • role_id: any number between 1-10w

Table creation statement:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL COMMENT 'username',
  `role_id` int(11) DEFAULT NULL COMMENT 'role id',
  `password` varchar(255) DEFAULT NULL COMMENT 'password',
  `salt` varchar(255) DEFAULT NULL COMMENT 'salt',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `role_name` varchar(255) DEFAULT NULL COMMENT 'role name',
  `orders` int(11) DEFAULT NULL COMMENT 'Sort weight\r\
',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Use function generation

Quickly insert through stored procedures, and ensure that data is not repeated through functions

Set to allow creation of functions

Check if MySQL allows creating functions

SHOW VARIABLES LIKE 'log_bin_trust_function_creators';

The result is as shown in the figure. We use the following command to turn on the create function function (global-all sessions are effective)

SET GLOBAL log_bin_trust_function_creators=1;

At this time, another query will show that it is open.

generate random string

-- Randomly generate string
DELIMITER $$
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 $$

-- If you want to delete
-- drop function rand_string;

Generate random numbers

-- used to randomly generate interval numbers
DELIMITER $$
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
 DECLARE i INT DEFAULT 0;
 SET i = FLOOR(from_num + RAND()*(to_num -from_num + 1));
RETURN i;
END$$

-- If you want to delete
-- drop function rand_num;

Create a stored procedure

Insert character sheet

--Insert role data
DELIMITER $$
CREATE PROCEDURE insert_role(max_num INT)
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO role (role_name,orders) VALUES (rand_string(8),rand_num(1,5000));
UNTIL i = max_num
END REPEAT;
COMMIT;
END$$
 
-- delete
-- DELIMITER ;
-- drop PROCEDURE insert_role;

Insert user table

--Insert user data
DELIMITER $$
CREATE PROCEDURE insert_user(START INT, max_num INT)
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO user (username, role_id, password, salt) VALUES (rand_string(8), rand_num(1,100000), rand_string(10), rand_string(10));
UNTIL i = max_num
END REPEAT;
COMMIT;
END$$
 
-- delete
-- DELIMITER ;
-- drop PROCEDURE insert_user;

Execute stored procedure

-- Execute the stored procedure and add 100,000 pieces of data to the dept table
CALL insert_role(100000);

-- Execute the stored procedure and add 1 million pieces of data to the emp table, numbering starting from 100000
CALL insert_user(100000,1100000);

Summary

The execution took almost half a minute for 100,000 data, and more than 20 minutes for 1,000,000 data. At the same time, the user’s storage was still stuck for a long time…

In the end, everything was successfully added, but the auto-increment value and the number of rows were inconsistent. I don’t know why…

Data Display

user table

Use Navicat’s own data generation

Next we use Navicat’s data generation

Go directly to the next step, and then select the corresponding two tables to generate the number of rows and the corresponding generation rules. Based on the previous execution speed, this time role generates 10,000 data and user generates 10,000 data.

For string type fields, we can set up its random data generator and select it as needed

For example, role name, if you select Position name, you can also select whether it contains null, etc.

But if it is name, you will be asked to choose whether it is unique or not.

Number will let you choose the range, default value, etc.

Once confirmed, we can click on the lower right corner to generate random test data.

From the results, you can see that it took 11 seconds to generate 110,000 test data, which is much faster than the first method. It is recommended.

Finally: The following are supporting learning materials. For those who are doing [software testing], it should be the most comprehensive and complete preparation warehouse. This warehouse has also accompanied me through the most difficult journey. I hope it can also help you. ! [100% free without any tricks]

Software testing interview applet

A software test question bank that has been used by millions of people! ! ! Who is who knows! ! ! The most comprehensive interview test mini program on the Internet, you can use your mobile phone to answer questions, take the subway, bus, and roll it up!

Covering these interview question sections:

1. Basic theory of software testing, 2. web, app, interface function testing, 3. network, 4. database, 5. linux

6. Web, app, interface automation, 7. Performance testing, 8. Programming basics, 9. HR interview questions, 10. Open test questions, 11. Security testing, 12. Computer basics

How to obtain the full set of information: Click on the small card below to get it yourself

The knowledge points of the article match the official knowledge files, and you can further learn relevant knowledge. MySQL entry skill treeSQL advanced skillsCTE and recursive query 76606 people are learning the system