存储程序(2)——MYSQL

时间：2022-01-20 16:10:49

1.触发器

触发器是与特定数据表相关联的存储过程，当相应的数据表被INSERT、DELETE或UPDATE语句修改时，触发器将自动执行。触发器可以被设置成在这几种语句处理每个数据行之前或之后触发。触发器的定义包括一条将在触发器被触发时执行的语句。
下面描述了触发器提供的一些好处：

1.触发器可以检查或修改将被插入或用来更新数据行的新数据值。这意味着我们可以利用触发器强制实现数据的完整性，比如检查某个百分比数值是不是落在了0到ioo的区间内。触发器还可以用来对输入数据进行必要的过滤。
2.触发器可以把表达式的结果赋值给数据列作为其默认值。这使我们可以绕开数据列定义里的默认值必须是常数的限制。
3.触发器可以在删除或修改数据行之前先检查它的当前内容。这种能力可以用来实现许多功能，例如把对现有数据行的修改记载到一个日志里。

触发器要用CREATE TRIGGER语句来创建。在触发器的定义里需要表明它将由哪种语句(INSERT,UPDATE或DELETE)触发，是在数据行被修改之前还是之后被触发。触发器创建语句的基本语法如下所示:

CREATE TRIGGER trigger_name            #the trigger name

    {BEFORE | AFTER}                   #when the trigger activates

    {INSERT | UPDATE | DELETE}         #what statement activates it

    ON tbl_name                        #the associated table

    FOR EACH ROW trigger_stmt;         #what the triaaer does

trigger_stmt是触发器的语句体部分，也就是在触发器被触发时将要执行的语句。在触发器的语句体里，可以使用NEW.col_name语法来引用将由INSERT或UPDATE语句插人或修改的新数据行里的数据列。类似地，OLD.col_name语法可以用来引用将由DELETE或UPDATE语句删除或修改的老数据行里的数据列。比如说，如果你想在新数据行被插人数据表之前改变它的某个数据列的值，只需创建一个BEFORE触发器并在其语句体里写出相应的“SET NEW.col name =value”语句即可。

在下面的例子里，我们为数据表t上的INSERT语句创建了一个名为bi_t的触发器。数据表t有一个整数类型的percent数据列用来保存百分比数值(0到100)和一个DATETIME数据列。我们在定义这个触发器时使用了BEFORE关键字，所以它将在数据值被插人数据表之前对它们进行检查:

CREATE TABLE t(percent INT, dt DATETIME);

delimiter $

CREATE TRIGGER bi_t BEFORE INSERT ON t

FOR EACH ROW

BEGIN

    SET NEW.dt= CURRENT_TIMESTAMP;

    IF NEW.percent < 0 THEN

        SET NEW.percent=0;

    ELSEIF NEW.percent > 100 THEN

        SET NEW.percent=100;

    END IF;

END$

delimiter ;

这个触发器将完成如下两个动作:
1.如果将被插人的百分比值超出了0到100的范围，这个触发器将把该值转换为最近的边界值(0或100)。

2.这个触发器将自动地为那个DATETIME数据列提供一个CURRENT_TIMESTAMP值。从效果上看，这绕开了“数据列的默认值必须是一个常数”的限制，让DATETIME数据列具备了类似于TIMESTAMP数据列的自动初始化能力。

INSERT INTO t (percent) VALUES(-2); DO SLEEP(2);

INSERT INTO t (percent) VALUES(30); DO SLEEP(2);

INSERT INTO t (percent) VALUES(120);

SELECT*FROM t;

先在数据表里插人一些数据行，然后再检索它的内容，结果为：

存储程序(2)——MYSQL

2.事件

MySQL 5.1.6及更高版本有一个事件调度器，它使我们可以把数据库操作安排在预定时间执行。
事件是与一个时间表相关联的存储程序，时间表用来定义事件发生的时间、次数以及何时消失。事件非常适合用来执行各种无人值守的系统管理任务，如定期更新汇总报告、清理过期失效的数据、对日志数据表进行轮转等。这里主要演示如何对过期失效的数据行进行处理。

在默认的情况下，事件调度器不会运行。如果你想使用事件，必须先启用事件调度器。把以下语句添加到一个选项文件中(服务器在启动时将读取):

[mysqld]

event_scheduler=ON

如果你想在系统运行时查看事件调度器的状态，可以使用这条语句:

SHOW VARIABLES LIKE 'event_scheduler';

如果你想在系统运行时停止或启动事件调度器，可以通过改变event_scheduler系统变量的值来达到目的(它是一个GLORAT变量，你必须拥有SUPER权限才能修改它):

SET GLOBAL event scheduler = OFF;    #or 0

SET GLOBAL event scheduler = ON;     #or 1

如果你停止了事件调度器，就没有事件可以运行了。你也可以让事件调度器保持运行，但禁用各事件稍后将讨论（DISABLED）。

说明：如果你在启动服务器时把event_scheduler变量设里为DISABLED，在系统运行时，你将只能查看它的状态，不能改变它的状态。在此基础上，你仍可以创建事件，但它们将不能执行（所有事件DISABLED）。

事件调度器将把它的执行情况写到服务器的“错误”日志里，你可以从这个日志查到关于事件调度器正在干什么的信息。它会把它运行的每一个事件以及在事件执行过程中发生的错误记载到日志里。如果你认为事件调度器应该正在运行、可它实际上并没有运行，请到“错误”日志里找找原因。
下面的例子演示了如何创建一个简单的事件来删除数据表里“老”数据行。假设你有一个名为web_session的数据表，其内容是访问你网站的用户的会话状态信息。这个数据表有一个名为last_visit的DATETIME数据列，记录着每位用户最近一次的访问时间。如果不想让这个数据表里的老数据行越积越多，就创建一个事件来定期清理它们。若要让这个事件每隔4小时执行一次，就把超过一天的数据行清除掉。下面是相应的事件定义:

CREATE EVENT expire_web_session

    ON SCHEDULE EVERY 4 HOUR

    DO

        DELETE FROM web_session WHERE last_visit<TCURRENT_TIMESTAMP - INTERVAL 1 DAY;

EVERY n interval子句用来给出事件定期执行的时间间隔。interval值近似于DATE_ADD()函数里的参数值，它可以是HOUR、DAY或MONTH。在EVERY子句的后面，你还可以用STARTS datetime和ENDS datetime选项给出事件第一次和最后一次执行的时间。在默认情况下，EVERY事件将在它被创建后立刻开始它的第一次执行，并将一直定期执行下去，没有“最后一次”的说法。

DO子句负责定义事件的语句体部分，也就是将在事件被触发时执行的SQL语句。和其他类型的存储程序一样，这可以是一条简单的语句，也可以是一条以BEGIN开始、以END结束的复合语句。

如果想创建一个只执行一次的事件，就应该使用AT调度类型而不是EVERY。如下所示的定义将创建一个只执行一次的事件，在一个小时后执行:

CREATE EVENT one_ shot

ON SCHEDULE AT CURRENT_TIMESTAMP+INTERVAL 1 HOUR

DO ...;

如果你想禁用某个事件，让它不再定期执行，或者重新激活某个已被禁用的事件，请使用ALTER EVENT语句:

ALTER EVENT event_name DISABLE;

ALTER EVENT event_name ENABLE;

每个事件都隶属于某个数据库，所以你必须拥有那个数据库的EVENT权限才能为它创建或删除事件。

相关文章