Mysql批量插入数据

绝地灬酷狼 2022-05-17 02:46 273阅读 0赞

#新建库
    create database bigdata;
    use bigdata;
    #新建部门表
    create table dept(
    id int unsigned primary key auto_increment,
    deptno mediumint unsigned not null default 0,
    dname varchar(20) not null default '',
    loc varchar(13) not null default ''
    )engine=innodb default charset=gbk;
    #新建员工表
    create table emp(
    id int unsigned primary key auto_increment,
    empno mediumint unsigned not null default 0 comment '编号',
    ename varchar(20) not null default '' comment '名字',
    job varchar(9) not null default '' comment '工作',
    mgr mediumint unsigned not null default 0 comment '上级编号',
    hiredate date not null comment '入职时间',
    sal decimal(7,2) not null comment '薪水',
    comm decimal(7,2) not null comment '红利',
    deptno mediumint unsigned not null default 0 comment '部门编号'
    )engine=innodb default charset=gbk;
    #由于开启过慢查询日志,开启bin-log需要指定一个参数
    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 chars_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,substring(chars_str,floor(1+rand()*52),1));
    set i=i+1;
    end while;
    return return_str;
    end $$
    #删除 drop function rand_string;
    #创建函数,产生随机部门编号
    delimiter $$
    create function rand_num() returns int(5)
    begin
    declare i int default 0;
    set i=floor(100+rand()*10);
    return i;
    end $$
    #删除 drop function rand_num;
    #创建存储过程,往部门表插入数据
    delimiter $$
    create procedure insert_emp(in start_num 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_num+i),rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
    until i=max_num
    end repeat;
    commit;
    end $$
    #创建存储过程,往员工表插入数据
    delimiter $$
    create procedure insert_dept(in start_num 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 dept(deptno,dname,loc) values((start_num+i),rand_string(10),rand_string(8));
    until i=max_num
    end repeat;
    commit;
    end $$
    #调用存储过程,批量生成数据.员工表会用到部门表deptno,所以先生成部门表的数据
    delimiter ;
    call insert_dept(100,10); #从100开始,生成10条
    select *from dept;
    #调用存储过程,生成员工表数据
    call insert_emp(100000,10000000); #从100000开始,生成一千万条 (建议分10次,一次插入一百万)
    select *from emp;