MySQL触发器与视图
触发器
-
trigger，在表的插入（insert）、更新（update）、删除（delete）操作发生时自动执行 MySQL 语句。
-
它可以在操作之前（BEFORE）或之后（AFTER）自动触发反应。
作用
- 实现数据的自动验证和控制。
- 自动维护数据一致性（如自动更新相关字段）。
- 实现审计（记录操作日志）。
- 实现复杂的业务逻辑。
举例
创建触发器时需要给出的信息：
- 触发器名
- 在操作前还是操作后触发（before/after）
- 被什么操作触发（insert/update/delete）
- 关联的表
使用触发操作的数据：
- insert：可通过 new 访问被插入的行，before insert 可以更新 new 中的值（允许更改被插入的值）。
- delete：可通过 old 访问被删除的行，old 中的值是只读的。
- update：old 访问更新前的值，new 访问更新后的值
语法：
# 创建触发器
create trigger 触发器名 before|after update|insert|delete on 表名 for each row
begin|set 触发器逻辑
end; #删除触发器
drop trigger [if exists] 触发器名;例子：创建一个在插入员工后，记录日志的触发器
-- 假设有如下两个表：
-- 1. 员工表
create table employees (id int auto_increment primary key,name varchar(100),position varchar(50),hire_date date
);-- 2. 操作日志表
create table employee_logs (log_id int auto_increment primary key,employee_id int,action varchar(20),action_time timestamp default current_timestamp
);-- 创建触发器：在插入员工后，记录日志
create trigger trg_after_insert_employee
after insert on employees
for each row
begininsert into employee_logs (employee_id, action)values (NEW.id, 'INSERT');
end;
- 说明：
- 触发器名称：trg_after_insert_employee
- 触发时机：AFTER INSERT，插入员工后触发
- 作用：在employee_logs表中插入一条记录，记录新员工ID和操作类型“INSERT”。
使用示例：
insert into employees (name, position, hire_date) values ('Alice', 'Engineer', '2023-06-01');
执行上述插入后，employee_logs表中会自动增加一条类似：
log_id | employee_id | action | action_time
---------------------------------------------------1 | 1 | 'INSERT'| 当前时间
例子：set类型，这个触发器在插入新员工数据前，如果没有工资信息，则自动设置工资为3000。
create trigger trg_before_insert
before insert on employees
for each row
set NEW.salary = IFNULL(NEW.salary, 3000);
- begin与set的不同
| 特性 | BEGIN ... END | SET |
|---|---|---|
| 用途 | 包含多条SQL语句，形成操作块 | 赋值语句，只执行一条赋值操作 |
| 用法范围 | 必须在存储过程、触发器的定义中使用，作为多语句块 | 单独一条操作，不作为多语句的容器 |
| 能执行内容 | 多条SQL语句（插入、更新、删除、条件判断等） | 赋值操作（变量、字段、参数） |
| 替换可能性 | 不能用SET替代BEGIN ... END，二者用途不同 | 不能用BEGIN ... END包裹或替代SET，二者目的不同 |
视图
-
view，由查询结果形成的一个虚拟的表。视图不能索引，也不能有关联的触发器。
-
视图是基于一条或多条SQL查询定义的虚拟表，实际上不存储数据。
-
它像一个“窗口”一样，显示的是底层表的部分或全部内容。
作用
-
重用 SQL 语句
-
简化复杂查询：可以把复杂的联结和筛选定义成视图，提高开发效率。
-
提高安全性：通过视图隐藏敏感字段，只显示用户有权限的数据。
-
提升数据抽象：给底层数据结构封装，方便维护。
实现原理
- 临时表算法：将视图的查询结果存放到临时表里，需要访问视图时，直接访问这个临时表，优点是可以处理复杂查询，缺点是引入了创建表的性能开销
- 合并算法：重写含有视图的查询，将视图的定义sql直接合并到查询sql里，性能更高。
MySQL 优化器根据查询的具体情况来选择使用哪种算法。如果视图中包含GROUY BY、DISTINCT、聚合函数等，只要无法在原表记录和视图记录中建立一一映射的场景中， MySQL都将使用临时表算法来实现视图。
举例
- 可更新视图：可以通过更新这个视图来更新视图涉及的相关表。只要指定了合适的条件，就可以更新、删除甚至向视图中写入数据。
#合并算法
create view abc as select * from emp;
explain select * from abc;
#临时表算法
create view dept_emp as select * from emp group by deptno;
explain select * from dept_emp;
#可更新视图
insert into abc(empno) values (111);
update abc set empno=222 where empno=111;
insert into dept_emp(empno) values (111);
#不可更新视图
create view abc2 as select deptno from emp;
insert into abc2(deptno) values (111);create view abc2 as select empno from emp;
insert into abc2(empno) values (111);create view abc2 as select deptno * 2 from emp;
update abc2 set deptno=222 where deptno is NULL;例子：这个视图显示薪资高于5000的员工。
create view high_salary as
select name, salary from employees where salary > 5000;
- 视图名字：high_salary_employees
你可以通过简单的select来查看这个视图：
select * from high_salary_employees;
二者区别
| 特征 | 触发器（Trigger） | 视图（View） |
|---|---|---|
| 性质 | 存储的过程（自动执行） | 虚拟表（查询定义） |
| 作用对象 | 作用于数据操作（DML：INSERT, UPDATE, DELETE） | 作用于数据查询（SELECT） |
| 是否存储数据 | 不存储数据，只存储逻辑 | 不存储数据，仅存储定义 |
| 使用场景 | 自动化业务逻辑、数据完整性约束、审计 | 简化复杂查询、权限控制、数据封装 |
| 触发时机 | 在特定DML操作前后触发 | 查询时动态生成，非触发器 |