Mysql dynamically generates test data statements to facilitate test data.
Mysql dynamically generates test data statements to facilitate test data.
I. Problems
To generate two types of data:
Class A: 01 02 03 of two digits... 09 10 11... 19 20 21... 98 99
Another Class B: The third-digit 100 101 102... 110 111 112... 998 999
Ii. Solution
1. Create a table
The Code is as follows:
Create table 'test'. 'ta '(
'A' varchar (45) NOT NULL
) ENGINE = InnoDB default charset = utf8;
2. Create a stored procedure
The Code is as follows:
DELIMITER $
Drop procedure if exists 'test'. 'proc _ tp '$
Create definer = 'root' @ 'localhost' PROCEDURE 'proc _ tp '(in prex int, in max int)
Begin
Declare I INT DEFAULT 0;
Declare s varchar (500 );
WHILE (I <10 and prex Select concat (prex, I) into s;
Insert into ta (a) values (s );
Set I = I + 1;
If (I = 10 and prex Set prex = prex + 1;
Set I = 0;
End if;
End while;
End $
DELIMITER;
3. respectively call and execute the Stored Procedure
CALL proc_tp () to create Class A Data
CALL proc_tp (10,100) to create Class B data
4. query results
SELECT * FROM ta t order by cast (a as signed) asc;
