MySQL可更新视图

灰太狼 2022-05-19 09:59 264阅读 0赞

可更新视图是指通过视图，来更新、插入、删除基本表中的数据。视图是一个虚拟表，即对视图的更新，实质上是更新基表。但是视图的构造很多时候是由多个表连接查询，以及结合聚合函数，分组过滤等等定义的。对于这类的视图，想要去更新，恐怕就显得力不从心了。因为涉及到多张表。本文简要描述可更新视图的特点并给出演示。

### 一、不带check option更新 ###

-- 当前环境
    mysql> show variables like 'version'; +---------------+--------+
    | Variable_name | Value | +---------------+--------+
    | version | 5.7.17 | +---------------+--------+
    -- 可更新视图演示
    
    DROP TABLE IF EXISTS items;
    
    CREATE TABLE items
    (
     id INT AUTO_INCREMENT PRIMARY KEY,
     name VARCHAR(100) NOT NULL,
     price DECIMAL(11, 2) NOT NULL
    );
    
    -- 为items表填充数据
    
    INSERT INTO items(name, price)
    VALUES ('Laptop', 700.56), ('Desktop', 699.99), ('iPad', 700.50);
    
    CREATE OR REPLACE VIEW vw_items AS SELECT * FROM items WHERE price > 700; -- 查询视图
    
    SELECT * FROM vw_items; -- 以下语句插入成功，基表和视图同时可见
    
    INSERT INTO vw_items VALUES (NULL, 'iPhone', 800.50); -- 以下语句插入成功，基表可见，因为视图包含了where子句对其过滤
    
    INSERT INTO vw_items VALUES (NULL, 'iPhone4', 500.50); -- Query OK, 1 row affected (0.00 sec)
    
    SELECT * FROM vw_items;

### 二、基于check option更新 ###

-- 先清空一下数据
    
    TRUNCATE TABLE items;
    
    CREATE OR REPLACE VIEW vw_items_check AS SELECT * FROM items WHERE price > 700 WITH CHECK OPTION;
    
    -- 下面基于vw_items_check创建另外一个视图vw_items_check2
    CREATE OR REPLACE VIEW vw_items_check2 AS SELECT * FROM vw_items_check WHERE price < 1000 WITH LOCAL CHECK OPTION;
    
    -- 下面基于vw_items_check创建另外一个视图vw_items_check3
    CREATE OR REPLACE VIEW vw_items_check3 AS SELECT * FROM vw_items_check WHERE price < 1000 WITH CASCADED CHECK OPTION;
    
    -- 基于视图vw_items_check插入数据，以下语句插入失败，不符合视图过滤条件
    INSERT INTO vw_items_check VALUES (NULL, 'Laptop', 600.56);
    
    -- ERROR 1369 (HY000): CHECK OPTION failed 'sakila.vw_items_check'
    
    -- 基于视图vw_items_check插入数据，以下语句执行成功
    -- 满足where子句过滤条件，插入后基表和视图数据可见
    INSERT INTO vw_items_check VALUES (NULL, 'Laptop', 700.56);
    
    -- 基于视图vw_items_check2插入数据成功，值符合过滤条件
    INSERT INTO vw_items_check2 VALUES (NULL, 'iPhone', 800.50);
    
    -- 基于视图vw_items_check3插入数据成功，值符合过滤条件
    INSERT INTO vw_items_check3 VALUES (NULL, 'iPhone3', 800.50);
    
    -- 下面使用一个不符合预期的值进行插入
    -- 基于视图vw_items_check2插入数据失败，值不符合底层过滤条件
    INSERT INTO vw_items_check2 VALUES (NULL, 'iPhone_chk2', 700);
    
    -- ERROR 1369 (HY000): CHECK OPTION failed 'sakila.vw_items_check2'
    
    -- 基于视图vw_items_check3插入数据失败，值不符合底层过滤条件
    INSERT INTO vw_items_check3 VALUES (NULL, 'iPhone_chk2', 700);
    
    -- ERROR 1369 (HY000): CHECK OPTION failed 'sakila.vw_items_check3'
    
    -- 通过上面的测试发现，使用CASCADED与LOCAL创建的视图都会检查底层依赖
    -- 在此并无特别
    -- 说明5.7.6版本以前，视图vw_items_check2不符合底层预期时，也可以成功执行

### 三、进一步测试对比CASCADED与LOCAL ###

-- 再次创建如下视图，此时的视图底层基于非check视图
    
    CREATE OR REPLACE VIEW vw_items_check4
    AS
     SELECT *
     FROM vw_items
     WHERE price < 1000
     WITH LOCAL CHECK OPTION;
    
    CREATE OR REPLACE VIEW vw_items_check5
    AS
     SELECT *
     FROM vw_items
     WHERE price < 1000
     WITH CASCADED CHECK OPTION;
    
    -- 基于视图vw_items_check4插入数据成功，值不符合底层过滤条件
    -- 但是此时可以成功插入，说明local生效，不依赖底层过滤条件    
    INSERT INTO vw_items_check4
    VALUES (NULL, 'iPhone_chk4', 700);    
    
    Query OK, 1 row affected (0.00 sec)
    
    -- 下面验证插入结果，查询vw_items_check4被过滤
    SELECT * FROM vw_items_check4; +----+---------+--------+
    | id | name | price | +----+---------+--------+
    | 1 | Laptop | 700.56 |
    | 2 | iPhone | 800.50 |
    | 3 | iPhone3 | 800.50 | +----+---------+--------+
    
    -- 查询基表数据存在
    SELECT * FROM items; +----+-------------+--------+
    | id | name | price | +----+-------------+--------+
    | 1 | Laptop | 700.56 |
    | 2 | iPhone | 800.50 |
    | 3 | iPhone3 | 800.50 |
    | 4 | iPhone_chk4 | 700.00 | +----+-------------+--------+
    
    -- 基于视图vw_items_check5插入数据失败，cascade级联校验生效
    INSERT INTO vw_items_check5
    VALUES (NULL, 'iPhone_chk5', 700);
    
    -- ERROR 1369 (HY000): CHECK OPTION failed 'sakila.vw_items_check5'

### 四、基于视图删除 ###

-- 基于视图vw_items_check4删除数据
    -- 如下，提示删除成功，但基表数据未删除，因为不满足过滤条件
    DELETE FROM vw_items_check4
    WHERE id = 4;
    
    -- Query OK, 0 rows affected (0.00 sec)
    
    -- Author : Leshami
    -- Blog : http://blog.csdn.net/leshami
    
    -- 基于视图vw_items_check5删除数据
    -- 如下，提示删除成功，但基表数据未删除，因为不满足过滤条件
    DELETE FROM vw_items_check5
    WHERE id = 4;
    
    -- Query OK, 0 rows affected (0.00 sec)
    
    -- 满足过滤条件 id为3的记录能够被删除
    DELETE FROM vw_items_check5
    WHERE id = 3;
    
    -- Query OK, 1 row affected (0.00 sec)
    
    -- 删除后的结果
    SELECT * FROM items; +----+-------------+--------+
    | id | name | price | +----+-------------+--------+
    | 1 | Laptop | 700.56 |
    | 2 | iPhone | 800.50 |
    | 4 | iPhone_chk4 | 700.00 | +----+-------------+--------+

### 五、更新视图 ###

-- 由于不符合过滤条件，2个视图均无法更新
    UPDATE vw_items_check5 SET price = 701 WHERE id = 4;    
    
    Query OK, 0 rows affected (0.00 sec)
    Rows matched: 0 Changed: 0 Warnings: 0
    
    UPDATE vw_items_check4 SET price = 701 WHERE id = 4;   
    
    Query OK, 0 rows affected (0.00 sec)
    Rows matched: 0 Changed: 0 Warnings: 0
    
    -- 基于视图vw_items5更新数据，此时选择满足条件的记录来更新
    -- 更新为比过滤条件低的价格，无法成功更新
    UPDATE vw_items_check5 SET price = 700 WHERE id = 2; 
    
    ERROR 1369 (HY000): CHECK OPTION failed 'sakila.vw_items_check5'
    
    -- 更新为符合条件时，被成功更新
    UPDATE vw_items_check5 SET price = 900 WHERE id = 2;
    
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1 Changed: 1 Warnings: 0

### 六、cascade 与local的差异(官方描述) ###

-- 未指定local与cascade时，缺省为cascade
    -- 官方给出的关于LOCAL与CASCADED对比
    /*
    
    • With LOCAL, the view WHERE clause is checked, then checking recurses to underlying views and
    applies the same rules.
    
    • With CASCADED, the view WHERE clause is checked, then checking recurses to underlying views,
    adds WITH CASCADED CHECK OPTION to them (for purposes of the check; their definitions remain
    unchanged), and applies the same rules.
    
    • With no check option, the view WHERE clause is not checked, then checking recurses to underlying
    views, and applies the same rules.
    */

### 七、结论： ###

1、不使用check子句情形，可以对视图进行DML操作，影响基表数据  
2、使用check子句情形，所有的DML必须满足过滤条件，否则报错，update语句更新后的值不符合过滤条件则无法更新  
3、LOCAL与CASCADED选项受底层视图影响，如果底层视图带check，则两者作用相同，否则LOCAL进作用于当前

我的博客即将搬运同步至腾讯云+社区，邀请大家一同入驻：[https://cloud.tencent.com/developer/support-plan?invite\_code=2tchhwnywegwc][https_cloud.tencent.com_developer_support-plan_invite_code_2tchhwnywegwc]

[https_cloud.tencent.com_developer_support-plan_invite_code_2tchhwnywegwc]: https://cloud.tencent.com/developer/support-plan?invite_code=2tchhwnywegwc