MySQL错误:1395无法从联接视图中删除 [英] MySQL Error: 1395 Can not delete from join view

问题描述

我正在尝试从由多个表的联接生成的视图中删除一条记录.我有一个新用户要删除并在此特定视图上插入.我能够将记录插入到视图中，但不能从视图中删除.您能否在下面的这条SQL语句中指出我一个错误?

I am trying to delete a record from a view which was generated by join of multiple tables. I have a new user to delete and insert on this particular view. I am able to insert the records to the view but not able to delete from view. Could you please point me an error in this below piece of SQL statements?

create view v1 as
select a.* 
from appearance a, photo p, photographer u, person s
where a.isShownIn = p.id
and p.takenBy = u.id
and u.id = s.id
and s.name = 'Fred';

create user 'Fred';

grant insert, delete on assignment_5.v1 to 'Fred';


delete from v1 where v1.shows = 17;`

外观表包含show和isShownIn列.

The appearance table has shows and isShownIn columns.

推荐答案

MySQL文档指出:

The MySQL documentation states:

要使视图可更新，必须存在一对一关系 在视图中的行和基础表中的行之间."

"For a view to be updatable, there must be a one-to-one relationship between the rows in the view and the rows in the underlying table."

MySQL的性能达到了设计水平，并阻止了您在这里开枪.本质上，将从视图中删除的行数与从基础表中删除的行数不匹配.另外，您要删除照片，外观，人物或摄影师吗?还是全部?还是其中一些? MySQL也不确定，因此不允许该操作.

MySQL is performing as-designed, and is preventing you from shooting yourself in the foot here. Essentially, the number of rows that would be deleted from your view does not match the number of rows that would be deleted from the underlying tables. Also, do you want to delete the photo, appearance, person or the photographer? Or all of them? Or just some of them? MySQL doesn't know for sure either, so it doesn't allow the operation.

请牢记，请运行以下查询:

Bearing that in-mind, run this query:

SELECT IS_UPDATABLE
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'v1';

如果结果不是是"，则您可能要考虑重新设计视图.另一种选择是直接从基础表中删除.

If the result is anything other than "YES", then you might want to consider redesigning your view. The other option, would be to delete from the underlying tables directly.

使用"WITH CHECK OPTION"子句创建可更新的视图也是一个好主意.除了满足视图的WHERE子句中定义的条件的表外，这可以防止对基础表的UPDATE或INSERT.或者您的情况是，防止弗雷德弄乱鲍勃的照片.

Also it's a good idea for an updatable view to created using the "WITH CHECK OPTION" clause. This prevents UPDATEs or INSERTs to underlying tables, except those which meet the conditions defined in the view's WHERE clause. Or in your case, prevent Fred from messing with Bob's photos.

这篇关于MySQL错误:1395无法从联接视图中删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！