3MySQL数据库-------MySQL批量插入数据---实例运用------全栈式开发38

一、基本说明

• 创建插入函数，可以自动创建数据，实现大批量的数据插入
• 这方面不需要掌握太多，理解下面的例子会用就行
• mysql中各种函数，参考：https://www.runoob.com/mysql/mysql-functions.html
• 关于mysql中的循环可参考:https://www.cnblogs.com/weibanggang/p/9678312.html

创建生成随机数据的函数

• DELIMITER $$ --分隔符，MySQL默认的结束符是分号，但是函数体中可能用到分号。为了避免冲突，需要另外定义结束符。
• CREATE FUNCTION 函数名(n INT) RETURNS 数据类型—(n INT)函数的参数和类型
• 函数实现过程放在BEGIN 与 END之间。
• DECLARE 声明变量名和类型，默认值是空。
• WHILE ;：可以接while循环判断，对参数进行处理
  • CONCAT连接多个字符串。
  • 更多循环内容可参考:https://www.cnblogs.com/weibanggang/p/9678312.html
• RETURN 返回变量

创建存储过程

• 就是实现批量插入数据函数
• 存储过程(stored procedure)是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。存储过程就是数据库sql语言层面的代码封装与重用。
• 可参考：https://www.runoob.com/w3cnote/mysql-stored-procedure.html
• 参照模板，大同小异

  delimiter $$
  create procedure insert_emp(in start int(10),in max_num int(10))  -- procedure 就是存储过程
  begin
  declare i int default 0;
  set autocommit = 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),'saleman',0001,curdate(),2000,400,rand_num());---curdate() 返回当前日期(年月日，不带时间)
  until i = max_num
  end repeat;
  commit;
  end $$
  

函数调用

• 普通调用：select 函数名(参数1,…);
• CALL 语句来调用存储过程：call 函数名(参数1,…);

二、案例实现

```
-- 部门表
create table dept(
    id int primary key auto_increment,
    deptno mediumint not null,
    dname varchar(20) not null,
    loc varchar(13) not null
)engine=innodb default charset=utf8;

-- 员工表
create table emp(
    id int primary key auto_increment,
    empno mediumint not null,
    ename varchar(20) not null,
    job varchar(9) not null,
    mgr mediumint not null,
    hiredate date not null,
    sal decimal(7,2) not null,
    comm decimal(7,2) not null,
    deptno mediumint not null
)engine=innodb default charset=utf8;
```

（一） 随机产生字符串

show variables like 'log_bin_trust_function_creators%';

-- 如果是off，打开
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 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
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()*48),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END $$ --分隔符

创建函数

• CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255) – 创建函数

rand_string

• rand_string(n INT) 随机数n整数 RETURNS VARCHAR(255) 返回值返回类型

substring
– substring用于截取字符串
substring(string,position,length);
– string 要截取的字符串，position 起始字符（从1开始），length截取长度

concat

• concat用于字符串的拼接
• 如 select concat(‘my’,‘sql’); —返回mysql

（二） 随机产生部门编号

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

• floor(100+rand()*10)产生随机整数

（三） 批量插入数据函数

创建存储过程

• 就是实现批量插入数据函数
• 存储过程(stored procedure)是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。存储过程就是数据库sql语言层面的代码封装与重用。
  可参考：https://www.runoob.com/w3cnote/mysql-stored-procedure.html

--- 员工表插入数据函数，调用上面创建的rand_string() 和rand_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;
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 $$


-- 部门表插入数据函数
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+1),rand_string(10),rand_string(8));
until i = max_num
end repeat;
commit;
end $$

• set autocommit = 0；关闭自动提交
• commit;统一提交，因为如果每次插入一行数据都执行提交的话，性能会比较差
• procedure 就是存储过程
• repeat 表示循环开始
• curdate() 返回当前日期(年月日，不带时间)

调用存储过程

call insert_dept(100,10); --- call insert_dept(100,10);  调用存储过程
call insert_emp(100001,500000);