批量插入数据脚本:
建表
- 员工表
- 部门表
设置参数log_bin_trust_function_creators
show variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators=1;
创建函数,保证每条数据都不一样:
- 随机产生字符串(创建函数)
DELIMITER $$
CREATE FUNCTION rand_string (n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE char_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,sunstring(char_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 a INT DEFAULT 0;
SET a=FLOOR(100+RAND()*10);
RETURN a;
END $$
*删除:
drop function rand_num;
创建存储过程
- 创建往emp表中插入数据的存储过程
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`,`mar`,`time`,`sal`,`com`,`deptno`) VALUES((START+i),rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());
UNTIL i =max_num
END REPEAT;
COMMIT;
END $$
- 创建往dept表插入数据的存储过程
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 $$
调用存储过程
- 插入10个部门:
DELIMITER ;
CALL insert_dept(100,10);
- 分批次插入1000万数据到员工表,每次50万
DELIMITER ;
CALL insert_emp(100001,500000);