澳门金沙vip 12

【澳门金沙vip】Navicat Premium创建事件计划调用MySql存储过程

1、检查事件计划,操作:工具——命令行界面——执行命令 show variables like
‘%event_scheduler%’; (分号不能丢)—— event_scheduler  ON 表示开启
event_scheduler OFF 表示关闭 

任务需求:定时执行的任务,调用存储过程,进行数据迁移。

 

 什么是事件: 

一组SQL集,用来执行定时任务,跟触发器很像,都是被动执行的,事件是因为时间到了触发执行,而触发器是因为某件事件(增删改)触发执行;

查看是否开启:

show variables like 'event_scheduler';

如果显示OFF,则输入以下语句开启:

set global event_scheduler = on;

提醒:虽然这里用set global event_scheduler =
on语句开启了事件,但是每次重启电脑。或重启mysql服务后,会发现,事件自动关闭(event_scheduler=OFF),所以想让事件一直保持开启,最好修改配置文件,让mysql服务启动的时候开启时间,只需要在my.ini配置文件的[mysqld]部分加上event_scheduler=ON 即可,如下:

自定义事件举例

首先、新建user表:

澳门金沙vip 1

-- ----------------------------
-- Table structure for `user`
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `address` varchar(500) NOT NULL,
  `addtime` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

澳门金沙vip 2

然后、新建事件,有两种事件类型,一个是间隔触发,另一个是特定事件触发。

*
澳门金沙vip 3*

存储过程相关总结:(存储过程的创建  不能伴随有if exists
 需要提前判断删除同名的存储过程)

类型一:每隔一分钟插入一条数据:

参数说明:

DEFINER:创建者;

ON COMPLETION [NOT] PRESERVE
:表示当事件不会再发生的情况下,删除事件(注意特定时间执行的事件,如果设置了该参数,执行完毕后,事件将被删除,不想删除的话可以设置成ON
COMPLETION PRESERVE);

ENABLE:表示系统将执行这个事件;

澳门金沙vip 4

-- ----------------------------
-- Event structure for `event_minute`
-- ----------------------------
DROP EVENT IF EXISTS `event_minute`;
DELIMITER ;;
CREATE DEFINER=`xxxxx`@`10.36.40.1` EVENT `event_minute` ON SCHEDULE EVERY 1 MINUTE STARTS '2016-08-15 22:46:33' ON COMPLETION NOT PRESERVE ENABLE DO 

BEGIN
    INSERT INTO USER(name, address,addtime) VALUES('test1','test1',now());
    INSERT INTO USER(name, address,addtime) VALUES('test2','test2',now());
END
;;
DELIMITER ;

澳门金沙vip 5

2、开启事件计划——命令行界面——执行命令  set global
event_scheduler=on;(分号不能丢)

存储过程的查看:

类型二:特定时间插入一条数据,如2016-01-17 15:30:00**

参数说明:

DEFINER:创建者;

ON COMPLETION [NOT] PRESERVE
:表示当事件不会再发生的情况下,删除事件(注意特定时间执行的事件,如果设置了该参数,执行完毕后,事件将被删除,不想删除的话可以设置成ON
COMPLETION PRESERVE);

ENABLE:表示系统将执行这个事件;

澳门金沙vip 6

-- ----------------------------
-- Event structure for `event_at`
-- ----------------------------
DROP EVENT IF EXISTS `event_at`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` EVENT `event_at` ON SCHEDULE AT '2016-01-17 15:30:00' ON COMPLETION NOT PRESERVE ENABLE DO 

BEGIN
    INSERT INTO USER(name, address,addtime) VALUES('AT','AT',now());
END
;;
DELIMITER ;

澳门金沙vip 7

查看结果,结果正确:

澳门金沙vip 8

澳门金沙vip 9

select name from mysql.proc where db = your_db_name and type =
PROCEDURE

 

 

 

 

 

mysql的定时任务一般用event(事件)来完成,触发器无法完成。
一、通过mysql的命令行客户端来完成
1、set global event_scheduler =1; //开启event_scheduler
执行这个语句如果出现,可在mysql的配置文档中设置[mysqld]段中添加
event_scheduler=ON
如果重启mysql;这种情况下依然出错,Error Code: 1290. The MySQL server is
running with the –event-scheduler=DISABLED 
or –skip-grant-tables option so it cannot execute this
statement,这个错误是说启动服务器时如果指定了–skip-grant-tables选项,则event_scheduler则自动被设置为DISABLED。命令行或配置文件的设置都会被覆盖。建议重现安装mysql或是修改启动参数(在系统服务中指定)。

查看event_scheduler状态:show status like ‘%event%’;
SELECT @@event_scheduler;
2、CREATE PROCEDURE Mypro() //创建存储过程

BEGIN
update userinfo SET endtime = now() WHERE id = ‘155’;
END;
3、创建event My_enevt,每隔三十秒执行一次
create event if not exists e_test
on schedule every 30 second
on completion preserve
do call Mypro();
4、关闭事件 
alter event e_test ON COMPLETION PRESERVE DISABLE;
5、开启事件
alter event e_test ON COMPLETION PRESERVE ENABLE;
语法:
CREATE EVENT [IF NOT EXISTS] event_name ON SCHEDULE schedule [ON
COMPLETION
[NOT] PRESERVE] [ENABLE | DISABLE] [COMMENT ‘comment’] DO
sql_statement;
schedule: AT TIMESTAMP [+ INTERVAL INTERVAL] | EVERY INTERVAL [STARTS
TIMESTAMP]
[ENDS TIMESTAMP] INTERVAL: quantity {YEAR | QUARTER | MONTH | DAY |
HOUR |
MINUTE | WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND |
HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

示例:

1) 首先来看一个简单的例子来演示每秒插入一条记录到数据表
CREATE EVENT e_test_insert ON SCHEDULE EVERY 1 SECOND DO INSERT INTO
test.aaa VALUES  (CURRENT_TIMESTAMP);

2) 5天后清空test表:
CREATE EVENT e_test ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO TRUNCATE TABLE test.aaa;

3) 2013年7月20日12点整清空test表:
CREATE EVENT e_test ON SCHEDULE AT TIMESTAMP ‘2013-07-20 12:00:00’
DOTRUNCATE TABLE test.aaa;

4) 每天定时清空test表:
CREATE EVENT e_test ON SCHEDULE EVERY 1 DAY DO TRUNCATE TABLE test.aaa;

5) 5天后开启每天定时清空test表:
CREATE EVENT e_test ON SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP +
INTERVAL 5 DAY DO TRUNCATE TABLE test.aaa;

6) 每天定时清空test表,5天后停止执行:
CREATE EVENT e_test ON SCHEDULE EVERY 1 DAY ENDS CURRENT_TIMESTAMP +
INTERVAL 5 DAY DO TRUNCATE TABLE test.aaa;

7) 5天后开启每天定时清空test表,一个月后停止执行:
CREATE EVENT e_test ON SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP

  • INTERVAL 5 DAY ENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH DO TRUNCATE
    TABLE test.aaa;

[ON COMPLETION [NOT]
PRESERVE]可以设置这个事件是执行一次还是持久执行,默认为NOT PRESERVE
8)
每天定时清空test表(只执行一次,任务完成后就终止该事件):
CREATE EVENT e_test ON SCHEDULE EVERY 1 DAY ON COMPLETION NOT PRESERVE
DO TRUNCATE TABLE test.aaa;

[COMMENT
comment]可以给该事件加上注释

2>修改事件(ALTER EVENT)
ALTER EVENT event_name [ON SCHEDULE schedule] [RENAME TO
new_event_name]
[ON COMPLETION [NOT] PRESERVE] [COMMENT ‘comment’] [ENABLE |
DISABLE] [DO
sql_statement]

1) 临时关闭事件
ALTER EVENT e_test DISABLE;

2) 开启事件
ALTER EVENT e_test ENABLE;

3) 将每天清空test表改为5天清空一次:
ALTER EVENT e_test ON SCHEDULE EVERY 5 DAY;

3>删除事件(DROP EVENT)

语法很简单,如下所示:
DROP EVENT [IF EXISTS] event_name

例如删除前面创建的e_test事件
DROP EVENT e_test;

当然前提是这个事件存在,否则会产生ERROR 1513 (HY000): Unknown
event错误,因此最好加上IF EXISTS
DROP EVENT IF EXISTS e_test;

二、使用第三方工具来创建,推荐使用HeidiSQL网站:,免费的开源客户端,由德国程序员Ansgar
Becker开发;这个软件的界面有中文界面。
打开HeidiSQL,右键单击表名,选创建新的的菜单,再选事件即可,很方便,我们可以编辑事件的时间设置和代码。这里定义人填写root@localhost。

3、创建事件计划,操作:连接名——数据库——事件——新建事件

show procedure status;

澳门金沙vip 10

查看存储过程或函数的创建代码

4、事件计划设置,定义——填写要调用的存储过程(要完成的需求),计划——设置事件计划的执行时间

show create procedure proc_name;

澳门金沙vip 11

存储过程的创建 语法以及遇到的问题

5、保存——输入事件计划名——确定

MySQL中,创建存储过程的基本形式如下:

澳门金沙vip 12

CREATE PROCEDURE sp_name ([proc_parameter[,…]])

 

[characteristic …] routine_body

其中,sp_name参数是存储过程的名称;proc_parameter表示存储过程的参数列表;
characteristic参数指定存储过程的特性;routine_body参数是SQL代码的内容,可以用BEGIN…END来标志SQL代码的开始和结束。

proc_parameter中的每个参数由3部分组成。这3部分分别是输入输出类型、参数名称和参数类型。其形式如下:

[ IN | OUT | INOUT ] param_name type

其中,IN表示输入参数;OUT表示输出参数;
INOUT表示既可以是输入,也可以是输出;
param_name参数是存储过程的参数名称;type参数指定存储过程的参数类型,该类型可以是MySQL数据库的任意数据类型。

characteristic参数有多个取值。其取值说明如下:

LANGUAGE
SQL:说明routine_body部分是由SQL语言的语句组成,这也是数据库系统默认的语言。

[NOT]
DETERMINISTIC:指明存储过程的执行结果是否是确定的。DETERMINISTIC表示结果是确定的。每次执行存储过程时,相同的输入会得到相同的输出。NOT
DETERMINISTIC表示结果是非确定的,相同的输入可能得到不同的输出。默认情况下,结果是非确定的。

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA
}:指明子程序使用SQL语句的限制。CONTAINS
SQL表示子程序包含SQL语句,但不包含读或写数据的语句;NO
SQL表示子程序中不包含SQL语句;READS SQL
DATA表示子程序中包含读数据的语句;MODIFIES SQL
DATA表示子程序中包含写数据的语句。默认情况下,系统会指定为CONTAINS SQL。

SQL SECURITY { DEFINER | INVOKER
}:指明谁有权限来执行。DEFINER表示只有定义者自己才能够执行;INVOKER表示调用者可以执行。默认情况下,系统指定的权限是DEFINER。

COMMENT ‘string’:注释信息。

技巧:创建存储过程时,系统默认指定CONTAINS
SQL,表示存储过程中使用了SQL语句。但是,如果存储过程中没有使用SQL语句,最好设置为NO
SQL。而且,存储过程中最好在COMMENT部分对存储过程进行简单的注释,以便以后在阅读存储过程的代码时更加方便。

创建一个名为num_from_employee的存储过程。代码如下:

CREATE  PROCEDURE  num_from_employee (IN emp_id INT, OUT count_num
INT )

READS SQL DATA

BEGIN

SELECT  COUNT(*)  INTO  count_num

FROM  employee

WHERE  d_id=emp_id ;

END

上述代码中,存储过程名称为num_from_employee;输入变量为emp_id;输出变量为count_num。SELECT语句从employee表查询d_id值等于emp_id的记录,并用COUNT(*)计算d_id值相同的记录的条数,最后将计算结果存入count_num中。代码的执行结果如下:

mysql> DELIMITER &&

mysql> CREATE  PROCEDURE  num_from_employee

(IN emp_id INT, OUT count_num INT )

-> READS SQL DATA

-> BEGIN

-> SELECT  COUNT(*)  INTO  count_num

-> FROM  employee

-> WHERE  d_id=emp_id ;

-> END &&

Query OK, 0 rows affected (0.09 sec)

mysql> DELIMITER ;

代码执行完毕后,没有报出任何出错信息就表示存储函数已经创建成功。以后就可以调用这个存储过程,数据库中会执行存储过程中的SQL语句。

说明:MySQL中默认的语句结束符为分号(;)。存储过程中的SQL语句需要分号来 
  结束。为了避免冲突,首先用”DELIMITER
&&”将MySQL的结束符设置为&&。最后再用”DELIMITER
;”来将结束符恢复成分号。这与创建触发器时是一样的。

一、MySQL 创建存储过程

“pr_add” 是个简单的 MySQL 存储过程,这个存储过程有两个 int
类型的输入参数 “a”、“b”,返回这两个参数的和。

drop procedure if exists pr_add;

— 计算两个数之和

create procedure pr_add

(

a int,

b int

)

begin

declare c int;

if a is null then

set a = 0;

end if;

if b is null then

set b = 0;

end if;

set c = a + b;

select c as sum;

/*

return c;- 不能在 MySQL 存储过程中使用。return 只能出现在函数中。

/

end;

二、调用Mysql存储过程

eg: call pr_add(10,20);

执行 MySQL 存储过程,存储过程参数为 MySQL 用户变量。

set @a = 10;

set @b = 20;

call pr_add(@a, @b);

假定存储过程如下:create procedure pr_return2Pr(a int,out b int,inout c
int) begin set b =a; set c = a+ c; end

set @cc = 3;call pr_return2Pr(1,@bb,@cc);select @bb,@cc; 返回1,4

如果存储过程中变量定义为out,可以不进行变量值定义,直接声明即可。inout 
则需要提前set 变量,然后调用。

IN为默认类型,值必须在调用时指定,值不能返回(值传递)

OUT值可以返回(指针传递)

INOUT值必须在调用时指定,值可以返回

使用DECLARE来声明,DEFAULT赋默认值,SET赋值

DECLARE counter INT DEFAULT 0;  – 默认为0

SET counter = counter+1;              – 自增+1

三、MySQL 存储过程特点

创建 MySQL 存储过程的简单语法为:

create procedure 存储过程名字()

(

[in|out|inout] 参数 datatype

)

begin

MySQL 语句;

end;

MySQL
存储过程参数如果不显式指定“in”、“out”、“inout”,则默认为“in”。习惯上,对于是“in”
的参数,我们都不会显式指定。

如下,给出解释

  1. MySQL 存储过程名字后面的“()”是必须的,即使没有一个参数,也需要“()”

  2. MySQL 存储过程参数,不能在参数名称前加“@”,如:“@a
    int”。下面的创建存储过程语法在 MySQL 中是错误的(在 SQL Server
    中是正确的)。 MySQL 存储过程中的变量,不需要在变量名字前加“@”,虽然
    MySQL 客户端用户变量要加个“@”。

create procedure pr_add

(

@a int,- 错误

b int  – 正确

)

  1. MySQL 存储过程的参数不能指定默认值。

  2. MySQL 存储过程不需要在 procedure body 前面加 “as”。而 SQL Server
    存储过程必须加 “as” 关键字。

create procedure pr_add

(

a int,

b int

)

as            – 错误,MySQL 不需要 “as”

begin

mysql statement …;

end;

  1. 澳门金沙vip,如果 MySQL 存储过程中包含单条或者多条 MySQL 语句,都需要 begin end
    关键字。

create procedure pr_add

(

a int,