Detailed explanation of view update in mysql

The updateability of the view is related to the definition of the query in the view

1. Those in MySQL that try to make it non-updatable? The following types of views are not updatable

1. SQL statement containing the following keywords: aggregate function (sum, min, max, count), distinct, group by, having, union or uinon all

2. Constant view

3.select Contains subquery

4.join

5.from a non-updatable attempt

6.The subquery of the where clause refers to the from clause Table

2. UpdateView condition restrictions

##WITH [CASCADED | LOCAL] CHECK OPTION determines the conditions for updating the view.

LOCAL means that as long as the conditions of this view are met, it can be updated

##CASCADED Then all view conditions for this view must be met before it can be updated

If it is not clear whether it is local or cascade, the default is cascade

##In order to facilitate understanding, I will illustrate it through examples

## The known data structure of the t3 table is as follows:

##

-- 创建视图 ldq_t1
CREATE VIEW ldq_t1 AS 
SELECT 
  * 
FROM
  t3 
WHERE id1 > 10 WITH CHECK OPTION ;
-- 查询ldq_t1中的所有结果
SELECT * FROM ldq_t1;

-- 创建视图 ldq_t2
CREATE VIEW ldq_t2 AS 
SELECT 
  * 
FROM
  ldq_t1 
WHERE id1 < 30 WITH LOCAL CHECK OPTION ;

-- 创建视图 ldq_t3
CREATE VIEW ldq_t3 AS 
SELECT 
  * 
FROM
  ldq_t1 
WHERE id1 < 30  WITH  CHECK OPTION ;

-- 更新视图ldq_t2（只有ldq_t2中存在的数据都可以更新）
SELECT * FROM ldq_t2; -- 查看ldq_t2当前记录
UPDATE ldq_t2 SET id1=5 WHERE id2=22;  -- 可以执行成功
UPDATE ldq_t2 SET id1=35 WHERE id2=22;  -- 将会报错CHECK OPTION failed(因为执行该语句之后，id2=22记录将从ldq_t2消失)
UPDATE ldq_t2 SET id1=28 WHERE id2=22;  -- 可以执行成功


-- 更新ldq_t3
SELECT * FROM ldq_t3;
UPDATE ldq_t3 SET id1=5 WHERE id2=22;  -- 将会报错CHECK OPTION failed（因为数据更新之后，必须还要保证其仍然在ldq_t3和ldq_t1之中,
该语句执行后id2=22记录将从ldq_t1消失）
UPDATE ldq_t3 SET id1=15 WHERE id2=22; -- 能够执行成功
UPDATE ldq_t3 SET id1=35 WHERE id2=22; -- 将会报错CHECK OPTION failed（因为执行该语句之后，id2=22记录将从ldq_t3消失）
DELETE FROM  ldq_t3 WHERE id2=22;  -- 执行成功

Summary: WITH When updating a view modified with LOCAL CHECK OPTION, you only need to ensure that the updated record still exists in the view, and no error will be reported. When

WITH CASCADED CHECK OPTION modifies the view, you must ensure that the updated records still exist in the view and the views related to the view.

The above is the detailed content of view update in mysql. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!