Stored procedure + function to create data myql
Insert N records into the identity table, N=P*Q
P: the number of records inserted per insert statement, determined by the number of temporary tables
Q: number of loops, input parameters of the stored procedure
-- identity information table
Drop table if exists t_identity_info
Create table t_identity_info (
Id int (11) not null auto_increment comment 'primary key id'
Real_name varchar (20) default null comment 'name'
Id_card varchar (18) not null comment'× ×'
Create_time varchar (19) not null comment 'creation time'
Primary key (id)
Key index_id_card (id_card)
) engine=innodb auto_increment=1 default charset=utf8 comment=' user information table'
-- Delete
Drop function if EXISTS rand_xing_ming
-- create function
Create function rand_xing_ming () returns varchar (20)
Begin
Declare xing_string varchar (1000) default 'Zhao Qian Sun Li Zhou Wu Zheng Wang Feng Chen'
Declare ming_string varchar (1000) default 'Heaven, Earth, Yellow, Universe, Sun and Moon'
Declare xing_ming varchar (20) default''
Declare xing_index int
Declare ming_index int
Declare temp_xing varchar (10)
Declare temp_ming varchar (10)
Set xing_index = round (rand () 10000) mod (char_length (xing_string)-1)
Set ming_index = (round (rand () 10000)) mod (char_length (ming_string)-1)
Set temp_xing = substring (xing_string,xing_index,1)
Set temp_ming = substring (ming_string,ming_index,1)
Set xing_ming = concat (temp_xing,temp_ming)
Return xing_ming
End
-- testing
Select rand_xing_ming ()
Temporary table, used to control the number of insert entries
Drop table if exists t_temp
Create table t_temp (
Id int (11) not null auto_increment comment 'primary key id'
Primary key (id)
) engine=innodb auto_increment=1 default charset=utf8 comment=' temporary Table'
-- insert 100 pieces of data
Insert into t_temp values
()
()
()
()
()
()
()
()
()
(),
-- stored procedure
Drop procedure if exists insert_data
Delimiter
Create procedure insert_data (IN data_count int)
Begin
Declare i int
Set I = 0
While I < data_count do
Set I = I + 1
Insert into t_identity_info (real_name,id_card,create_time)
Select rand_xing_ming (), 'abc',now () from t_temp
End while
End
DELIMITER
-- call stored procedure to insert 10W records
Set autocommit= 0
Call insert_data (1000)
Commit
-- Verification
Select count (1) from t_identity_info
