mysql中的事件、存储过程、触发器

目录
事件event 1
存储过程PROCEDURE 2
触发器 6
相关SHOW命令 7
示例 7




本篇涉及的SQL语句有：

CREATE EVENT、CREATE PROCEDURE、CREATE TRIGGER 

介绍了mysql中的事件event、存储过程procedure、触发器trigger，并给出具体例子

事件event

事件调度器是在MySQL5.1中新增的功能，可以作为定时任务调度器，取代部分原来只有操作系统任务调度器才能完成的定时功能。例如Linux中的crontab只能精确到每分钟执行一次，而MySQL事件调度器可以实现每秒钟执行一个任务，这些对实时性要求较高的环境来说是非常实用的。
事件触发器可以设置每隔一定时间执行指定的任务。事件由一个特定的线程来管理，即“事件调度器”，通过event_scheduler变量可以动态地控制mysql事件调度器，如果启用了，则可以使用SHOW PROCESSLIST查看事件调度器的线程。

语法：

create event [ if not exists] event_name
on schedule schedule_type        #  任务执行的时间 或 间隔
[on completion [not] preserver]     #  指定事件过期后，是否仍然保存，而不是被删除
[comment ‘comment’]            #  event的注释
DO event_body;                 #  event具体做什么,可以是SQL语句 
schedule_type:
   AT timestamp [+ INTERVAL interval] …
   | EVERY interval
    [ STARTS timestamp [+ INTERVAL interval] …]
    [ENDS timestamp [+ INTERVAL interval] …]
interval:
  quantity { YEAR | QUARTER | MONTH | DAY | HOUR| MNUTE | WEEK | SECOND | 
YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND |

HOUR_MINUTE |  HOUR_SECOND | MINUTE_SECOND}

例子：每五分钟调度一次p_up_t1存储过程
mysql> create event if not exists e_5minute
-> on schedule every 5 minute
->on completion preserver
    -> comment 'this e will call procedure p_up_t1'
    -> do call p_up_t1(@id);

Query OK, 0 rows affected (0.00 sec) 

注意：
只有在my.cnf中[mysqld]下面指定 event_scheduler=1启动mysql事件调度器，或者在mysql中通过set global event_scheduler=on动态启动事件调度器，或 通过start mysql –event_scheduler=1启动mysql服务，才可以使用事件调度器，这时在mysql中使用show processlist可以查看事件调度器线程信息。 
创建事件需要拥有对事件所属模式的 EVENT权限。


存储过程PROCEDURE
语法：
  CREATE PROCEDURE procedure_name ( [ proc_parameter[,…] ])
  [characteristic …] routine_body; 
proc_parameter:
  [ IN | OUT | INOUT] param_name type
type:
  ane valid MySQL data type
characteristic:
   COMMENT ‘string’
| LANGUAGE SQL
| [NOT] DETERMINISTIC
 | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
 | SQL SECURITY { DEFINER | INVOKER }
routine_body:
  valid SQL routine statement 
 存储过程是一组为了完成特定功能的SQL语句，经过编译后存储在数据库中。通过调用指定的存储过程名并给出参数来调用它。存储过程中可以包含逻辑控制语句和数据操作语句，可以接受参数、输出参数。
 创建存储过程时需要指定参数，如果没有参数也要加上括号 procedure_name()
 存储过程的三种参数类型in、out、inout
# in: 传值给存储过程，存储过程会改变这个值,改变后的变量值对于调用者是不可见的。
如set @id=10,调用procedure，执行id=id+1，call procedure返回id=11，而使用select @id，id仍为10
# out:把存储过程的值传递给调用者。在procedure内部out的被初始值为NULL，procedure返回结果时，out的值对于调用者是可见的。
使用set @id=10,调用out参数的procedure,id会被初始化为NULL,(此时id=NULL),然后返回procedure处理后的id值。使用select @id显示的是procedure处理后的id值。
# inout:由调用者对变量进行初始化,值可以被procedure改变,改变后的值对于调用者是可见的。
如set @id=10,调用procedure,返回改变的id=11,使用select @id显示也是id=11。 

 示例 in , out ,inout

#in 
mysql> create procedure p_test1( in id int)
    -> begin
    -> if (id is not null) then
    -> set id = id + 1;
    -> end if;
    -> select id as id_inner;
    -> end $
Query OK, 0 rows affected (0.00 sec) 
mysql> set @id=5$
Query OK, 0 rows affected (0.00 sec) 
mysql> call p_test1(@id)$
+----------+
| id_inner |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec) 
Query OK, 0 rows affected (0.00 sec) 
mysql> select @id$
+------+
| @id  |
+------+
|    5 |
+------+
1 row in set (0.00 sec)
mysql> 
#变量@id传入procedure内的值为5,执行procedure后,在procedure内部id=6，但外部变量值仍为id=5
#out
mysql> create procedure p_test2
    -> (out id int(3))
    -> begin
    ->  select id as id_inner_1;
    ->  if (id is not null) then
    ->    set id = id + 1;
    ->    select id as id_inner_2;
    ->  else
    ->    select 10 into id;
    ->  end if;
    ->  select id as id_inner_3;
    -> end$
Query OK, 0 rows affected (0.00 sec) 
#在procedure内部，out的初始值为NULL
mysql> call p_test2(@id)$
+------------+
| id_inner_1 |
+------------+
|       NULL |
+------------+
1 row in set (0.01 sec) 
+------------+
| id_inner_3 |
+------------+
|         10 |
+------------+
1 row in set (0.01 sec) 
Query OK, 0 rows affected (0.01 sec) 
#设置id的值为20
mysql> set @id=20$
Query OK, 0 rows affected (0.00 sec)
mysql> select @id$
+------+
| @id  |
+------+
|   20 |
+------+
1 row in set (0.00 sec) 
#调用p_test2,此时已经设置id的值为20
mysql> call p_test2(@id)$
+------------+
| id_inner_1 |
+------------+
|       NULL |
+------------+
1 row in set (0.00 sec) 
+------------+
| id_inner_3 |
+------------+
|         10 |
+------------+
1 row in set (0.00 sec) 
Query OK, 0 rows affected (0.00 sec)
#设置的id=20没有使用，也可以说调用p_test2时，p_test2重新初始化id为NULL,然后设置为10
mysql> select @id as id_out$
+--------+
| id_out |
+--------+
|     10 |
+--------+
1 row in set (0.00 sec) 
mysql> call p_test1(@id)$
+----------+
| id_inner |
+----------+
|       11 |
+----------+
1 row in set (0.00 sec) 
mysql> select @id as id$
+------+
| id   |
+------+
|   10 |
+------+
1 row in set (0.00 sec) 
#inout参数类型
mysql> create procedure p_test3
    -> (inout id int(3))
    -> begin
    ->   select id as id_inner_1;
    ->   if (id is not null) then
    ->     set id = id + 1;
    ->     select id as id_inner_2;
    ->   else
    ->     select 10 into id;
    ->   end if;
    ->   select id as id_inner_3;
    -> end$
Query OK, 0 rows affected (0.00 sec) 
#设置id=NULL
mysql> set @id=NULl$
Query OK, 0 rows affected (0.00 sec) 
mysql> call p_test3(@id)$
+------------+
| id_inner_1 |
+------------+
|       NULL |
+------------+
1 row in set (0.00 sec) 
+------------+
| id_inner_3 |
+------------+
|         10 |
+------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec) 
#此时id=10,继续调用p_test3
mysql> call p_test3(@id)$
+------------+
| id_inner_1 |
+------------+
|         10 |
+------------+
1 row in set (0.00 sec) 
+------------+
| id_inner_2 |
+------------+
|         11 |
+------------+
1 row in set (0.00 sec) 
+------------+
| id_inner_3 |
+------------+
|         11 |
+------------+
1 row in set (0.00 sec) 
Query OK, 0 rows affected (0.00 sec) 

mysql>

触发器
触发器是一种特殊的存储过程。当对某个表执行特定的操作时，触发器会被自动触发，执行在触发器中编译好的相关操作。而存储过程和函数，需要主动调用才会被执行。
 语法：
   CREATE TRIGGER trigger_name
   trigger_time trigger_event
   ON table_name
   FOR EACH ROW
   trigger_body;
trigger_name:
   指定应用触发器的表,只能是永久表,不可以是临时表或视图。
trigger_time:
   BEFORE, AFTER 指定触发事件在行改变之前还是之后执行。
trigger_event:
   INSERT,当插入新纪录是触发,如insert, load data, replace语句
   UPDATE, 当行被更新时触发。
   DELETE,表中的行被删除时触发。如delete和replace语句，而drop和truncate不会。
trigger_body:
   触发器被激活时执行的语句操作。
    
 在同一个表上不能创建具有相同触发时间和触发事件的触发器，如在表a上不能有两个BEFORE UPDATE触发器。但是可以有一个BEFORE UPDATE和一个AFTER UPDATE触发器。
 在触发器中可以使用OLD和NEW引用相关表中的列。
OLD.col_name引用在update或delete之前存在的列，列是只读的，不能更改；
NEW.col_name引用在update或insert之后存在的列，可以对其进行更改。
在BEFORE触发器中，AUTO_INCREMENT列的NEW值为0，不是实际插入新纪录时将自动生成的序列号。
 创建触发器的用户需要拥有TRIGGER权限，在mysql库的host, user, db, tables_priv表中设置。
当在触发器中使用OLD.col_name或NEW.col_name时，需要拥有相关表上的SELECT权限；
当在触发器中使用SET NEW.col_name = value操作时，需要拥有相关表上的UPDATE权限；
 如果在存储过程中对一个表执行了insert操作，就不能同时在触发器中对这个表执行与INSERT相关的操作。否则会提示与存储过程/函数相关的错误。

相关SHOW命令

SHOW CREATE EVENT event_name;
SHOW CREATE PROCEDURE procedure_name;
SHOW CREATE TRIGGER trigger_name;
SHOW CREATE TABLE table_name;
SHOW EVENTS;
SHOW PROCEDURE STATUS [like_or_where];
SHOW TABLE STATUS [FROM db_name] [like_or_where];
SHOW TRIGGERS [FROM db_name] [like_or_where];

示例

每5分钟调度一次事件，执行存储过程对表进行插入操作，同时触发触发器对此表进行备份。
启动mysql事件调度器
#在my.cnf的[mysqld]下添加，event_scheduler=1
mysql> use db_test;
Database changed
#创建事件e_5minute
#每5分钟调用一次存储过程p_up_t1
mysql> show events;
Empty set (0.00 sec)
mysql> create event if not exists e_5minute
    -> on schedule every 5 minute
    -> comment 'this e will call procedure p_up_t1'
    -> do call p_up_t1(@id);
Query OK, 0 rows affected (0.00 sec)
#建表t_t1,t_t2
#t_t2表是t_t1的备份表,对表t_t1的insert、update操作，都会应用到表t_t2上。
mysql> create table if not exists t_t1
    -> (
    ->  id int(3)
    -> )$
Query OK, 0 rows affected (0.00 sec)
mysql> create table if not exists t_t2
    -> (
    ->  id int(3)
    -> )$
Query OK, 0 rows affected (0.00 sec)
#创建存储过程
#向表db_test.t_t1中添加新记录，值为t_t1表的count(*)
mysql> delimiter $
mysql> drop procedure p_up_t1$
Query OK, 0 rows affected (0.00 sec)
mysql> create procedure p_up_t1
    -> (inout id int(3))
    -> begin 
    ->   select id as id_old_value;
    ->   select count(*) into id from t_t1;
    ->   select id as id_cout_t1;
    ->   insert into t_t1 values(id);
    ->   select id as id_count_t1_new;
    -> end$
Query OK, 0 rows affected (0.00 sec)
#创建触发器tri_t1
#把表t_t1中的列更新到表t_t2
mysql> drop trigger tri_t1$
Query OK, 0 rows affected (0.00 sec)
mysql> create trigger tri_t1
    -> after insert
    -> on t_t1
    -> for each row
    -> begin
    ->  insert into t_t2 set id = new.id;
    -> end$
Query OK, 0 rows affected (0.02 sec)
#触发器:更新t_t1表时，会级联更新t_t2表
mysql> create trigger tri_up_t1
    -> after update
    -> on t_t1
    -> for each row
    -> begin
    ->  update t_t2 set id = new.id
    ->   where id = old.id;
    -> end$
Query OK, 0 rows affected (0.02 sec)


#表t_t1,t_t2都为空
mysql> select count(*) from t_t1
    -> union
    -> select count(*) from t_t2$
#调用存储过程p_up_t1,向t_t1中添加记录,并更新t_t2
mysql> call p_up_t1(@id)$
+--------------+
| id_old_value |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)
+------------+
| id_cout_t1 |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)
+-----------------+
| id_count_t1_new |
+-----------------+
|               0 |
+-----------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
#查看表t_t1,t_t2中的内容是否一致
mysql> select * from t_t1
    -> union
    -> select * from t_t2$
+------+
| id   |
+------+
|    0 |
+------+
1 row in set (0.00 sec)
#更新表t_t1，会级联更新t_t2
mysql> select * from t_t2$
+------+
| id   |
+------+
|    0 |
+------+
1 rows in set (0.00 sec)
mysql> update t_t1
    -> set id=2
    -> where id=0$
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from t_t2$
+------+
| id   |
+------+
|    2 |
+------+
1 rows in set (0.00 sec)
mysql>