mysql 事件

事件的概念

事件是基于特定时间周期来执行某些任务

事件是MySQL在相应的时刻调用的过程式数据库对象。一个事件可调用一次，也可周期性的启动，它由一个特定的线程来管理的，也就是所谓的“事件调度器”

通俗讲事件和mysql触发器类似表示在某一时刻（具体的某一时刻用时间来表示）或者在某一个时间段内一次或多次的执行一段sql程序

优缺点

1）优点

一些对数据定时性操作不再依赖外部程序，而直接使用数据库本身提供的功能。可以实现每秒钟执行一个任务，这在一些对实时性要求较高的环境下就非常实用了。

2）缺点

定时触发，不可以程序调用

查看事件

1）查看事件是否被开启

show variables like 'event_scheduler'
select @@event_scheduler

2）设置事件开启

set global event_scheduler = 1;
set global event_scheduler = true;
set global event_scheduler = On
修改my.ini文件，在其中加上EVENT_SCHEDULER = 1

创建事件

事件可以通过createa event语句来创建，其语法格式为：

CREATE EVENT [if not exists] event_name(事件名) ON SCHEDULE schedule [ENABLE | DISABLE |DISABLE ON SLAVE]

DO event_body(事件主体)

其中schedule的语法格式为

AT <时间戳> [ + interval <间隔>] ....

或者

EVERY <间隔> [starts <时间戳> [+interval <间隔>]....] [ENDS <时间戳> [+ 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:指定的事件名，前面可以添加关键字IF NOT EXISTS 来修饰

<事件主体>：DO子句中的<事件主体>部分用于指定事件启动时所要求执行的代码。如果包含多条语句，可以使用BEGIN ....END 符合结构

schedule:事件调度，用于指定事件何时发生或者每隔多久发生一次，分别对应下面两个子句

（1）AT 子句：用于指定事件在某个时刻发生。其中，timestamp表示一个具体的时间点，后面可以加上一个时间间隔，表示在这个时间间隔后事件发生；interval表示这个时间间隔，由一个数值和单位构成；quantity是间隔时间的数值。

（2）EVERY子句：用于表示事件在指定时间区间内每间隔多长时间发生一次。其中，STARTS 子句用于指定开始时间，ENDS子句用于指定结束时间

event_body:Do子句中的event_body部分用于指定事件启动时所要求执行的代码。如果包含多条语句，可以使用begin....end复合结构

enable | disable | disable on slave：为可选项，表示事件的一种属性。其中，关键字enable表示该事件是活动的，活动意味着调度器检查事件动作是否必须调用；

关键字disable表示该事件是关闭的，关闭意味着事件的声明存储到目录中，但是调度器不会检查它是否应该调用

关键字disable on slave表示事件在从机中是关闭的。

创建事件，5秒钟后开始，每10秒钟执行一次，1分钟后结束

mysql> delimiter $$
mysql> create event if not exists event_insert
    -> on schedule every 10 second
    -> starts current_timestamp + interval 5 second
    -> ends current_timestamp + interval 1 minute
    -> do
    -> begin
    -> INSERT INTO tb_student VALUES(NULL,'张晓勇','男','1997-12-11','山西','汉','AC1301');
    -> end $$
mysql> delimiter ;

创建事件，10秒钟后执行

mysql> delimiter $$
mysql> create event if not exists event_insert_2
    -> on schedule at current_timestamp + interval 10 second
    -> do
    -> begin
    -> INSERT INTO tb_student VALUES(NULL,'张晓勇','男','1997-12-11','山西','汉','AC1301');
    -> end $$
mysql> delimiter ;

查看事件

select * from mysql.event

修改事件

事件被创建后可以通过alter event 语句来修改其定义和相关属性

语法格式：

ALTER 
    EVENT <事件名>
    [ON hu schedule]
    [RENAME TO <新事件名>]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [DO <事件主体>]
alter event event_insert disable;
alter event event_insert rename to e_insert;

开启事件

alter event event_insert_2 enable;

关闭事件

alter event event_insert disable;

删除事件

语法格式：

DROP EVENT [IF EXISTS] event_name

例如

drop event if exists event_insert;

存储过程与存储函数

存储过程

存储过程：一组为了完成特定功能的sql语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程中带有参数）来调用执行它。

优点：

封装性：存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的sql语句，并且数据库专业人员可以随时对存储过程进行修改，而不会影响到调用它的应用程序源代码。

可增强sql语句的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

可减少网络流量：由于存储过程是在服务器端运行，且执行速度快，那么当客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而可降低网络负载。

高性能：存储过程执行一次后，产生的二进制代码就驻留在缓冲区，在以后的调用中，只需要从缓冲区中执行二进制代码即可，从而提高了系统的效率和性能。

提高数据库的安全性和数据的完整性，使用存储过程可以完成所有数据库操作，并可通过编程方式控制数据库信息访问的权限。

创建存储过程

语法格式create procedure <过程名>（[过程参数.....]） <过程体>

[过程参数]格式

[in | out | inout]<参数名><类型>

<过程名>：存储过程的名称，默认在当前数据库中创建。需要在特定数据库中创建存储过程时，则要在名称前面加上数据库的名称，即db_name,sp_name.

[过程参数.....]:mysql存储过程支持三种类型的参数，即输入参数，输出参数和输入/输出参数，分别用in 、out和inout三个关键字标识。其中，输入参数可以传递给一个存储过程；输出参数用于存储过程需要返回一个操作结果的情形；而输入/输出参数既可以充当输入参数也可以充当输出参数。

注意：参数的取名不要与数据表的列名相同，尽管不会返回出错信息，但是存储过程中的sql语句会将参数名看作是列名，从而引发不可预知的结果

<过程体>:存储过程的主体部分，也称为存储过程体，包含了在过程调用的时候必须执行的sql语句。这个部分以关键字begin开始，以关键字end结束。若存储过程体重只有一条sql语句，可以省略 begin .... end标志。

定界符

语法格式：

DELIMITER $$

注意：

$$是用户定义的结束符，通常这个符号可以是一些特殊的符号，如两个“？”或两个"￥"

当使用DELIMITER命令时，应该避免使用反斜杠“\”字符，因为它是mysql的转义字符

mysql> delimiter $$
mysql> create procedure up_name(in sno int,in sname varchar(6))
    -> begin
    -> update tb_student set name=sname where studentno = sno;
    -> end $$
mysql> delimiter ;

调用存储过程

1. mysql> call up_name(2147483647,'王五');
2. 
3. update tb_student set name='王五' where studentno = 2147483647;

存储过程体

局部变量

在存储过程体中可以声明局部变量，用来存放产生的临时结果。

注意：局部变量，与全局变量相对应，在局部范围内有效的变量。

语法格式

declare <变量名> [,.....] <类型> [default <默认值>]

<变量名>：指定的局部变量的名称，

注意：不要和mysql的关键字、保留字、列名重复

<类型>：指定局部变量的数据类型

default子句：用于为局部变量指定一个默认值，默认为null

declare xname varchar(5) default '李明';

注意：

局部变量只能在存储过程体begin ......end语句中声明

局部变量作用范围只是在begin ...end之间

局部变量必须在存储过程体开头处声明

局部变量和用户变量的不同之处在于：局部变量声明时没有使用@符号，并且只能在begin....end范围内使用；用户变量在声明时，需要使用@符，已经声明的用户变量存在于整个会话中。

set语句

变量声明后，可以使用set语句为局部变量赋值

语法格式

set <变量名> = <表达式1> ......

set xname = '王杰'

select .... into语句

可以使用select ... into语句把选定列的值直接存储到局部变量中，存储过程体中的select ... into只能返回一行数据

语法格式

select <列名>  into <变量名> [.....] <其它>
mysql> delimiter $$
mysql> create procedure select_name(in sno int,out sname varchar(6))
    -> begin
    -> select name into sname from tb_student where studentno=sno;
    -> end $$
mysql> delimiter ;
mysql> call select_name(2013110101,@str);
mysql> select @str;
+--------+
| @str   |
+--------+
| 王小飞 |
+--------+
1 row in set

流程控制语句

1）IF ~ THEN ~ELSE

语法格式：

if <判断语句> then <语句>

[elseif <判断语句> then <语句>]

[else <语句>]

end if

mysql> delimiter $$
mysql> create procedure select_sex(in sno int)
    -> begin
    -> declare stu_sex char(2);
    -> select sex into stu_sex from tb_student where studentno=sno;
    -> if stu_sex='男' then
    -> select stu_sex,1;
    -> elseif stu_sex='女' then
    -> select stu_sex,2;
    -> end if;
    -> end$$
mysql> delimiter ;

2)case

1. case <参数>
2. when <参数> then <语句>
3. [when <参数> then <语句>]
4. [else 语句]
5. end case

mysql> delimiter $$
mysql> create procedure sel_sex(in sno int)
    -> begin
    -> declare stu_sex char(2);
    -> select sex into stu_sex from tb_student where studentno=sno;
    -> case
    -> when stu_sex='男' then select stu_sex,'1';
    -> when stu_sex='女' then select stu_sex,'2';
    -> else
    -> select stu_sex;
    -> end case;
    -> end $$
mysql> delimiter ;

游标

select ...into 语句成功执行后，会返回带有值的一行数据，这行数据可以被读取到存储过程中进行处理。但是在使用select 语句进行数据检索时，若该语句成功被执行，则会返回一组称为结果集的数据行，该结果集中可能有多行数据，这些数据无法直接被一行一行地进行处理，此时就需要用到游标；

定义：

游标是一个被select语句检索出来的结果集，在存储了游标后，应用程序或用户就可以根据需要滚动或浏览其中的数据。

注意：

游标只能用于存储过程或存储函数中，不能单独在查询操作中使用；

在存储过程或存储函数中可以定义多个游标，但是在一个begin....end语句块中每一个游标的名字必须是唯一的

游标不是一条select语句，是被select语句检索出来的结果集

mysql对游标的支持是从mysql5.0开始的，之前的mysql版本无法使用游标

语法格式：

declare <游标名> cursor for <select 语句>

打开游标

open <游标名>：指定要打开的游标

读取数据

fetch <游标名> into <变量名>......[变量2]

关闭游标

close <游标名>

在结束游标使用后，必须关闭游标。

PHP的学习与MYSQL高级 2

mysql 事件

事件的概念

优缺点

查看事件

创建事件

查看事件

修改事件

删除事件

存储过程与存储函数

存储过程

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像