MySQL可更新视图

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

一、不带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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏用户画像

新闻发布 sql server代码

834
来自专栏黑泽君的专栏

day05_MySQL学习笔记_02

    注:每个表中至少要有一个主键。 主键用于唯一地标识表中的每一条记录,可以定义一列或多列为主键。 

962
来自专栏数据和云

MySQL - 8种常见的SQL错误用法

前言:MySQL在2016年仍然保持强劲的数据库流行度增长趋势。越来越多的客户将自己的应用建立在MySQL数据库之上,甚至是从Oracle迁移到MySQL上来。...

4354
来自专栏xiaoheike

为什么 EXISTS(NOT EXIST) 与 JOIN(LEFT JOIN) 的性能会比 IN(NOT IN) 好

网络上有大量的资料提及将 IN 改成 JOIN 或者 exist,然后修改完成之后确实变快了,可是为什么会变快呢?IN、EXIST、JOIN 在 MySQL 中...

5044
来自专栏栗霖积跬步之旅

第四章:数据检索

表名:products 字段:product_id、product_name、product_price、vend_id(供应商) 1.检索单个列: SELEC...

19610
来自专栏Java后端生活

MySQL(八)DQL之连接查询

1808
来自专栏逸鹏说道

sql server之数据库语句优化

一切都是为了性能,一切都是为了业务 一、查询的逻辑执行顺序 (1) FROM left_table (3) join_type JOIN right_table...

3657
来自专栏battcn

MySQL - WHERE优化篇

在做JAVA开发中,通过指令重拍会对代码做一定程度的优化,在数据库中 MYSQL优化器也做了一系列相关优化工作,下面要介绍的就是数据库做的内置优化

1222
来自专栏java相关

使用shell脚本生成数据库markdown文档

学习shell脚本编程的一次实践,通过shell脚本生成数据库的markdown文档,代码如下:

1975
来自专栏程序员宝库

Mysql - JOIN 详解

一个完整的SQL语句中会被拆分成多个子句,子句的执行过程中会产生虚拟表(vt),但是结果只返回最后一张虚拟表。从这个思路出发,我们试着理解一下JOIN查询的执行...

4013

扫码关注云+社区

领取腾讯云代金券