Mysql、(五) 千万级数据批量插入

---

一、开启函数创建错误日志

• 查看是否开启： show variables like '%log_bin_trust_function_creators';

• 开启：set global log_bin_trust_function_creators=1;

• 永久开启：

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

二、创建sql脚本

建表语句：

create table if not exists `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 if not exists `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,
   `hiredata` 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=gbk;

1) 创建函数

# 生成长度为n的随机字符串

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()*25),1));
     set i = i+1;
  end while;
  return return_str;
end $$

# 随机生成一个整形数字

delimiter $$
create function rand_num() returns int(6)
begin
  declare i int default 0;
  set i = floor(rand()*10+100);
  return i;
end $$

2) 创建存储过程

# 往emp 表中插入 id从start开始的 max_num 条数据

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;
      # 存储过程中调用了之前定义的函数 rand_num()、rand_string(int n)
      insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values ((start+i),rand_string(6),'saleman',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(10));
    until i=max_num
    end repeat;
    commit;
end $$

3) 调用存储过程

插入40w条数据：
call insert_emp(10001,40000);

此时使用40W条数据测试索引查询的优势就很明确了：

• 建立索引前，全表查询：
  
• 建立索引后：