视图和存储程序
视图
- 虚拟表
使用：
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语句结果存放到一张临时表 查询视图时查询这张临时表
使用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 INT
BEGIN
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 来指定定义者