批量插入数据脚本

一时失言乱红尘 2022-09-12 04:59 248阅读 0赞

# 一 建表 #

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 开启状态。 否则会报错：

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY2hlbmdxaXVtaW5n_size_20_color_FFFFFF_t_70_g_se_x_16][]

查询：

> 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 测试查询时间 ###

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY2hlbmdxaXVtaW5n_size_20_color_FFFFFF_t_70_g_se_x_16 1][]

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY2hlbmdxaXVtaW5n_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/f5d73d55bbab4401b0a4ad26735a821b.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY2hlbmdxaXVtaW5n_size_20_color_FFFFFF_t_70_g_se_x_16 1]: /images/20220828/8b917c2d17ba40bca24b31e21ad8e2c1.png