Mysql inserts data in batches using stored procedures and mysql stored procedures

Mysql inserts data in batches using stored procedures and mysql stored procedures

Recently, we need to test the performance of adding, deleting, modifying, and querying more than data records in a single mysql table. Because there is no ready-made data, we construct it by ourselves. This article is just an example and a brief introduction.

First of all, create a table:

CREATE TABLE `fortest` (  `ID` INT(30) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  `IP` VARCHAR(32) NOT NULL,  `OID` VARCHAR(15) DEFAULT NULL)

Second, build a stored procedure:

DELIMITER $ USE 'name of the database in which the table is inserted '$ drop procedure if exists 'autoinsert' $ create definer = 'root' @ '192. 168.137.10 'Procedure 'autoinsert' (IN IP_NUM INT, IN OID_NUM INT) begin declare iIP int default 0; DECLARE iOID int default 0; WHILE (iIP <IP_NUM) do set iOID = 0; WHILE (iOID <OID_NUM) do set @ mySql = CONCAT ("insert into fortest (IP, OID) VALUES (CONCAT (ROUND (RAND () * 255) + 1 ), '. ', (ROUND (RAND () * 255) + 1 ),'. ', (ROUND (RAND () * 255) + 1 ),'. ', (ROUND (RAND () * 255) + 1), ROUND (RAND () * 100) + 1); "); PREPARE stmt FROM @ mySql; EXECUTE stmt; deallocate prepare stmt; SET iIP = iIP + 1; end while; SET iPC = iPC + 1; end while; END $ DELIMITER;

The preceding Stored Procedure specifies two input parameters: IP_NUM OID_NUM. The two Parameters specify the number of machines and the number of OID IDs of each machine.

Then you can call the stored procedure:

call autoinsert 1000 50

This means that there are 100 machines, each with 50 parameters.

In this way, we have built 50000 pieces of data. If we want to reach million pieces of data based on the above stored procedures, it will take some time. You can use the following method to further increase the speed:

First, create a table with the same table structure:

CREATE TABLE fortest_2 LIKE fortest;

Insert 5 million data records based on the fortest table.

INSERT INTOfortest_2(IP,OID) SELECT IP,ROUND(RAND() * 100) + 1) FROM fortest;

The execution speed of the preceding statement is extremely fast, and data records are inserted in an instant. You can write a script for execution:

#!/bin/bashi=1;MAX_INSERT_ROW_COUNT=$1;j=0;while [ $i -le $MAX_INSERT_ROW_COUNT ]do    time mysql -h192.168.137.1 -uroot -p123456 fortest -e "INSERT INTOfortest_2(IP,OID) SELECT IP,ROUND(RAND() * 100) + 1) FROM fortest;"    echo "INSERT $i "        i=$(($i+1))#    sleep 0.05doneexit 0

 

 

How to use MySQL to insert data in batches

Using the stored procedure, write a loop to show you what I wrote before.
Begin
Declare I int;
Declare B int;
Declare c int;
Set @ I = 2;
Set @ B = 6;
Set @ c = 0;
Set @ stmt = concat ('insert into t_j_goods_name (id, 'code', 'name ')
Values (?,?, (Select distinct 'A' from sheet1 limit ?, 1 ))');
While @ I <442 do
Prepare s1 from @ stmt;

Execute s1 using @ B, @ I, @ c;

Deallocate prepare s1;
Set @ I = @ I + 1, @ B = @ B + 1, @ c = @ c + 1;

End while;
End

Batch Data insertion in Stored Procedures

Create proc pc_Text
@ Number_star int,
@ Number_end int
As
Declare @ int
Set @ int = @ number_end-@ number_star + 1
While (@ int> 0)
Begin
Select @ int = count (1) from Text where aNumber1 = @ number_star
If (@ int = 1) -- insert if there is a change
Begin
Set @ number_star = @ number_star + 1
Set @ int = @ number_end-@ number_star + 1

End
Else
Begin
Insert into Text value (@ number_star ,...)
Set @ number_star = @ number_star + 1
Set @ int = @ number_end-@ number_star + 1

End
End