mysql批量插入数据的存储过程

mysql批量插入数据的存储过程
<Date: 2008-01-17> <Author: admin> <Category: mysql> 查看评论
mysql批量插入数据的存储过程

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 ;


附表结构。


/*DDL Information For - test.song*/
-----------------------------------


Table Create Table
------ ----------------------------------------------------------------------------------------
song 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