MYSQLg高级------批量插入百万级数据量

默语

发布于 2024-11-20 09:52:58

1820

文章被收录于专栏：JAVAJAVA

为了更好的展示 先建立一张表：作为测试使用

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=utf8;

CREATE TABLE emp(
    id int unsigned primary key auto_increment,
    empno mediumint unsigned not null default 0,
    ename varchar(20) not null default "",
    job varchar(9) not null default "",
    mgr mediumint unsigned not null default 0,
    hiredate date not null,
    sal decimal(7,2) not null,
    comm decimal(7,2) not null,
    deptno mediumint unsigned not null default 0
)ENGINE=INNODB DEFAULT CHARSET=utf8;

开启了可以创建存储函数的权限，主要是这个功能

设置参数log_bin_trust_function_creators

# 在mysql中设置 
# log_bin_trust_function_creators 默认是关闭的 需要手动开启
mysql> SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+
1 row in set (0.00 sec)

mysql> SET GLOBAL log_bin_trust_function_creators=1;
Query OK, 0 rows affected (0.00 sec)

上述修改方式MySQL重启后会失败，在my.cnf配置文件下修改永久有效。

windows下my.ini[mysqld]加上log_bin_trust_function_creators=1
linux下/etc/my.cnf 下my.cnf[mysqld]加上log_bin_trust_function_creators=1

[mysqld]
log_bin_trust_function_creators=1

上面的作用就是为了下的 $$ （函数；的表达式）更好的执行；重启或者重新连接即可恢复默认

创建函数，保证每条数据都不同

随机产生字符串

#随机产生字符串
#create function  建函数
#returns 返回值
#i int default 0;  i int 类型从0开始
delimiter $$ # 两个 $$ 表示结束函数中使用（可理解为正常使用 )
create function rand_string(n int) returns varchar(255)
begin
    declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyz';
    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(2)

;，要以

结尾

随机产生部门编号

delimiter $$
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()

，要以

结尾

创建存储过程，创建往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,mgr,hiredate,sal,comm,deptno) values((start+i),rand_string(6),'salesman',0001,curdate(),2000,400,rand_num());
        until i=max_num
        end repeat;
    commit;
end $$

重点语句分析

新增语句前面的emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) 都是正常的新增语句；

values((start+i),rand_string(6),‘salesman’,0001,curdate(),2000,400,rand_num());

(start+i)：

rand_string(6)：（6）长度最大为6

salesman：默认值；所有的值都是这个不变的 0001：所有的值都是1 也是不变的

curdate()：当前得到时间应该是函数得一种写法；（具体的不了解；抱歉）

2000：都是默认值 400：都是默认值

rand_num()：

上面就是他的一个完整的解释；不足之处请多多指教；

insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values((start+i),rand_string(6),'salesman',0001,curdate(),2000,400,rand_num());

创建往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 $$

调用存储过程 往dept表中插入数据

DELIMITER ;
CALL insert_dept(100, 10);

往emp表中插入50万数据

DELIMITER ;
CALL insert_emp(100001, 500000);

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

就好像我们执行接口一样为接口传入需要的参数进行执行；他们的2给参数分别是：从多少条开始，执行多少条；

整体过程梳理：（本人就是这样执行的）

#创建部门表 dept
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=utf8;

#创建员工表 emp
CREATE TABLE (
    id int unsigned primary key auto_increment,
    empno mediumint unsigned not null default 0,
    ename varchar(20) not null default "",
    job varchar(9) not null default "",
    mgr mediumint unsigned not null default 0,
    hiredate date not null,
    sal decimal(7,2) not null,
    comm decimal(7,2) not null,
    deptno mediumint unsigned not null default 0
)ENGINE=INNODB DEFAULT CHARSET=utf8;

#查看存储函数的权限
SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
#开启存储函数的权限（临时重启效果消失）
SET GLOBAL log_bin_trust_function_creators=1;

#查看当前
select now() from dual;


#随机产生字符串
#create function  建函数
#returns 返回值
#i int default 0;  i int 类型从0开始
delimiter $$ # 两个 $$ 表示结束函数中使用（可理解为正常使用 )
create function rand_string(n int) returns varchar(255)
begin
    declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyz';
    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 $$


#随机产生部门编号
delimiter $$
create function rand_num() returns int(5)
begin
    declare i int default 0;
    set i=floor(100+rand()*10);
    return i;
end $$


#创建存储过程，创建往emp表中插入数据的存储过程
#insert_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,mgr,hiredate,sal,comm,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 $$

#从100开始插入10条数据 dept
DELIMITER ;
CALL insert_dept(100, 10);

SELECT * from dept

#往emp表中插入50万数据
DELIMITER ;
CALL insert_emp(100001, 500000);
#查看ok 成功了
SELECT * from emp

下面贴下部分图片；