Implement loop insert in MySQL

MySQL does not support directly writing SQL statements to implement the Circular insert function. You can use other languages to manipulate MySql or use stored procedures to implement this function.

1. Implementation of Stored Procedures
A. Create Table song)
------ Success ----------------------------------------------------------------------------------------------
Create table 'song '(
'Id' int (11) not null AUTO_INCREMENT COMMENT 'autoincreament element ',
'Name' text not null,
'Datetime' timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
'Rank 'int (11) not null,
Primary key ('id ')
) ENGINE = MyISAM AUTO_INCREMENT = 8102001 default charset = gbk

B. Define the Stored Procedure
DELIMITER $
Drop procedure if exists 'test'. 'SP _ insert_batch' $
Create definer = 'root' @ 'localhost' PROCEDURE 'SP _ insert_batch '(IN number int (11 ))
BEGIN
Declare I int (11 );
Set I = 1;
-- Such as 1-2000,2000-4000 ,....
WHILE I <= number DO
If mod (I, 2000) = 1 then
Set @ sqltext = concat ('(''', concat ('T', I), ''', ''', now (),''',', ceil (10 * rand ()),')');
Elseif mod (I, 2000) = 0 then
Set @ sqltext = concat (@ sqltext, ', (''', concat ('T', I), ''', ''', now (), ''', ', ceil (10 * rand ()),')');
Set @ sqltext = concat ('insert into song (name, datetime, rank) values', @ sqltext );
Prepare stmt from @ sqltext;
Execute stmt;
Deallocate prepare stmt;
Set @ sqltext = '';
Else
Set @ sqltext = concat (@ sqltext, ', (''', concat ('T', I), ''', ''', now (), ''', ', ceil (10 * rand ()),')');
End if;
Set I = I + 1;
End while;
-- Process when number is not be moded by 2000
-- Such as 2001,4002, 15200 ,...
If @ sqltext <> ''then
Set @ sqltext = concat ('insert into song (name, datetime, rank) values', @ sqltext );
Prepare stmt from @ sqltext;
Execute stmt;
Deallocate prepare stmt;
Set @ sqltext = '';
End if;
END $
DELIMITER;
C. Call the Stored Procedure
Call sp_insert_batch (100); -- the parameter is the number of rows to be inserted.

2. Use other languages to control MySql implementation (using shell as an example)
For (I = 1; I <= 1000; I ++ ));
Do 'mysql test-e "insert into t1 (idx, pw) value ($ I, md5 ($ I ));"';
Done