mysql实现批量导入数据，可供测试

 新建表格

 

 

 

 

 

 

 

 新建函数用来返回随机字符串

DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFHIJKLMNOPQRSTUVWXYZ' ;
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 $$

 

　　创建批量插入存储emp表

DELIMITER $$
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
#set autocommit =0把autocommit设置成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 $$

　　创建批量插入存储dept表

BEGIN
DECLARE i INT DEFAULT 0;
#set autocommit =0把autocommit设置成0
SET autocommit= 0;
REPEAT
SET i=i+1;
INSERT INTO dept(deptno,dname,loc) VALUES ( (START+i),rand_string(10) ,rand_strin(8));
UNTIL i = max_num
END REPEAT;
COMMIT;
END

　　最后调用存储过程完成批量插入

DELIMITER ;
CALL insert_dept(100,500000)