批量自动插入数据

1.1、dept表

CREATE TABLE `dept` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `deptno` int(11) NOT NULL DEFAULT '0',
  `dname` varchar(20) DEFAULT NULL,
  `loc` varchar(13) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4;

1.2、EMP表

CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `empno` int(11) NOT NULL DEFAULT '0',
  `ename` varchar(20) DEFAULT NULL,
  `job` varchar(20) DEFAULT NULL,
  `mgr` int(11) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` decimal(7,2) DEFAULT NULL,
  `comm` decimal(7,2) DEFAULT NULL,
  `deptno` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10000011 DEFAULT CHARSET=utf8mb4;

1.3、MySQL配置文件修改，否则会报错

vim /etc/my.cnf

[mysqld]下新加：log_bin_trust_function_creators=1

1.4、两个函数

-- 随机产生字符串
CREATE FUNCTION rand_string(n INT)
RETURNS VARCHAR(255)
BEGIN
    DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    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()*62 ),1));
        SET i = i +1;
    END WHILE;
    RETURN return_str;
END

调用：select rand_string(10);

-- 随机产生部门编号
CREATE FUNCTION rand_num()
returns int(5)
begin
    declare i int default 0;
    set i = floor(100+rand()*10);
    return i;
END 

1.5、存储过程插入数据

插入emp表

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',1,CURDATE(),2000.0,400.0,rand_num ());
        UNTIL i = max_num
    END REPEAT;
    COMMIT;
END

插入dept表

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

1.6、调用存储过程，插入数据

call insert_dept(100,10);
call insert_emp(100001,500000)