Mysql使用存储过程与函数进行批量插入数据

批量插入

第一步表格

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
                        `id` int(11) NOT NULL AUTO_INCREMENT,
                        `deptno` int(11) NOT NULL,
                        `dname` varchar(50) NOT NULL,
                        `loc` varchar(50) NOT NULL,
                        PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=gbk;

-- ----------------------------
-- Table structure for emp
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
                       `id` int(11) NOT NULL AUTO_INCREMENT,
                       `empno` int(11) DEFAULT NULL,
                       `ename` varchar(255) DEFAULT NULL,
                       `job` varchar(255) DEFAULT NULL,
                       `mgr` varchar(255) DEFAULT NULL,
                       `hiredate` datetime DEFAULT NULL,
                       `sal` varchar(255) DEFAULT NULL,
                       `comm` varchar(255) DEFAULT NULL,
                       `deptno` int(11) DEFAULT NULL,
                       PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8;

 第二部开关

 

第三部创建函数

create function rand_string(n int) returns varchar(255)
begin
    declare chars_str varchar(100) default 'abcdefghigklmnopqrstuvwxyz';
    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;
select rand_string(5);
create function rand_num() returns int(5)
begin
    declare i int default 0;
    set i = floor(100 + rand() * 10);
    return i;
end;
select rand_num();

 第四部存储过程

create procedure insert_emp(in start int(10), in max int(10))
begin
    declare i int default 0;
    set autocommit = 0;
    repeat
        set i = i + 1;
        insert into emp(id,empno, ename, job, mgr, hiredate, sal, comm, deptno)
        values (i,(start + i), rand_string(8), 'saleman', 0001, '2020-12-12', 12, 'd', rand_num());
    until i = max
        end repeat;
    #记录一次性提交
    commit;
end;
create procedure insert_dept(in start int(10), in max 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
        end repeat;
    #记录一次性提交
    commit;
end;

第五步调用

call insert_dept(10000,10000);
call insert_emp(10000,10000);

第六部结果