批量插入数据脚本

一 建表

CREATE TABLE `dept` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `deptName` VARCHAR(30) DEFAULT NULL,
  `address` VARCHAR(40) DEFAULT NULL,
  ceo INT NULL ,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `emp` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `empno` INT NOT NULL ,
  `name` VARCHAR(20) DEFAULT NULL,
  `age` INT(3) DEFAULT NULL,
  `deptId` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

二 设置参数

在执行创建函数之前，首先请保证 log_bin_trust_function_creators 参数为 1，即 on 开启状态。 否则会报错：

查询：

show variables like 'log_bin_trust_function_creators';

设置：

set global log_bin_trust_function_creators=1;

当然，如上设置只存在于当前操作，想要永久生效，需要写入到配置文件中：

在[mysqld]中加上

log_bin_trust_function_creators=1

三 编写随机函数

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()*52),1));
      SET i = i + 1;
    END WHILE;
  RETURN return_str;
END $$

2 随机产生部门编号

# 用于随机产生多少到多少的编号
DELIMITER $$
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
  DECLARE i INT DEFAULT 0;
  SET i = FLOOR(from_num +RAND()*(to_num -from_num+1)) ;
RETURN i;
END$$

四  创建存储过程

1  创建往 emp 表中插入数据的存储过程

DELIMITER $$
CREATE PROCEDURE insert_emp( START INT , max_num INT )
BEGIN
  DECLARE i INT DEFAULT 0;
  #set autocommit =0 把 autocommit 设置成 0
  SET autocommit = 0;
  REPEAT
  SET i = i + 1;
  INSERT INTO emp (empno, NAME ,age ,deptid ) VALUES ((START+i) ,rand_string(6) , rand_num(30,50),rand_num(1,10000));
  UNTIL i = max_num
END REPEAT;
COMMIT;
END$$

2  创建往 dept 表中插入数据的存储过程

# 执行存储过程，往 dept 表添加随机数据
DELIMITER $$
CREATE PROCEDURE `insert_dept`( max_num INT )
BEGIN
  DECLARE i INT DEFAULT 0;
  SET autocommit = 0;
  REPEAT
    SET i = i + 1;
    INSERT INTO dept ( deptname,address,ceo ) VALUES (rand_string(8),rand_string(10),rand_num(1,500000));
    UNTIL i = max_num
  END REPEAT;
  COMMIT;
END$$

五  调用存储过程

1 添加数据到部门表

# 执行存储过程，往 dept 表添加 1 万条数据
DELIMITER ;
CALL insert_dept(10000);
# 执行结果
受影响的行: 0
时间: 7.217s

2 添加数据到员工表

# 执行存储过程，往 emp 表添加 50 万条数据
DELIMITER;
CALL insert_emp(100000,500000);
# 执行结果
受影响的行: 0
时间: 185.782s

3 测试查询时间