Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.