视图和存储程序

视图

使用:

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