mysql 循环插入 插入sql语句 time_MySQL实现循环插入功能

B、定义存储过程

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`sp_insert_batch`$$

CREATEDEFINER=`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 sp_insert_batch (100); --参数为需要插入的行数