视图和存储程序

视图

使用:

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视图的实现:

屏幕截图 2021-03-23 161026

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

临时表

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

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

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

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

内部临时表

以下查询会用临时表:

  1. union:需要比对去重,所以需要临时表,union all 没有比对去重,则不需要
  2. 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();

触发器

-- 插入前检验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 来指定定义者