MySQL基础：存储过程（利用存储过程生成随机数据）

旧城等待， 2022-01-29 14:43 542阅读 0赞

**1.创建存储过程**  
右键函数，新建函数  
![在这里插入图片描述][2019052212091042.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x0aGlyZG9uZWw_size_16_color_FFFFFF_t_70]  
2. 创建函数或者存储过程  
填入参数  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x0aGlyZG9uZWw_size_16_color_FFFFFF_t_70 1]  
3. 填写存储过程代码

随机往aws\_action表中插入数据

BEGIN
     
    	DECLARE i int UNSIGNED;
    	DECLARE t int UNSIGNED;
    	set i=1;
    	set t=1529370782;
    	while i<=num DO
    		if(i%50=0) then 
    			set t=t+FLOOR(1+RAND()*18);
    		ELSE
    			set t=t+FLOOR(1+RAND()*10000);
    		end if;
    		insert into aws_action(item_id,user_id,start_time) values(FLOOR(11000+RAND()*60000),FLOOR(1+RAND()*17),t);
    		
    		set i = i + 1;
    		
    	end while;
    
    
    END

1.  原始SQL语句

DROP PROCEDURE IF EXISTS `insertaction`;
    
    CREATE DEFINER = `root`@`%` PROCEDURE `insertaction`(IN `num` int)
    BEGIN
     
    	DECLARE i int UNSIGNED;
    	DECLARE t int UNSIGNED;
    	set i=1;
    	set t=1529370782;
    	while i<=num DO
    		if(i%50=0) then 
    			set t=t+FLOOR(1+RAND()*18);
    		ELSE
    			set t=t+FLOOR(1+RAND()*10000);
    		end if;
    		insert into aws_action(item_id,user_id,start_time) values(FLOOR(11000+RAND()*60000),FLOOR(1+RAND()*17),t);
    		
    		set i = i + 1;
    		
    	end while;
    
    
    END;

[2019052212091042.png]: /images/20220129/aa771d7bc30741ab9718c4334c16ed4c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x0aGlyZG9uZWw_size_16_color_FFFFFF_t_70]: /images/20220129/1427fecf1d7048d39cdb40fa62f9fe75.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x0aGlyZG9uZWw_size_16_color_FFFFFF_t_70 1]: /images/20220129/91ba68033a7b4bfbabaee358925248df.png