mysql实现批量数据导入_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

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)

原文链接：https://www.cnblogs.com/420ITboy/p/12881783.html

本站声明:网站内容来源于网络,若有侵权,请联系我们,我们将及时处理。