MySQL updatable view

Updatable view is to update, insert and delete data in basic table through view. View is a virtual table, that is, the update of view, which is essentially the update base table. But the construction of views is often defined by multiple table join queries, combined with aggregate functions, group filtering, and so on. For this kind of view, if you want to update it, you may not be able to do it. Because multiple tables are involved. This paper briefly describes the characteristics of updatable views and gives a demonstration.

1, Update without check option

-- Current environment
mysql> show variables like 'version';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| version | 5.7.17 |
+---------------+--------+
-- Updatable view presentation

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
);

-- by items Table population data

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;

-- Query view

SELECT * FROM vw_items;

-- The following statement is inserted successfully, and the base table and view are visible at the same time

INSERT INTO vw_items
VALUES (NULL, 'iPhone', 800.50);

-- The following statement was inserted successfully and the base table is visible because the view contains where Clause to filter

INSERT INTO vw_items
VALUES (NULL, 'iPhone4', 500.50);

-- Query OK, 1 row affected (0.00 sec)

SELECT * FROM vw_items;

2, Update based on check option

-- Clear the data first

TRUNCATE TABLE items;

CREATE OR REPLACE VIEW vw_items_check
AS
   SELECT *
   FROM items
   WHERE price > 700
   WITH CHECK OPTION;

-- Based on vw_items_check Create another view vw_items_check2
CREATE OR REPLACE VIEW vw_items_check2
AS
   SELECT *
   FROM vw_items_check
   WHERE price < 1000
   WITH LOCAL CHECK OPTION;

-- Based on vw_items_check Create another view vw_items_check3
CREATE OR REPLACE VIEW vw_items_check3
AS
   SELECT *
   FROM vw_items_check
   WHERE price < 1000
   WITH CASCADED CHECK OPTION;

-- View based vw_items_check Insert data, the following statement failed to insert, does not meet the view filter conditions
INSERT INTO vw_items_check
VALUES (NULL, 'Laptop', 600.56);

-- ERROR 1369 (HY000): CHECK OPTION failed 'sakila.vw_items_check'

-- View based vw_items_check Insert data, the following statement executed successfully
-- satisfy where Clause filter condition, base table and view data are visible after insertion
INSERT INTO vw_items_check
VALUES (NULL, 'Laptop', 700.56);

-- View based vw_items_check2 Insert data successfully, value meets filter conditions
INSERT INTO vw_items_check2
VALUES (NULL, 'iPhone', 800.50);

-- View based vw_items_check3 Insert data successfully, value meets filter conditions
INSERT INTO vw_items_check3
VALUES (NULL, 'iPhone3', 800.50);

-- Next, insert it with a value that is not as expected
-- View based vw_items_check2 Failed to insert data, the value does not meet the underlying filter conditions
INSERT INTO vw_items_check2
VALUES (NULL, 'iPhone_chk2', 700);

-- ERROR 1369 (HY000): CHECK OPTION failed 'sakila.vw_items_check2'

-- View based vw_items_check3 Failed to insert data, the value does not meet the underlying filter conditions
INSERT INTO vw_items_check3
VALUES (NULL, 'iPhone_chk2', 700);

-- ERROR 1369 (HY000): CHECK OPTION failed 'sakila.vw_items_check3'

-- Through the above test, we found that CASCADED And LOCAL All views created check for underlying dependencies
-- Nothing special here
-- Note 5.7.6 Before version, view vw_items_check2 It can also be executed successfully if it does not meet the underlying expectations

3, Further test and comparison of CASCADED and LOCAL

-- Create the following view again. At this time, the bottom layer of the view is based on non check view

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;

-- View based vw_items_check4 Insert data successfully, the value does not meet the underlying filter conditions
-- However, it can be inserted successfully at this time, indicating that local Effective, independent of underlying filter conditions    
INSERT INTO vw_items_check4
VALUES (NULL, 'iPhone_chk4', 700);    

Query OK, 1 row affected (0.00 sec)

-- Next, verify the insertion results and query vw_items_check4 Be filtered
SELECT * FROM vw_items_check4;
+----+---------+--------+
| id | name | price |
+----+---------+--------+
| 1 | Laptop | 700.56 |
| 2 | iPhone | 800.50 |
| 3 | iPhone3 | 800.50 |
+----+---------+--------+

-- Query base table data exists
SELECT * FROM items;
+----+-------------+--------+
| id | name | price |
+----+-------------+--------+
| 1 | Laptop | 700.56 |
| 2 | iPhone | 800.50 |
| 3 | iPhone3 | 800.50 |
| 4 | iPhone_chk4 | 700.00 |
+----+-------------+--------+

-- View based vw_items_check5 Failed to insert data, cascade Cascade verification takes effect
INSERT INTO vw_items_check5
VALUES (NULL, 'iPhone_chk5', 700);

-- ERROR 1369 (HY000): CHECK OPTION failed 'sakila.vw_items_check5'

4, Delete based on view

-- View based vw_items_check4 Delete data
-- As follows, the deletion is successful, but the base table data is not deleted because the filtering conditions are not met
DELETE FROM vw_items_check4
WHERE id = 4;

-- Query OK, 0 rows affected (0.00 sec)

-- Author : Leshami
-- Blog : http://blog.csdn.net/leshami

-- View based vw_items_check5 Delete data
-- As follows, the deletion is successful, but the base table data is not deleted because the filtering conditions are not met
DELETE FROM vw_items_check5
WHERE id = 4;

-- Query OK, 0 rows affected (0.00 sec)

-- Meet filtering conditions id Records for 3 can be deleted
DELETE FROM vw_items_check5
WHERE id = 3;

-- Query OK, 1 row affected (0.00 sec)

-- Results after deletion
SELECT * FROM items;
+----+-------------+--------+
| id | name | price |
+----+-------------+--------+
| 1 | Laptop | 700.56 |
| 2 | iPhone | 800.50 |
| 4 | iPhone_chk4 | 700.00 |
+----+-------------+--------+

5, Update view

-- Both views cannot be updated because they do not meet the filter criteria
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

-- View based vw_items5 To update the data, select the records that meet the conditions to update
-- Update to a price lower than the filter condition, unable to update successfully
UPDATE vw_items_check5
SET price = 700
WHERE id = 2; 

ERROR 1369 (HY000): CHECK OPTION failed 'sakila.vw_items_check5'

-- When the update is qualified, it is successfully updated
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

6, Differences between cascade and local (official description)

--When local and cascade are not specified, the default is cascade
 --Official comparison between LOCAL and cascade
/*

• 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.
*/

7, Conclusion:

1. Without check clause, DML operation can be performed on the view to affect the base table data
2. In the case of check clause, all DML S must meet the filter conditions, otherwise an error is reported, and the updated value of update statement cannot be updated if it does not meet the filter conditions
3. The LOCAL and CASCADED options are affected by the underlying view. If the underlying view has check, the two functions are the same. Otherwise, LOCAL is used for the current

Tags: MySQL

Posted on Fri, 31 Jan 2020 10:33:51 -0500 by kaeRock