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