跟我一起学习MySQL技术内幕（第五版）：（第四章学习日记2）

4.2.3触发器

触发器是与特定表相关联的存储过程，其定义会在执行表的insert、delete 或update语句时，被自动激活。触发器可被设置成语句处理各行之前或之后激活。触发器的定义包含有一条会在触发器被激活时执行的语句。

触发器具有以下几个好处。

1、触发器可以检查或修改将被插入或用来更新那些新的数据值。这意味着我们可以利用触发器来实现根据完整性约束、如检查某个百分比值是不是落在了0~100这样一个区间内。还可以用来过滤数据。

2、触发器可以基于某个表达式来为列提供默认值，甚至可以为那些只能使用常量默认值进行定义的列类型提供值。

3、触发器可以在行删除或更新之前先检查行的当前内容。这种能力能完成许多任务，如记录已有行的更改情况。

要创建触发器，可以使用create trigger 语句。在触发器的定义里，需要指明：触发它的语句类型（insert、update、delete）以及是在行被修改之前触发，还是在之后触发。创建触发器语句的基本语法如下所示：

create trigger trigger_name 
    {before | after}
    {insert | update | delete}
    on table_name 
    for each row trigger_stmt;

其中，trigger_name 是触发器的名字，table_name 是与触发器相关联的那个表的名字。在给触发器命名时，可以在名字中体现触发器的用途和相关联的表，如bi_table_name 表示的是一个与tbl_name 表有关的before insert触发器，而ai_table_name 表示的则是一个与table_name 表有关的after insert 触发器。

trigger_stmt是触发器的语句体，即再触发器被激活时需要执行的语句，在触发器的语句体里，可以使用new.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 
      if new.percent <0 then
        set new.percent = 0 ;
      elseif new.percent >100 then
        set new.percent = 100 ;
      endif;
    end$ new.dt = current_timestamp;
    end$
delimiter;

这个触发器将完成以下两个动作.

1.如果要插入的百分比值超出了 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);

检索整个表则会发现 表中percent列对应的值为 0 、 30、 100

触发器属于表，因此你必须要拥有表的trigger权限，才能为表创建或删除触发器。

触发器创建操作具有与存储函数创建操作一样的约束。

4.2.4事件

4.2.4事件
MySQL有一个事件阅读器，它可以定时激活多个数据库操作。时间就是一个与计划相关联的存储程序。计划会定义事件执行的时间或次数，并且还可以定义事件何时强行退出。事件非常适合于执行那些无人值守的系统管理任务，如汇总报告定期更新、旧数据过期清理或者日志表轮换。

默认情况下，事件调度器并不会运行，因此必须先启用它才能使用事件。请把下面两行内容放到某个在服务器启动时才会被读取的选项文件里：

[mysqld]
event_scheduler =on

如果想在运行时查看事件调度器的状态，那么可以使用下面这条语句：

show variables like ‘event_scheduler’;

如果想在运行时停止或启动事件调度器，那么可以更改event_scheduler 系统变量的值（它是一个global变量，因此必须要拥有super权限才能修改它）

set global event_scheduler = off; #或者0
set global event_scheduler = on ; #或者1

如果你停止了调度器，则所有事件都不会运行。也可以让事件调度器保持运行，但禁用各个事件。

如果在服务器启用时把变量event_scheduler 设置成disabled，那么在运行时只能查看其状态，而不能更改它。你可以创建事件，但不会执行。

事件调度器会把执行情况写到服务器的错误日志里，通过它，你可以查看到事件调度器正在做什么。在事件执行期间，只要有错误发生，事件调度器都会把它们记录下来。如果事件调度器没有如预期的那样运行，那么查看错误日志里的消息，从而找出具体的原因。

创建事件的方法是，使用create event 语句，其基本语法如下：

create event event_name
on schedule 
{at datetime | every expr interval [starts datetime] [ends datetime]}
Do event_stmt

事件属于数据库，因此你必须要拥有数据库的event权限才能创建或删除其他触发器。

下面这个示例演示的是：如何创建一个简单的事件，并用它来删除表里的旧行。假设有一个名为web_session的表，其存储的内容是与网站访问用户相关的会话状态信息，并且该表有一个名为last_visit的datetime列，用于记录每位用户的最近一次访问时间。为防止表里的行越积越多，可以创建一个事件，用于定期清理它们。为实现事件每四小时执行一次，同时将超过一天的行清除掉，可以编写如下事件定义：

create event expire_web_session
on  schedule every 4 hour 
do 
delete from web_session
where last_visit 
其中every n interval 子句用于指定事件定期执行的时间间隔。Interval 值 与date_add()函数里的参数值（hour、day、month）相似。在every子句的后面，还可以包含可选子句starts datetime和 end datetime 用于指定时间的第一次和最后一次执行时间。默认情况下，every事件在被创建后会立刻开始它的第一次执行，并且会定时持续执行下去，永不停止。
do子句负责定义事件的语句体部分，它是一条在事件被触发时执行的SQL语句。与其它类型的存储程序一样，它可以是一条简单的语句，也可以是一条使用begin和end编写出的复合语句。
如果想要创建一个只执行一次的事件，则应该使用at调度类型，而不要使用every。像下面的定义便会创建一个在一个小时后只执行一次的事件：
create event one_shot
on schdule at current_timestamp + interval 1 hour
do ... ;
如果要禁用某个事件，让它不再执行，或者要重新激活某个被禁用的事件，可以使用alter_event :
alter event event_name disable;
alter event event_name enable;
4.3视图和存储程序的安全性
在定义视图时，需要设置一条用于以后调用的select语句。在定义存储程序时，也有相同的操作，即需要定义一个以后执行的对象。这里的”以后执行”预示着实际执行这些对象的用户并不一定就是当初创建它们的那个用户，而这会引出一个很重要的问题：服务器在执行时应该使用什么样的安全上下文来检查访问权限呢？也就是说，应该使用哪个账户的权限呢？
默认情况下，服务器会使用定义该对象的那个用户的账户。假设我定义了一个存储过程p（）,

用于访问我的表。如果我把p（）的execute权限授予你，那么你便可以使用call p（）来调用该过程，进而访问我的表，因为它是以我的权限来运行的。这种类型的安全上下文有利有弊：
1.有利的一面是：精心编写的存储程序可以把表开放给那些无法直接访问它们的用户，并可对访问进行控制。

2.有弊的一面是：如果某个用于创建了某个可以访问到敏感数据的存储程序，但却忘记了其他可以调用该对象的人也会访问到那些敏感数据，则会造成安全漏洞。
在定义存储程序或视图时，显式指定定义者的方法是，在该对象的create语句里包含一个definer = account子句。这样，这里列出的账户就能跟定义者完全一样，从而达到在执行时检查访问权限的目的。例如：
create defner = ‘sampadm’@’localhost’ procedure count_students()
select count(*) from student;
在definer子句里，定义者的具体值可以是格式为‘user_name’@‘host_name’的账户名，该格式与账户管理语句（如 create user）里所用的格式一样。对于这种格式，其中的user_name 和host_name必不可少。此外，那个具体值还可以是current_user或current_user（）。它表明定义者就是执行这条create语句的那个用户的账户。如果没有给出definer子句，则默认使用同一个账户。
如果你拥有super权限，则可以使用任何一种语法正确的账户名来作为definer值。当账户不存在时，会出现一条警告消息。如果你没有super权限，则只能把定义者设置为自己的账户。既可以使用完整的账户名，也可以使用current_user。
对于视图和存储例程（包括存储函数和存储过程），还可以使用sql security 特性，用于实现对执行时访问检查的附加控制。Sql security 特性的允许值为definer（以定义者的权限执行）或invoker（以对象调用者的权限执行）
适合使用sql security invoker的场合是：只想以调用者所用的权限来执行视图或存储程序。例如，下面这个视图将访问mysql数据库里的某个表，但是是以调用者的权限来运行。这样一来，如果调用者本人无权访问mysql.user表，那么即使他访问这个视图，也无法突破权限限制。
create sql security invoker view v
as seslect concat(user，‘@’，host) as account,password from mysql.user; 
服务器会自动调用触发器和事件，所以这里的”调用者”概念对他们并不适用。因此，他们没有sql security 特性，总是以定义者的权限来执行。
如果某个视图或存储程序以定义者的权限来运行，但那个定义者账户并不存在，那么会出现一个错误。
在视图或存储程序里，current_user（）函数会默认返回与该对象的definer属性相对应的账户。对于视图和定义时带有sql security invoker 特性的存储例程，current_user（）会返回调用用户的账户。