MySQL批量插入数据脚本

1、建表

部门表

1 CREATE TABLE dept(
2 id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
3 deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
4 dname VARCHAR(20) NOT NULL DEFAULT "",
5 loc VARCHAR(13) NOT NULL DEFAULT ""
6 )ENGINE INNODB CHARSET utf8;

员工表

 1 CREATE TABLE emp(
 2 id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
 3 empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*编号*/
 4 ename VARCHAR(20) NOT NULL DEFAULT "",/*名字*/
 5 job VARCHAR(9) NOT NULL DEFAULT "", /*工作*/
 6 mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*上级编号*/
 7 hiredate DATE NOT NULL, /*入职时间*/
 8 sal DECIMAL(7,2) NOT NULL, /*薪水*/
 9 comm DECIMAL(7,2) NOT NULL, /*红利*/
10 deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
11 )ENGINE INNODB CHARSET utf8;

2、设置参数 log_bin_trust_function_creators

设置前

设置后

3、创建函数

创建随机获取字符串的函数

 1 DELIMITER $$
 2 CREATE FUNCTION rand_str(n INT) RETURNS VARCHAR(255)
 3 BEGIN
 4  DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
 5  DECLARE return_str VARCHAR(255) DEFAULT '';
 6  DECLARE i INT DEFAULT 0;
 7  WHILE i < n DO
 8   SET return_str = CONCAT(return_str, SUBSTRING(chars_str, FLOOR(1 + RAND() * 52), 1));
 9   SET i = i + 1;
10  END WHILE;
11  RETURN return_str;
12 END $$

创建随机获取数字的函数

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

4、创建存储过程

创建往emp表中插入数据的存储过程

 1 DELIMITER $$
 2 CREATE PROCEDURE insert_emp(IN START INT(10), IN max_num INT(10))
 3 BEGIN
 4  DECLARE i INT DEFAULT 0;
 5  #set autocommit = 0 把autocommit 设置为0
 6  SET autocommit = 0;
 7  REPEAT
 8  SET i = i + 1;
 9  INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES ((START + i), rand_str(6), 'SALESMAN', 0001, CURDATE(), 2000, 400, rand_num());
10  UNTIL i = max_num
11  END REPEAT;
12  COMMIT;
13 END $$

创建往dept表中插入数据的存储过程

 1 DELIMITER $$
 2 CREATE PROCEDURE insert_dept(IN START INT(10), IN max_num INT(10))
 3 BEGIN
 4  DECLARE i INT DEFAULT 0;
 5  #set autocommit = 0 把autocommit 设置为0
 6  SET autocommit = 0;
 7  REPEAT
 8  SET i = i + 1;
 9  INSERT INTO dept (deptno, dname, loc) VALUES ((START + i), rand_str(10), rand_str(8));
10  UNTIL i = max_num
11  END REPEAT;
12  COMMIT;
13 END $$