《MySQL触发器、级联操作》

不念不忘少年蓝@ 2023-01-02 11:27 152阅读 0赞

# MySQL触发器、级联操作 #

## 1.1 触发器 ##

### 1.1.1 触发器介绍 ###

触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作 。

*  MySQL中触发器共有3种：

<table> 
 <thead> 
  <tr> 
   <th>触发器类型</th> 
   <th>NEW和OLD的使用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>insert触发器</td> 
   <td>NEW 表示将要或者已经新增的数据</td> 
  </tr> 
  <tr> 
   <td>update触发器</td> 
   <td>OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据</td> 
  </tr> 
  <tr> 
   <td>delete触发器</td> 
   <td>OLD 表示将要或者已经删除的数据</td> 
  </tr> 
 </tbody> 
</table>

> 使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容

### 1.1.2 触发器语法 ###

create trigger trigger_name [after/before] [insert/update/delete] 
    on table_name
    for each row
    begin
    	......
    end;

\*\*after：\*\*在插入表之后执行

\*\*before：\*\*在插入表之前执行（两种触发器效果一致）

建立一张日志表，存储日志

CREATE TABLE log  (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `message` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `time` datetime(0) NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

*  insert触发器

create trigger test1 after insert
    on student
    for each row
    begin
    	insert into log values(null,new.name,now());
    end;
    
    insert into student values(6,'王八',3);
    
    select * from log;  -- 查看日志表

*  update触发器

create trigger test2 after update
    on student
     for each row
    begin
    	insert into log values(null,concat('之前的值: ',old.name,';之后的值: ',new.name),now());
    end;
    
    
    update student set name='王巴' where id=6; 
    
    select * from log;  -- 查看日志表

*  delete触发器

create trigger test3 after delete
    on student
     for each row
    begin
    	insert into log values(null,concat('删除的值: ',old.name),now());
    end;
    
    delete from student where id=6;
    select * from log;  -- 查看日志表

*  查看当前数据库中的触发器

show triggers;

*  删除触发器

drop trigger trigger_name;
    
    -- 示例
    drop trigger test1;

## 1.2 级联 ##

我们在表与表之间存在关系时，一般都是通过外键来关联关系的，外键维护着我们表与表之间的关系，级联就是当一方数据发生表更时（delete、update），另一方的数据应该保持着何种关联，是删除还是一起修改，还是置空呢？

我们把原来的数据库删除，重新创建：

CREATE TABLE `class`  (
      `id` int(11) not null auto_increment primary key,
      `name` varchar(30)
    ) ;
    
    INSERT INTO `class` VALUES (1, 'Java');
    INSERT INTO `class` VALUES (2, 'UI');
    INSERT INTO `class` VALUES (3, '产品');
    
    
    CREATE TABLE `student`  (
      `id` int(11) NOT NULL auto_increment primary key,
      `name` varchar(20) ,
      `class_id` int(11) NULL DEFAULT NULL,
      foreign key (`class_id`) references `class` (`id`) on delete set null on update set null
    ) ;
    
    INSERT INTO `student` VALUES (1, '张三', 1);
    INSERT INTO `student` VALUES (2, '李四', 1);
    INSERT INTO `student` VALUES (3, '王五', 2);
    INSERT INTO `student` VALUES (4, '赵六', 1);
    INSERT INTO `student` VALUES (5, '孙七', 3);

### 1.2.1 外键的种类 ###

*  删除级联：
    
     *  `on delete set null`：如果主表有删除，那么从表的数据都为null
     *  `on delete cascade`：如果主表有删除，那么从表的数据也删除
     *  `on delete no action`：如果主表有删除，从表不做任何操作
     *  `on delete restrict`：如果设置该值，主表不允许做删除操作（默认的外键行为）
 *  修改级联：
    
     *  `on update set null`：如果主表有更新，那么从表的数据都为null
     *  `on update cascade`：如果主表有更新，那么从表的数据也更新
     *  `on update no action`：如果主表有更新，从表不做任何操作
     *  `on update restrict`：如果设置该值，主表不允许做更新操作（默认的外键行为）

### 1.2.2 操作外键 ###

-- 禁用外键
    SET FOREIGN_KEY_CHECKS=0;
     
    -- 启用外键
    SET FOREIGN_KEY_CHECKS=1;
     
    -- 查看当前MySQL服务器是否开启外键
    SELECT  @@FOREIGN_KEY_CHECKS;

*  创建外键：

-- 建表时写(1):
    foreign key (`class_id`) references `class` (`id`) on delete set null on update set null
    
    -- 建表时写(2):
    constraint `student_ibfk_1` foreign KEY (`class_id`) references `class` (`id`) on delete set null on update restrict
    
    -- 使用SQL语句添加外键
    alter table student add constraint `stu_fk_1` foreign key(`class_id`) references `class`(`id`) on delete set null on update restrict;

*  删除外键：

alter table student drop foreign key stu_fk_1;