MySQL _ MySQL

MySQL inserts multiple pieces of test data into a table in MySQL database. sometimes, to create a test table, you need to insert many pieces of data into a table. you can use the following stored procedure:

First, create a test table:

Mysql>Create table t_1 (id serial, name char (5 ));
Query OK, 0 rows affected (0.26 sec)

Then create a stored procedure:

Delimiter $
Set autocommit = 0 $

Create procedure test01 ()
Begin
Declare v_cnt decimal (10) default 0;
Dd: loop
Insert into t_1 values (null, 'aaaaa'), (null, 'aaaaa'), (null, 'aaaaa ');
Commit;
Set v_cnt = v_cnt + 10;
If v_cnt = 10000 then leave dd;
End if;
End loop dd;
End; $

Delimiter;

Call the stored procedure to insert data into the table:

Mysql>Select count (*) from t_1;
+ ---------- +
| Count (*) |
+ ---------- +
| 0 |
+ ---------- +
1 row in set (0.00 sec)


Mysql>
Mysql>
Mysql>Call test01;
Query OK, 0 rows affected (21.25 sec)


Mysql>Select count (*) from t_1;
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 10000 |
+ ---------- +
1 row in set (0.01 sec)

Executing the stored procedure again inserts more data:

Mysql>Call test01;
Query OK, 0 rows affected (21.70 sec)


Mysql>Select count (*) from t_1;
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 20000 |
+ ---------- +
1 row in set (0.01 sec)

Note: the above test table structure, data insertion, and number of table rows can be modified as needed to generate the required data test environment.

For the MySQL stored procedure, run the following commands:

Select name from mysql. proc where db = 'test ';
Select routine_name from information_schema.routines where routine_schema = 'test ';
Show procedure status where db = 'test ';
Show create procedure test. test01;

Command for deleting a stored procedure:

Drop procedure test01;

Query stored procedures in a database:
Method 1: (direct query)
Select 'specpacific _ name' from mysql. proc where db = 'Your _ db_name 'and 'type' = 'Procedure'
Method 2: (view all stored procedures and content in the database)
Show procedure status;
Method 3: (view the list of stored procedures in the current database)
Select specific_name from mysql. proc;
Method 4: (view the specific content of a stored procedure)
Select body from mysql. proc where specific_name = 'Your _ proc_name ';
View the code for creating a stored procedure or function:
Show create procedure your_proc_name;
Show create function your_func_name;
Delete stored procedure:
Drop procedure your_proc_name;

However, if the Test table contains a primary key, the above stored procedure may be faulty. you can use the following stored procedure:

Create a Test table:

Create table t1 (id1 INT, a1 INT, b1 INT, primary key (id1 ));
Create table t2 (id2 INT, a2 INT, b2 INT );
Create table t3 (id3 int nuique, a3 INT, b3 INT );
Create table t4 (id4 INT, a4 INT, b4 INT );
Create table t5 (id5 int unique, a5 INT, b5 INT );

Create a stored procedure for data insertion:

Create a while... end while stored procedure (check the execution conditions before execution ):
DELIMITER //
Create procedure proc4 ()
Begin
Declare var int;
Set var = 0;
While var <6 do
Insert into t1 values (var, var, var );
Set var = var + 1;
End while;
End;
//
DELIMITER;






Create a repeat... end repeat stored procedure (check the execution conditions after execution ):
DELIMITER //
Create procedure proc01 ()
Begin
Declare v int;
Set v = 0;
Repeat
Insert into t1 values (v, v, v );
Set v = v + 1;
Until v> = 100
End repeat;
End;
//
DELIMITER;