想写一个存储过程生成10万条记录,不知道写对没。call handlerdemo()没反应CREATE TABLE `db_test` (
`ID` int(11) NOT NULL auto_increment,
`Title` varchar(50) default NULL,
`Description` text,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=gb2312;CREATE PROCEDURE `handlerdemo`()
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
begin
DECLARE v1 INT DEFAULT 0;
DECLARE vR INT DEFAULT 0;
DECLARE vTitle char(50) DEFAULT '';
DECLARE vDescription char(50) DEFAULT '';
WHILE @v1 < 100000 DO
set vR= ROUND(RAND()*10);
if vR>5 then
set vTitle= CONCAT('中国', vR);
End if;
set vR= ROUND(RAND()*10);
if vR>5 then
set vTitle= vTitle + CONCAT('历史', vR);
ENd if;
set vR= ROUND(RAND()*10);
if vR>5 then
set vTitle= vTitle + CONCAT('今天', vR);
ENd if;
set vR= ROUND(RAND()*10);
if vR>5 then
set vTitle= vTitle + CONCAT('事件', vR);
ENd if; set vR= ROUND(RAND()*10);
if vR>5 then
set vDescription= CONCAT('中国', vR);
ENd if;
select vR= ROUND(RAND()*10);
if vR>5 then
set vDescription= vDescription + CONCAT('历史', vR);
ENd if;
set vR= ROUND(RAND()*10);
if vR>5 then
set vDescription= vDescription + CONCAT('今天', vR);
ENd if;
set vR= ROUND(RAND()*10);
if vR>5 then
set vDescription= vDescription + CONCAT('事件', vR);
ENd if;
insert into DB_test set Title=vTitle ,Description=vDescription;
SET v1 = v1 + 1;
END WHILE;
end;
`ID` int(11) NOT NULL auto_increment,
`Title` varchar(50) default NULL,
`Description` text,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=gb2312;CREATE PROCEDURE `handlerdemo`()
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
begin
DECLARE v1 INT DEFAULT 0;
DECLARE vR INT DEFAULT 0;
DECLARE vTitle char(50) DEFAULT '';
DECLARE vDescription char(50) DEFAULT '';
WHILE @v1 < 100000 DO
set vR= ROUND(RAND()*10);
if vR>5 then
set vTitle= CONCAT('中国', vR);
End if;
set vR= ROUND(RAND()*10);
if vR>5 then
set vTitle= vTitle + CONCAT('历史', vR);
ENd if;
set vR= ROUND(RAND()*10);
if vR>5 then
set vTitle= vTitle + CONCAT('今天', vR);
ENd if;
set vR= ROUND(RAND()*10);
if vR>5 then
set vTitle= vTitle + CONCAT('事件', vR);
ENd if; set vR= ROUND(RAND()*10);
if vR>5 then
set vDescription= CONCAT('中国', vR);
ENd if;
select vR= ROUND(RAND()*10);
if vR>5 then
set vDescription= vDescription + CONCAT('历史', vR);
ENd if;
set vR= ROUND(RAND()*10);
if vR>5 then
set vDescription= vDescription + CONCAT('今天', vR);
ENd if;
set vR= ROUND(RAND()*10);
if vR>5 then
set vDescription= vDescription + CONCAT('事件', vR);
ENd if;
insert into DB_test set Title=vTitle ,Description=vDescription;
SET v1 = v1 + 1;
END WHILE;
end;
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
begin
DECLARE v1 INT DEFAULT 0;
DECLARE vR INT DEFAULT 0;
DECLARE vTitle char(50) DEFAULT '';
DECLARE vDescription char(50) DEFAULT '';
WHILE v1 < 100000 DO
set vR= ROUND(RAND()*10);
if vR>5 then
set vTitle= CONCAT('中国', vR);
End if;
set vR= ROUND(RAND()*10);
if vR>5 then
set vTitle= CONCAT(vTitle,'历史', vR);
ENd if;
set vR= ROUND(RAND()*10);
if vR>5 then
set vTitle= CONCAT(vTitle,'今天', vR);
ENd if;
set vR= ROUND(RAND()*10);
if vR>5 then
set vTitle= CONCAT(vTitle,'事件', vR);
ENd if; set vR= ROUND(RAND()*10);
if vR>5 then
set vDescription= CONCAT('中国', vR);
ENd if;
select vR= ROUND(RAND()*10);
if vR>5 then
set vDescription= CONCAT(vDescription,'历史', vR);
ENd if;
set vR= ROUND(RAND()*10);
if vR>5 then
set vDescription= CONCAT(vDescription,'今天', vR);
ENd if;
set vR= ROUND(RAND()*10);
if vR>5 then
set vDescription= CONCAT(vDescription,'事件', vR);
ENd if;
insert into DB_test set Title=vTitle ,Description=vDescription;
SET v1 = v1 + 1;
END WHILE;
end;
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
begin
DECLARE v1 INT DEFAULT 0;
DECLARE vR INT DEFAULT 0;
DECLARE vTitle char(50) DEFAULT '';
DECLARE vDescription char(50) DEFAULT '';
WHILE @v1 < 100000 DO
set vTitle= vTitle + CONCAT('历史', vR); .....
set vDescription= vDescription + CONCAT('历史', vR);
SET v1 = v1 + 1;
END WHILE;
end;注意上面红色部分:
1、你循环的变量v_i前后不对应,要不就全部用@v1,要不就全部用v1。
2、mysql里面字符串相加不能用"+",而要用函数concat。
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
begin
DECLARE v1 INT DEFAULT 0;
DECLARE vR INT DEFAULT 0;
DECLARE vTitle varchar(50) DEFAULT '';
DECLARE vDescription varchar(50) DEFAULT '';
set v1 =0;
WHILE v1 < 100000 DO
set vTitle= ''; set vR= ROUND(RAND()*10);
if vR>5 then
set vTitle= CONCAT(vTitle ,'中国');
End if;
set vTitle= CONCAT(vTitle, vR); set vR= ROUND(RAND()*10);
if vR>5 then
set vTitle= CONCAT(vTitle ,'历史');
ENd if;
set vTitle= CONCAT(vTitle, vR); set vR= ROUND(RAND()*10);
if vR>5 then
set vTitle= CONCAT(vTitle,'今天');
ENd if;
set vTitle= CONCAT(vTitle, vR); set vR= ROUND(RAND()*10);
if vR>5 then
set vTitle= CONCAT(vTitle ,'事件');
ENd if;
set vTitle= CONCAT(vTitle, vR); set vDescription= ''; set vR= ROUND(RAND()*10);
if vR>5 then
set vDescription= CONCAT(vDescription ,'中国');
End if;
set vDescription= CONCAT(vDescription, vR); set vR= ROUND(RAND()*10);
if vR>5 then
set vDescription= CONCAT(vDescription ,'历史');
ENd if;
set vDescription= CONCAT(vDescription, vR); set vR= ROUND(RAND()*10);
if vR>5 then
set vDescription= CONCAT(vDescription,'今天');
ENd if;
set vDescription= CONCAT(vDescription, vR); set vR= ROUND(RAND()*10);
if vR>5 then
set vDescription= CONCAT(vDescription ,'事件');
ENd if;
set vDescription= CONCAT(vDescription, vR); insert into tb_test set Title=vTitle ,Description=vDescription;
SET v1 = v1 + 1;
END WHILE;
end;