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进作用于当前