MySQL--批量数据脚本

你的名字 2021-08-13 13:12 463阅读 0赞

### 批量数据脚本 ###

**建表：**

CREATE TABLE dept(
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
    dname VARCHAR(20) NOT NULL DEFAULT '',
    ioc VARCHAR(13) NOT NULL DEFAULT ''
    )ENGINE=INNODB DEFAULT CHARSET=GBK;
    
    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(9) 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=utf8;

**设置餐宿log\_bin\_trust\_function\_creators**  
创建函数，假如报错：This function has none of DETERMINISTIC…

由于开启过慢查询日志，因为开启了bin-log，我们就必须为我们的function指定一个参数。

show variables like 'log_bin_trust_function_creators';
    set global log_bin_trust_function_creators=1;
    
    #这样添加了参数以后，如果mysqld重启，上述参数又会消失，永久方法：
    window下my.ini[mysqld]加上log_bin_trust_function_creators=1
    linux下 /etc/my.cnf下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 $$
    
    select rand_string(5)$$

**随机产生部门编号**

DELIMITER $$
    CREATE FUNCTION rand_num() RETURNS INT(5)
    BEGIN
    	DECLARE i INT DEFAULT 0;
    	SET i = FLOOR(100 + RAND() * 10);
    	RETURN i;
    END $$

**创建存储工程**

#向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 $$
    
    #向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, ioc) VALUES((START + i), rand_string(10),rand_string(8));
    	UNTIL i = max_num
    	END REPEAT;
    	COMMIT;
    END $$

**调用存储函数插入数据**  
DELIMITER ;  
CALL insert\_dept(100, 10);

DELIMITER ;  
CALL insert\_emp(100,100);

![在这里插入图片描述](https://img-blog.csdnimg.cn/20191126222916506.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5,size_16,color_FFFFFF,t_70)
    
    ![在这里插入图片描述](https://img-blog.csdnimg.cn/20191126222936615.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5,size_16,color_FFFFFF,t_70)