mysql 存储过程

mysql 定时器

 1.show variables like 'event%';  查询mysql 定时任务是否开启
 2. SET GLOBAL event_scheduler = 1;  开启mysql 定时任务
 3. SET GLOBAL event_scheduler = 0;  关闭mysql 定时任务

创建mysql 定时任务（每20秒执行一次）

CREATE EVENT e_test_insert
ON 
	SCHEDULE EVERY 20 SECOND STARTS TIMESTAMP  '2019-06-24 20:56:00'
DO 
	INSERT INTO test.test01 VALUES (CURRENT_TIMESTAMP);

mysql 存储过程定义

CREATE FUNCTION myFunction (
	str VARCHAR(255)  -- 声明会输入的参数
) 
 
RETURNS VARCHAR(255)	 -- 声明返回的类型
 
BEGIN	-- 开始定义
 
	DECLARE str1 VARCHAR(255);	-- 声明一个变量

	SET str1=CONCAT('Hello ',str);  -- 进行操作
	 
	RETURN(str1);			-- 返回值
 
END;

mysql 存储过程 do while

DROP PROCEDURE IF	EXISTS my_insert_list;
CREATE PROCEDURE my_insert_list ( IN str1 INTEGER ( 10 ) ) BEGIN
	DECLARE		i INTEGER;
	DECLARE		age INTEGER ( 20 );
	DECLARE		card VARCHAR ( 225 );
	SET i = 1;
	WHILE	i <= str1 DO
			
		SET age = FLOOR( RAND() * 100 );
		SET card = CONCAT(FLOOR( RAND() * 1000000 ));
		INSERT INTO test ( NAME, age, card )
		VALUES( 'hello', age, card );
		SET i = i + 1;
	END WHILE;
	
END;

mysql 存储过程调用

SELECT myFunction("JBY") AS test;