存储过程造大量数据

• 存储过程方法  while 和repeat

-- WHILE

Delimiter//
USE ts(数据库名称)//
DROP PROCEDURE IF EXISTS addstudents//
CREATE PROCEDURE addstudents(Num INT)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=Num DO
INSERT INTO students (name,sex,age,class,Addr)VALUES (CONCAT('test',i),'女','28','三年二班','北京市海淀区');
SET i = i+1;
END WHILE;
END//
Delimiter;

CALL addstudents(500);  --500条数据

 

-- REPEAT
delimiter //
DROP PROCEDURE if EXISTS insert_score//
CREATE PROCEDURE insert_score(num int)
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE sid INT DEFAULT 1;
DECLARE cid INT DEFAULT 1;
DECLARE grade INT DEFAULT 1;
SET j=0;
REPEAT
INSERT INTO score(stu_id,C_id,Grade) VALUES(sid,cid,grade);
set i=i+1;
SELECT FLOOR(1+(RAND()*100))INTO cid;  -- rand()产生0-1之间的随机数，向下取值所以+1
SELECT FLOOR(1+(RAND()*100))INTO grade;
SELECT FLOOR(1+(RAND()*500))INTO sid;
UNTIL i>num
END REPEAT;
END//
delimiter ;

 

转载于:https://www.cnblogs.com/njq666/p/10838266.html