MySQL用存储过程与函数批量插入数据

20.存储过程与函数

• 函数(FUNCTION)和存储过程(PROCEDURE),最大区别在于函数有返回值，存储过程没有返回值。

• 批量创建数据案例：

  # 创库
  create database bigData;
  use bigData;
  
  # 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=gbk;
  
  # 建表emp
  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(20) 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=GBK;
  

• 由于进行大批量数据插入，mysql会报一个错误。需要设置参数log_bin_trust_function_creators，它功效用于开启二进制模块，否则会报错：This function has none of DETERMINISTIC...

• 查看log_bin_trust_function_creators是否开启：默认关闭

  show variables like 'log_bin_trust_function_creators';
  

  

• 开启

  1.终端开启：
  	set global log_bin_trust_function_creators=1;
  # 这样添加参数以后，如果mysqld重启，上述参数会消失
  2.永久方式开启：
  	windows 下my.ini [mysqld]添加：log_bin_trust_function_creators=1
  	linux下 /etc/my.cnf [mysqld]加上 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 $$
  

  解释：
  	DELIMITER $$
  	# 默认DELIMITER 为 ';',但是我们在编辑我们创建函数使用';'' 会终端我们编辑函数，这样，我们通过 更给DELIMITER为$$，这样就得到解决。
  	CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
  	# 创建函数 rand_string 传入n为int类型， 返回值 为varchar(255)
  	DECLARE 变量名称  类型  DEFAULT 默认值
  	# while循环
  	WHILE 条件 DO
  	END WHILE;
  	# FLOOR函数：
  		FLOOR(RAND()*2)    # 表示0~2 随机生成一个数
       # SUBSTRING
       	SUBSTRING('HELLO WORLD',1,5) # 表示截取'HELLO WORLD' 1-5位也就是'HELLO'
       # CONCAT 用于拼接
       	SELECT CONCAT('NO.',2);
  

• 函数：所及产生部门编号

  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 函数名；

• 创建存储过程，往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 $$
  

  解释：
  	# SET autocommit=0; 每提交一条数据就会在终端打印，造成爆屏，所以可以关闭autocommit,最后我们再commit
  	# REPEAT ...UNTIL ... END REPEAT; 重复操作,知道UNTIL条件满足就END REPEAT
  	# CURDATE 年月日
  

• 创建存储过程：往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 $$
  

• 更改DELIMITER ; 成默认

• 调用存储过程插入10条数据 到dept

  CALL insert_dept(100,10);
  

• 调用存储过程插入500000条数据 到emp

  CALL insert_emp(100001,500000);