MySQL存储过程——批量插入数据

DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
  DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
  DECLARE return_str VARCHAR(255) DEFAULT '';
  DECLARE i INT DEFAULT 0;  
  WHILE i<n DO
  SET return_str=CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
  SET i=i+1;
END WHILE;
RETURN return_str;
END $$

DELIMITER $$
  CREATE FUNCTION rand_num() RETURNS INT(5)
  BEGIN
    DECLARE i INT DEFAULT 0;
    SET i=FLOOR(100+RAND()*10);
    RETURN i;
END $$

DELIMITER $$
  CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
  BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit=0;
    REPEAT
  SET i=i+1;
  INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES((START+i), rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());
  UNTIL i=max_num END REPEAT;
  COMMIT;
END $$

DELIMITER $$
  CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))
  BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit=0;
    REPEAT
  SET i=i+1;
  INSERT INTO dept(deptno,dname,loc) VALUES((START+i), rand_string(10),rand_string(8));
  UNTIL i=max_num END REPEAT;
  COMMIT;
END $$

DELIMITER ;
CALL insert_emp(100001,10000000);# 插入一千万条数据