MySQL必备触发器

ゞ浴缸里的玫瑰 2023-02-23 05:09 1阅读 0赞

### 文章目录 ###

*  触发器的基本概念
 *  触发器的创建
 *   *  触发条件
     *  触发时机
     *  对象new和old
     *  查看触发器
 *  删除触发器
 *   *  使用触发器制作日志
     *  触发器的使用限制
 *  利用错误突破使用限制
 *  事件
 *   *  事件的基本概念
     *  事件的创建
     *  查看事件
 *  事件调度器
 *  单独开启/禁用某个事件
 *  MySql时间单位
 *  事件删除
 *  总结 触发器

# 触发器的基本概念 #

`触发器是和表相关联的一个数据库对象，是一种特殊的存储过程。`  
例如:某个表发生一个事件（增删改操作），然后自动的执行预先编译好的 SQL 语句，执行相关操作。  
这样保证了数据的完整性。  
`触发器关键字： trigger`  
基本作用：通过对表进行数据的插入、更新或删除等操作来触发，就会执行特定的存储过程

# 触发器的创建 #

可以字MySQL命令界面 ? create trigger 命令查看创建触发器的格式。

create trigger trigger_name #[触发器名] 
       触发时机 #触发时机:before|after 
       触发条件 #触发条件:insert|update|delete 
       on tbl_name #表名 
       for each row #对每一行 
       trigger_body #执行的操作

总的来说就是对表中的某一行进行插入、删除、更新时触发某一触发器来执行某些操作

## 触发条件 ##

> **insert 在插入表的时候触发  
> update 在更新表的时候触发  
> delete 在删除表的时候触发**

## 触发时机 ##

before 在…之前  
after 在…之后  
数据库一共有六种触发器，分别为：

before insert #在数据插入之前 
    after insert #在数据插入之后 
    before update #在数据更新之前 
    after update #在数据更新之后 
    before delete 
    after delete

例如：利用触发器实现检查约束 check  
对于下表创建一个触发器,规定年龄不能低于0岁且不能高于100岁，低于0岁的用0代替，高于100岁的用100代替。  
**创建表格**

create table test_tb( 
        id int primary key auto_increment, 
        age decimal(3,0) #decimal是一种浮点型，可规定小数位数 
     );

**创建触发器**

create trigger tir_test1_insert 
    before insert on test1 for each row 
    begin 
       if new.age < 0 then set new.age = 0; 
       elseif new.age >100 then set new.age = 100; 
       end if; 
    end;

创建一个触发器，实现上述功能

## 对象new和old ##

`数据库给触发器提供了两个对象 new 和 old 分别记录新值和旧值`，例如：update更新数据时会把新的数据覆盖之前的数据，那new对象就是保存的新数据，而old对象是保存被覆盖之前的数据。  
那么相对于insert而言是没有old的，因为插入之前是没有旧数据  
相对于delete而言是没有new的,只删除原来存在的数据,不添加新数据

> new：当触发插入和更新事件时可用，指向的是被操作的记录  
> old： 当触发删除和更新事件时可用，指向的是被操作的记录

`insert 插入时 new 表示插入的数据`  
`update 更新时,new表示新的数据,old表示的是原数据`  
`delete 删除时, old 表示的是删除的数据`  
触发器在 8.0 以上的新版本中不允许使用临时表,也就是不允许写上 select 语句

## 查看触发器 ##

MySQL触发器都是存在 information\_shema.triggers 表中的，所以查看触发器需要查询information\_shema 数据库中的 trigger 表.

# 查看所有触发器 
    show triggers from 数据库;

# 删除触发器 #

drop trigger 触发器名;

drop trigger student_ins;

## 使用触发器制作日志 ##

**创建日志表**

create table review_tb (
        id int primary key auto_increment, 
        username varchar(64), 
        action_name varchar(20), 
        actiom_time datetime 
    )engine innodb default charset utf8;

**创建触发器**

create trigger test_review_insert 
    after insert 
    on test1 for each row 
    begin 
       insert into review values(null,user(),'insert',now()); 
    end;

## 触发器的使用限制 ##

不是任何语句都可以用触发器来执行的

1.  触发器不能使用存储过程和函数，也不能使用select和call等动态的sql语句。
2.  触发器中不能使用开始和结束事务。
3.  触发器不能写得太复杂，否则没改变一行，所执行的任务就太多了。

# 利用错误突破使用限制 #

虽然触发器中不能使用select查询数据，不能直接通过参数返回,但是可以通过用户变量带回数据。  
例如：创建触发器带回一个参数

create trigger test_review_id 
    after insert on test1 for each row 
    begin 
       select new.age from test1 
       where new.id = id into @ages; 
    end;
       select @ids;

另外，触发器中只要有一条语句出现错误，整个触发器就不会执行。  
如：

create trigger test_review_idname 
    after insert on test1 for each row 
    begin 
       select new.id from test1 into @ids; 
       select new.action_name from test into @actions; 
       select '错误';#这里的@ids和@actions值会是什么？ 
    end;
       select @ids; #这里的@ids和@actions值没有被改变 
       select @actions; #这就是事务处理，利用错误实现回滚

# 事件 #

## 事件的基本概念 ##

与触发器类似，都是在特定的条件执行相应的操作，  
但是不同的是，触发器是触发时执行任务，而事件是定时执行任务的。

1.  事件关键字： event
2.  基本作用：让数据库定时执行某些操作。

## 事件的创建 ##

可以通过 ? create event 命令查看创建触发器的格式。

#if not exists不存在就创建 
    create event [if not exists] 事件名称 
    ON schedule #计划任务 
    (定时操作:AT timestamp 时间点，EVERY interval 间隔多久) 
           [ON COMPLETION [NOT] PRESERVE] # 事件到期处理 
           (事件执行完之后默认保留，加一个NOT为删除) 
           [enable | disable | disable on slave] #事件的状态 
           (开启 | 关闭) #相当于你设置的闹钟是打开还是关闭的 
           [COMMENT 'string'] # 这是注释，不用管 
           DO event_body; # 执行的操作 
                  schedule: # 任务计划参数解析 
           AT timestamp [+ INTERVAL interval] ... 
    # AT 时间点 [+ 时间间隔] 
    （如：AT 2020-6-6 8:30:10，2020年6月6号8点30分10秒） 
    | EVERY interval 
      [STARTS timestamp [+ INTERVAL interval] ...] 
      [ENDS timestamp [+ INTERVAL interval] ...]

**创建事件的流程**

create event [if not exists] 事件名 
    on schedule 执行的操作 时间点 ##执行的操作: every | at 
    [starts] 时间点 ##到什么时间开始 
    [ends] 时间点 ##到什么时间结束 
    do 
     语句操作

every 间隔多久

1.  start \[开始时间 \[+ 时间间隔\]\]
2.  end \[结束时间 \[+ 时间间隔\]\]

current\_timestamp 开启时间

时间计算操作

interval 1 day + interval 30 second;

例如：对于以下表，创建一个事件，每隔6秒钟自动插入一条数据。  
4. 创建事件测试表

create table event_table ( 
        id int primary key auto_increment, 
        insert_time datetime 
    )engine innodb default charset utf8;

select * from event_table; # 查看表中数据

4.创建一个事件，每隔6秒自动插入一条数据

create event insert_event 
    on schedule every 6 second 
    do
    insert into event_table values(null,now()); 
    #这里只有一条语句可以不用begin end;

## 查看事件 ##

查看所有事件的状态

show events;

# 事件调度器 #

事件是由事件调度器管理的，所以需要打开事件调度器才能执行事件。

1.  查看事件调度器的状态 \#事件调度器状态是一个会话变量

show variables like '%SCHEDULE%';

1.  设置事件调度器的的状态 \# 通过会话变量打开或关闭

set global event_scheduler = on; # 或者=1，打开调度器
    set global event_scheduler = off; # 或者=0，关闭调度器

1.  查看进程列表  
    事件调度器开启后是有由一个进程来执行的  
    show processlist; \# 查看进程列表  
    小练习：每隔一分钟清空表数据

create event truncate_event 
    on schedule 
    every 1 minute 
    do
    truncate table event_table;

# 单独开启/禁用某个事件 #

开启和关闭事件调度器会使所有的事件都同时开启或关闭，那如何对单个事件进行开启或关闭呢？

1.  单独禁用某个事件  
    alter event insert\_event disable;
2.  单独开启某个事件  
    alter event insert\_event enable;

事件调度器像是一个总开关而每个单独的事件都有一个自己独立的开关

#删除表格内所有的数据 
    #这种删除方式比delete快,但是不能进行where筛选 
    truncate table 表格名称

小练习1：创建一个清空表事件，7天之后开启事件,每天清空表,一个月后停止并删除事件。

create event clear1_event 
    on schedule 
    every 1 day # 每隔1天 
    starts current_timestamp + interval 7 day # 7天后开启 
    ends current_timestamp + interval 1 month # 1月后停止 
    on completion not preserve # 停止后不保留事件 
    do
    truncate table event_table;

小练习2：创建一个清空表事件，固定时间点清空表数据

create event clear2_event 
    on schedule 
    at '2020-3-15 23:59:59' + interval 3 day # 固定时间点 
    do # 可用interval关键字加上间隔时间 
    truncate table event_table;

# MySql时间单位 #

数据库中的时间单位有：year年、month月、day日、week星期、hour小时、minute分钟、second秒钟、microsecond微秒。  
`second 秒`  
`minute 分`  
`hour 时`  
`day 天`  
`month 月`  
`year 年`

# 事件删除 #

drop event 事件;

查看事件的具体执行语句

show create event 运行的事件;

# 总结 触发器 #

1.  通过触发器对增删改进行逻辑操作  
    通过触发器进行数据备份  
    进行事务监控
2.  事件  
    每隔一段时间执行固定的操作  
    制作一个定时器