在Mysql中插入百万级别数据的方法

首先,建立部门表和员工表：

部门表:

 

[plain]  view plain  copy

 

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 DEFAULT CHARSET=GBK  

员工表:

[plain]  view plain  copy

 

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 DEFAULT CHARSET=GBK  

开启二进制日志:

 

通过

 

[plain]  view plain  copy

 

1. show variables like 'log_bin_trust_function_creators'  

可以看到默认的二进制日志时关闭的

 

 

[plain]  view plain  copy

 

1. Variable_name                   |Value |  
2. --------------------------------|------|  
3. log_bin_trust_function_creators |OFF   |  

通过下面的命令进行设置:

 

 

[plain]  view plain  copy

 

1. set global log_bin_trust_function_creators=1  

 

[plain]  view plain  copy

 

1. Variable_name                   |Value |  
2. --------------------------------|------|  
3. log_bin_trust_function_creators |ON    |  

１．创建一个生成随机字符串的函数:

 

 

[sql]  view plain  copy

 

1. DELIMITER $$  
2. create FUNCTION rand_string(n int) returns varchar(255)  
3. BEGIN  
4.     declare chars_str varchar(100) default 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';  
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 $$  

通过DELIMITER定义mysql语句的结束符,因为函数中多处有分号,如果不修改掉默认的结束符;那么函数将会产生错误

 

随后,创建生成随机数的函数:

 

[plain]  view plain  copy

 

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 $$  

插入员工表函数:

 

 

[plain]  view plain  copy

 

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;  
6.     repeat  
7.     set i=i+1;  
8.     insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values((start+i),rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());  
9.     until i=max_num  
10.     end repeat;  
11.     commit;  
12. end $$  

插入部门的函数:

 

 

[plain]  view plain  copy

 

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;  
6.     repeat  
7.     set i=i+1;  
8.     insert into dept(deptno,dname,loc) values((start+i),rand_string(10),rand_string(8));  
9.     until i=max_num  
10.     end repeat;  
11.     commit;  
12.     end $$  
13. end $$  

调用上边的两个插入函数

 

 

[plain]  view plain  copy

 

1. call insert_dept(100,10);  

 

[plain]  view plain  copy

 

1. +----+--------+------------+----------+  
2. | id | deptno | dname      | loc      |  
3. +----+--------+------------+----------+  
4. |  1 |    101 | OVnDatOMsA | BbGYWOaO |  
5. |  2 |    102 | PHQffkLYGl | mEgXmxza |  
6. |  3 |    103 | IljlEhcRXn | xvJjUSGz |  
7. |  4 |    104 | EwuFUElxBk | zNrtSdVl |  
8. |  5 |    105 | vtHaksNIb  | mdGUBVar |  
9. |  6 |    106 | FamifbRZyr | ljmJDQso |  
10. |  7 |    107 | tYLKrAAbHd | ziuuQRKZ |  
11. |  8 |    108 | SpXNXzEvmc | ByJZUzNo |  
12. |  9 |    109 | hXlpSoXPfj | HDUNcbT  |  
13. | 10 |    110 | sfBoRlLUlA | OtCCdPIU |  
14. +----+--------+------------+----------+  

在员工表中插入50W条数据,

 

 

[plain]  view plain  copy

 

1. mysql> call insert_emp(100001,500000);  

这样就实现了大量数据的插入。

转载于:https://www.cnblogs.com/GotoJava/p/7116481.html