任务需求：定时执行的任务，调用存储过程，进行数据迁移。
存储过程相关总结：(存储过程的创建 不能伴随有if exists 需要提前判断删除同名的存储过程)
存储过程的查看：
select name from mysql.proc where db = your_db_name and type = PROCEDURE
show procedure status;
查看存储过程或函数的创建代码
show create procedure proc_name;
存储过程的创建 语法以及遇到的问题
MySQL中，创建存储过程的基本形式如下：
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 - 正确
)
3. MySQL 存储过程的参数不能指定默认值。
4. MySQL 存储过程不需要在 procedure body 前面加 “as”。而 SQL Server 存储过程必须加 “as” 关键字。
create procedure pr_add
(
a int,
b int
)
as - 错误，MySQL 不需要 “as”
begin
mysql statement ...;
end;
5. 如果 MySQL 存储过程中包含单条或者多条 MySQL 语句，都需要 begin end 关键字。
create procedure pr_add
(
a int,
b int
)
begin
mysql statement 1 ...;
mysql statement 2 ...;
end;
6.MySQL 存储过程中的每条语句的末尾，都要加上分号 “;”
...
declare c int;
if a is null then
set a = 0;
end if;
...
7. MySQL 存储过程中的注释。
/*
这是个
多行 MySQL 注释。
/
declare c int; - 这是单行 MySQL 注释 （注意- 后至少要有一个空格）
if a is null then 这也是个单行 MySQL 注释
set a = 0;
end if;
...
end;
8. 不能在 MySQL 存储过程中使用 “return” 关键字。
set c = a + b;
select c as sum;
/*
return c;- 不能在 MySQL 存储过程中使用。return 只能出现在函数中。
/
end;
9. 调用 MySQL 存储过程时候，需要在过程名字后面加“()”，即使没有一个参数，也需要“()”
call pr_no_param();
10. 因为 MySQL 存储过程参数没有默认值，所以在调用 MySQL 存储过程时候，不能省略参数。可以用 null 来替代。
call pr_add(10, null);
mysql写条件判断时，注意时if elseif else 其中elseif之间是不能够有空格的。
命令行编写存储过程命令是一定注意提前delimiter。说明：MySQL中默认的语句结束符为分号（;）。存储过程中的SQL语句需要分号来 结束。为了避免冲突，首先用"DELIMITER &&"将MySQL的结束符设置为&&。最后再用"DELIMITER ;"来将结束符恢复成分号。这与创建触发器时是一样的。但是mysql 窗口就没问题了。
存储过程的删除： 不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程
drop procedure if exists prName
11，存储方法
存储方法与存储过程的区别
1，存储方法的参数列表只允许IN类型的参数，而且没必要也不允许指定IN关键字
2，存储方法返回一个单一的值，值的类型在存储方法的头部定义
3，存储方法可以在SQL语句内部调用
4，存储方法不能返回结果集
mysql事件：创建存储过程的时候，如果采用命令行的方式，需要先修改命令结束符，将分号改成其他的符号，注意delimiter时 不能在$$后添加; 否则就以$$; 作为结束标记了。
-- 设置分隔符为 '$$' ，mysql默认的语句分隔符为 ';' ，这样在后续的 create 到 end 这段代码都会看成是一条语句来执行
DELIMITER $$
//创建存储过程或者事件语句
//结束
$$
- 将语句分割符设置回 ';'
DELIMITER ;
事件简介
事件（event）是MySQL在相应的时刻调用的过程式数据库对象。一个事件可调用一次，也可周期性的启动，它由一个特定的线程来管理的，也就是所谓的“事件调度器”。
事件和触发器类似，都是在某些事情发生的时候启动。当数据库上启动一条语句的时候，触发器就启动了，而事件是根据调度事件来启动的。由于他们彼此相似，所以事件也称为临时性触发器。
事件取代了原先只能由操作系统的计划任务来执行的工作，而且MySQL的事件调度器可以精确到每秒钟执行一个任务，而操作系统的计划任务（如：Linux下的CRON或Windows下的任务计划）只能精确到每分钟执行一次。
2 事件的优缺点
2.1 优点
一些对数据定时性操作不再依赖外部程序，而直接使用数据库本身提供的功能。
可以实现每秒钟执行一个任务，这在一些对实时性要求较高的环境下就非常实用了。
2.2 缺点
定时触发，不可以调用。
3 创建事件
一条create event语句创建一个事件。每个事件由两个主要部分组成，第一部分是事件调度（event schedule），表示事件何时启动以及按什么频率启动，第二部分是事件动作（event action ），这是事件启动时执行的代码，事件的动作包含一条SQL语句，它可能是一个简单地insert或者update语句，也可以使一个存储过程或者benin...end语句块，这两种情况允许我们执行多条SQL。
一个事件可以是活动（打开）的或停止（关闭）的，活动意味着事件调度器检查事件动作是否必须调用，停止意味着事件的声明存储在目录中，但调度器不会检查它是否应该调用。在一个事件创建之后，它立即变为活动的，一个活动的事件可以执行一次或者多次。
3.1 创建语法如下
CREATE
[DEFINER = { user | CURRENT_USER }]
EVENT
[IF NOT EXISTS]
event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
DO event_body;
schedule:
AT timestamp [+ INTERVAL interval] ...
| EVERY interval
[STARTS timestamp [+ INTERVAL interval] ...]
[ENDS timestamp [+ INTERVAL interval] ...]
interval:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
名词解释：
event_name ：创建的event名字（唯一确定的）。
ON SCHEDULE：计划任务。
schedule: 决定event的执行时间和频率（注意时间一定要是将来的时间，过去的时间会出错），有两种形式 AT和EVERY。
[ON COMPLETION [NOT] PRESERVE]： 可选项，默认是ON COMPLETION NOT PRESERVE 即计划任务执行完毕后自动drop该事件；ON COMPLETION PRESERVE则不会drop掉。
[COMMENT 'comment'] ：可选项，comment 用来描述event；相当注释，最大长度64个字节。
[ENABLE | DISABLE] ：设定event的状态，默认ENABLE：表示系统尝试执行这个事件， DISABLE：关闭该事情，可以用alter修改
DO event_body: 需要执行的sql语句（可以是复合语句）。CREATE EVENT在存储过程中使用时合法的。
定时器 -- 创建定时器后会在mysql 中创建一个线程用以执行该事件。 user就是event_scheduler command-daemon state-waiting for next activation
1.查看定时器-事件调度器 MySQL事件调度器event_scheduler负责调用事件，它默认是关闭的。这个调度器不断地监视一个事件是否要调用， 要创建事件，必须打开调度器。
show variables like '%event_scheduler%';
2.开启时间调度器
命令行：
SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@global.event_scheduler = 1;
配置文件：event_scheduler = 1 #或者ON
查看调度器线程
mysql> show processlist;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| 2 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 3 | event_scheduler | localhost | NULL | Daemon | 6 | Waiting on empty queue | NULL |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
关闭事件调度器
通过命令行
可通过如下任何一个命令行
SET GLOBAL event_scheduler = OFF;
SET @@global.event_scheduler = OFF;
SET GLOBAL event_scheduler = 0;
SET @@global.event_scheduler = 0;
通过配置文件my.cnf
在[mysqld]下增加
event_scheduler = 0 #或者OFF，DISABLED
查看调度器线程
mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 2 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
查看事件运行状态：
（1）查询mysql.event表；
（2）通过SHOW EVENTS命令；
（4）通过查询information_schema.events表
（5）SHOW CREATE EVENT。 -- 查看创建目录
总之，event的使用频率较低建议使用root用户进行创建和维护。
使用权限
单独使用event调用SQL语句时，查看和创建需要用户具有event权限，调用该SQL语句时，需要用户具有执行该SQL的权限。Event权限的设置保存在mysql.user表和mysql.db表的Event_priv字段中。
当event和procedure配合使用的时候，查看和创建存储过程需要用户具有create routine权限，调用存储过程执行时需要使用excute权限，存储过程调用具体的SQL语句时，需要用户具有执行该SQL的权限。
关于事件计划的权限：
单独使用event调用SQL语句时，查看和创建需要用户具有event权限，调用该SQL语句时，需要用户具有执行该SQL的权限。Event权限的设置保存在mysql.user表和mysql.db表的Event_priv字段中。（FLUSH PRIVILEGES;）
当event和procedure配合使用的时候，查看和创建存储过程需要用户具有create routine权限，调用存储过程执行时需要使用excute权限，存储过程调用具体的SQL语句时，需要用户具有执行该SQL的权限。
SELECT HOST,USER,Event_priv FROM mysql.user;
（Figure1：user表的Event_priv权限）
获取当前登陆的用户和数据库：SELECT CURRENT_USER(), SCHEMA();
从Figure1可以知道bfsql@%是没有Event_priv权限的，在该用户下创建事件的时候会出现下面的错误：
Error Code: 1044
Access denied for user 'bfsql'@'%' to database 'blog'
如果出现上面的错误，执行下面的SQL就可以给bfsql@%赋予创建Event的权限：
UPDATE mysql.user SET Event_priv = 'Y' WHERE HOST='%' AND USER='bfsql';
如果你这个时候再次执行创建Event的SQL，还是会出现上面的错误，因为你需要执行：
FLUSH PRIVILEGES;最后，你可以通过SHOW GRANTS FOR 'bfsql'@'%';查看所有权限；
创建event语句相关命令解释：
ON SCHEDULE 计划任务，有两种设定计划任务的方式：
1. AT 时间戳，用来完成单次的计划任务。
2. EVERY 时间（单位）的数量时间单位[STARTS 时间戳] [ENDS时间戳]，用来完成重复的计划任务。
在两种计划任务中，时间戳可以是任意的TIMESTAMP 和DATETIME 数据类型，时间戳需要大于当前时间。
在重复的计划任务中，时间（单位）的数量可以是任意非空（Not Null）的整数式，时间单位是关键词：YEAR，MONTH，DAY，HOUR，MINUTE 或者SECOND。
提示: 其他的时间单位也是合法的如：QUARTER, WEEK, YEAR_MONTH,DAY_HOUR,DAY_MINUTE,DAY_SECOND,HOUR_MINUTE,HOUR_SECOND, MINUTE_SECOND，不建议使用这些不标准的时间单位。
标注4： [ON COMPLETION [NOT] PRESERVE]
ON COMPLETION参数表示"当这个事件不会再发生的时候"，即当单次计划任务执行完毕后或当重复性的计划任务执行到了ENDS阶段。而PRESERVE的作用是使事件在执行完毕后不会被Drop掉，建议使用该参数，以便于查看EVENT具体信息。
标注5：[ENABLE | DISABLE]
参数Enable和Disable表示设定事件的状态。Enable表示系统将执行这个事件。Disable表示系统不执行该事件。
可以用如下命令关闭或开启事件：
ALTER EVENT event_name ENABLE/DISABLE
标注6：[COMMENT 'comment']
注释会出现在元数据中，它存储在information_schema表的COMMENT列，最大长度为64个字节。'comment'表示将注释内容放在单引号之间，建议使用注释以表达更全面的信息。
当然SQL语句是有限制的，对它的限制跟函数Function和触发器Trigger 中对SQL语句的限制是一样的，如果你在函数Function 和触发器Trigger 中不能使用某些SQL，同样的在EVENT中也不能使用。明确的来说有下面几个：
LOCK TABLES
UNLOCK TABLES
CREATE EVENT
ALTER EVENT
LOAD DATA
修改事件 使用ALTER EVENT 来修改事件，具体的ALTER语法如下，与创建事件的语法类似：
删除事件:EVENT使用DROP EVENT语句来删除已经创建的事件，语法如下：DROP EVENT [IF EXISTS] event_name
但当一个事件正在运行中时，删除该事件不会导致事件停止，事件会执行到完毕为止。使用DROP USER和DROP DATABASE 语句同时会将包含其中的事件删除。
每隔一秒自动调用e_test()存储过程:
CREATE EVENT IF NOT EXISTS e_test
ON SCHEDULE EVERY 1 SECOND
ON COMPLETION PRESERVE
DO CALL e_test();
每个月的一号凌晨1 点执行STAT()存储过程：
CREATE EVENT NOT EXISTS STAT
ON SCHEDULE EVERY 1 MONTH STARTS DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 1 MONTH),INTERVAL 1 HOUR)
ON COMPLETION PRESERVE ENABLE
DO
BEGIN
CALL STAT();
END
创建一个每隔3秒往test表中插入一条数据的事件，代码如下：
CREATE EVENT IF NOT EXISTS test ON SCHEDULE EVERY 3 SECOND
ON COMPLETION PRESERVE
DO INSERT INTO test(id,t1) VALUES('',NOW());
创建一个10分钟后清空test表数据的事件
CREATE EVENT IF NOT EXISTS test
ON SCHEDULE
AT CURRENT_TIMESTAMP + INTERVAL 10 MINUTE
DO TRUNCATE TABLE test.aaa;
创建一个在2012-08-23 00:00:00时刻清空test表数据的事件
CREATE EVENT IF NOT EXISTS test
ON SCHEDULE
AT TIMESTAMP '2012-08-23 00:00:00'
DO TRUNCATE TABLE test;
创建一个从2012年8月22日21点45分开始到10分钟后结束，运行每隔3秒往test表中插入一条数据的事件
CREATE EVENT IF NOT EXISTS test ON SCHEDULE EVERY 3 SECOND
STARTS '2012-08-22 21:49:00'
ENDS '2012-08-22 21:49:00'+ INTERVAL 10 MINUTE
ON COMPLETION PRESERVE
DO INSERT INTO test(id,t1) VALUES('',NOW());
注意：
默认创建事件存储在当前库中，也可显示指定事件创建在哪个库中
通过show events只能查看当前库中创建的事件
事件执行完即释放，如立即执行事件，执行完后，事件便自动删除，多次调用事件或等待执行事件可以查看到。
如果两个事件需要在同一时刻调用，mysql会确定调用他们的顺序，如果要指定顺序，需要确保一个事件至少在另一个事件1秒后执行
对于递归调度的事件，结束日期不能在开始日期之前。
select可以包含在一个事件中，然而他的结果消失了，就好像没执行过。
删除事件:drop event 语句删除它。使用这条语句我们不需要等到最后一次事件调用。DROP EVENT [IF EXISTS] event_name,drop event if exists event_second;
一个事件示例：每分钟的零秒执行数据迁移
create event updateEachMinuteAt0Second
on schedule every 1 minute starts timestampadd(second,60 -second(current_timestamp()),current_timestamp())
on completion preserve enable
do
begin
call updatetestevent();
end
函数体内使用函数变量提前声明 declare a int; 判断是否为空 a is null; 条件块：if body then body; elseif body then body; end if; 存储过程:begin body end;
网友评论