数据库 mysql批量插入数据函数存储过程

ゞ浴缸里的玫瑰 2022-12-28 00:57 341阅读 0赞

## 数据库 mysql批量插入数据函数存储过程 ##

-- 1 批量插入数据
    -- 1.1 创建表
    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`)
    #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
    ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
    
    
    -- 1.2 设置参数
    -- 在执行创建函数之前， 首先请保证 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.3 编写随机函数
    -- 1.3.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 $$
    
    -- 如果要删除函数， 则执行： 
    drop function rand_string;
    
    -- 1.3.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$$
    
    -- 如果要删除函数： 
    drop function rand_num;
    
    -- 1.4 创建存储过程
    -- 1.4.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$$
    
    #删除
    # DELIMITER ;
    # drop PROCEDURE insert_emp;
    
    -- 1.4.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$$
    
    #删除
    # DELIMITER ;
    # drop PROCEDURE insert_dept;
    
    -- 1.5 调用存储过程
    -- 1.5.1 添加数据到部门表
    #执行存储过程， 往 dept 表添加 1 万条数据
    DELIMITER ;
    CALL insert_dept(100);
    
    -- 1.5.2 添加数据到员工表
    #执行存储过程， 往 emp 表添加 50 万条数据
    DELIMITER ;
    CALL insert_emp(1000,5000);