视图和存储程序

视图

虚拟表

使用：

CREATE VIEW v AS SELECT ...;CREATE VIEW v(c1,c2) AS SELECT ...; -- 对SELECT结果的列重命名CREATE VIEW v AS SELECT id FROM tb WHERE i>1;UPDATE v SET i = i+1; -- 对于单张表的简单视图 是可以进行更新的

MySQL视图的实现：

合并算法：将对视图的查询条件合并到原表的查询条件上去
临时表算法：将创建视图时的SELECT语句结果存放到一张临时表查询视图时查询这张临时表

---title: 合并算法---sequenceDiagram    participant Client as 客户端    participant Server as 服务器    participant SQL1 as SQL查询    participant View as 视图    participant SQL2 as 合并查询SQL    participant Data as 底层表数据    Client ->> Server: 发起包含视图的查询    Server ->> View: 解析查询，找到对应视图    View ->> SQL1: 执行视图的SQL    SQL1 ->> SQL2: 将视图SQL和查询SQL进行合并    SQL2 ->> Data: 在底层表执行查询    Data -->> Server: 返回查询结果    Server -->> Client: 返回结果给客户端

---title: 临时表算法---sequenceDiagram    participant Client as 客户端    participant Server as 服务器    participant SQL1 as SQL查询    participant View as 视图    participant TempTable as 临时表    participant Data as 底层表数据    Client ->> Server: 发起包含视图的查询    Server ->> View: 解析查询，找到对应视图    View ->> TempTable: 生成临时表    TempTable ->> Data: 执行临时表中的查询    Data -->> TempTable: 将数据存储到临时表中    TempTable -->> SQL1: 在临时表中执行查询    SQL1 -->> Server: 返回查询结果    Server -->> Client: 返回结果给客户端

使用EXPLAIN查看视图实现方式，如果视图包含聚合函数、UNION或者子查询，则都会使用临时表实现

临时表

建表语法是 create temporary table …
一个临时表只能被创建它的 session 访问，对其他线程不可见。临时表的命名：进程id_线程id_序列号
临时表可以与普通表同名。session内有同名的临时表和普通表的时候，show create 语句，以及增删改查语句访问的是临时表
show tables 命令不显示临时表

临时表的应用：用来进行分库分表的聚合

stateDiagram-v2	direction LR	客户端 --> 临时表: 创建	库1 --> 客户端: select	库2 --> 客户端: select	库3 --> 客户端: select	临时表 --> 客户端: select

如果临时表在主从复制集群里创建，一个 session 关闭了，主库的临时表就会被自动删除，但从库则需要在主库上再写一个 DROP TEMPORARY TABLE 传给备库执行

内部临时表

以下查询会用临时表：

union：需要比对去重，所以需要临时表，union all 没有比对去重，则不需要
group by：如果聚合的字段不是有序的，也会用到临时表

临时表分为内存临时表与磁盘临时表，内存临时表的大小是由参数 tmp_table_size 决定的，对于用到临时表的查询，可以通过 SQL_BIG_RESULT 优化提示强制 MySQL 使用磁盘临时表

使用 Memory 引擎的内存表可以被用作内存临时表，内存表相比 InnoDB 表的区别在于：不支持事务、数据按插入顺序排序、只支持表锁、

存储程序

存储过程

不能用在 sql 表达式中可以返回多个结果集

CREATE PROCEDURE show_tables ()  SELECT * FROM information_schema.tables;CALL show_tables(); -- 调用存储过程CREATE PROCEDURE print_2 () -- 复合语句BEGIN	SELECT * FROM staff;	SELECT * FROM actor;END;-- 存储过程参数CREATE PROCEDURE count_people_1(OUT ret INT)BEGIN	SET ret = (SELECT COUNT(*) FROM staff);END;CALL count_people_1(@ret);SELECT @ret;

存储函数

可以有参数有返回值

存储函数不能对调用它的那条语句正操作的表进行修改

CREATE FUNCTION count_people() RETURNS INTBEGIN	RETURN (SELECT COUNT(*) FROM staff);END;SELECT count_people();

触发器

对于每张表的每个事件只能定义一个触发器
MySQL的触发器是基于行的触发

-- 插入前检验CREATE TRIGGER tri_person BEFORE INSERT ON person	FOR EACH ROW BEGIN		IF NEW.name != 'cxk' THEN			SET NEW.name = 'cxk';		END IF;END;

事件

开启事件调度：SET GLOBAL event_scheduler = ON;

-- 每秒插入一条记录CREATE EVENT insert_people	ON SCHEDULE EVERY 1 SECOND	DO		INSERT INTO person VALUES('cxk');

如果上一个事件的上一次调度没有执行完，时间一到，下一次调度就会开始，这种并发需要用户自己处理

安全性

对于视图或者存储程序

默认调用者的身份都是创建者

可以在CREATE 语句后面加上DEGINER = xxx 来指定定义者