视图和存储程序

视图

• 虚拟表

使用：

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或者子查询，则都会使用临时表实现

临时表

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 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 来指定定义者