The core idea of creating data

In the work of performance testing , We often encounter the need to create data , This work can let the development students cooperate , But it must depend on the face of the development students ！ encounter nice The development of students naturally work smoothly , But when you meet development students who don't cooperate well , Coordination and leadership are often needed to facilitate this , This is more troublesome ！ This paper simulates the scenario of creating data through the example of circular batch inserting data , What is provided is the dry goods needed to make data SQL Code ！ This article assumes that the reader understands the basic sql How to write statements and stored procedures .

The core idea of creating data is very simple ： Is to write stored procedures , Loop insert data

Create data instance

Here through to mock Insert data into the database to do demo demonstration ：

First , Create a table api_record2, The field information is as follows ：

CREATE TABLE api_record2(
id INT(11) PRIMARY KEY AUTO_INCREMENT,
app_name VARCHAR(30) DEFAULT 'test',
host_ip VARCHAR(30),
access_time DATETIME,
method VARCHAR(10),
url VARCHAR(1000),
api_data VARCHAR(1000),
return_value VARCHAR(1000),
INDEX index_app_name ( app_name )
);

The specific meaning of field information will not be explained here （ I believe you can guess the meaning of naming by looking at it , If you have any questions, please leave a message ）,id It's the primary key ,app_name Indexed .

The next step is to write stored procedures , The code is as follows ：

DELIMITER ;;
DROP PROCEDURE IF EXISTS `insertdata`;
CREATE PROCEDURE insertdata()
BEGIN
   DECLARE i INT;
   SET i=1;
   WHILE(i<=10)DO
       INSERT INTO api_record2 (app_name,host_ip,access_time,method,url,api_data,return_value) VALUES (CONCAT('test',FLOOR( 1 + RAND() * (100 - 1))),'127.0.0.1',NOW(),'post','/yourapp_mock/func2','{"user":"kevin","pwd":"123",}','ok');
       SET i=i+1;
   END WHILE;
   COMMIT; # If it's big data , It is suggested to add a layer of circulation , hold commit Put it on the outside of this layer of circulation , Not to fail , Roll back all 
END;;
DELIMITER ;
CALL insertdata();

Stored procedure statement analysis

DELIMITER ;; sentence ;;

delimiter yes mysql Separator , stay mysql The default separator in the client is a semicolon , In general , On the client command line , If a line SQL Command to “;” ending , After returning , Will execute this command . For example, when creating a stored procedure , Functions, etc , At this time, you need to enter all a section of commands , Finally, execute it at one time , At this point, the interpreter needs to pass delimiter To deal with it , Replace the semicolon with another symbol , Such as ：“//” or “;;”, This explains the semicolon in the command , Realize the one-time execution of this command , Otherwise, an error will be reported , Unable to execute .

DROP PROCEDURE IF EXISTS `insertdata` If stored procedure insertdata There is , Give up execution

CREATE PROCEDURE insertdata()  Create stored procedure

BEGIN The transaction start

END  End of transaction

COMMIT  Transaction submission

WHILE(i<=10)DO

insert sql sentence

SET i=i+1;

END WHILE;

Batch insert data core ： Circular insert 10 statement , Statement implementation is almost consistent with circular statements in other languages

insert In the sentence CONCAT('test',FLOOR( 1 + RAND() * (100 - 1)))  Indicates that the application name is ：test start +1-99 The random number , The main purpose is to explain how to insert random data

insert In the sentence NOW() Indicates to get the current time

CALL insertdata(); Execute stored procedures

This is a simple example , But it has clearly explained the ideas and specific methods of creating data , I believe you can combine your business to write more complex sql Statement and then insert operation ！ Originality is not easy. , If the article helps you, you are welcome to like and forward ！