mysql 执行错误1395_MySQL错误：1395无法从联接视图中删除

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

The appearance table has shows and isShownIn columns.

解决方案

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 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.

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.