mysql循环insert_MySQL实现循环插入功能

MySQL 不支持直接写SQL 语句实现循环插入功能. 想要实现该功能的方法有：用其他语言操控MySql或者用存储过程来实现两种。

1、存储过程实现

A、表结构 (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

B、定义存储过程

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

2、用其他语言操控MySql实现(以shell为例)

for ((i=1;i<=1000;i++));

do `mysql test -e "insert into t1(idx,pw) value($i,md5($i));"`;

done