mysql错误1442的真正原因是什么? [英] what is the real cause of mysql error 1442?

问题描述

好吧，我在互联网上找了很多地方来寻找mysql error #1442的原因

well i have looked for a lot of places on the internet for the cause of the mysql error #1442 which says

无法更新存储的函数/触发器中的表"unlucky_table"，因为 调用此存储的语句已使用它 功能/触发

Can't update table 'unlucky_table' in stored function/trigger because it is already used by statement which invoked this stored function/trigger

有人说这是mysql中的错误或它没有提供的功能.

some say that this is a bug in mysql or a feature that it doesnt provide.

MySQL触发器无法操作分配给它们的表.所有其他主要的DBMS都支持此功能，因此希望MySQL会尽快添加此支持.

有人声称这是由于递归行为 当您插入记录时， mysql正在做一些锁定操作.您不能在同一张表中插入/更新/删除行..因为触发器将一次又一次地调用..最终以递归方式

Some claim that this is due to recursive behavior when you insert a record mysql is doing some lock stuff. you can't insert/update/delete rows of the same table where you insert.. because then the trigger would called again and again.. ending up in a recursion

在插入/更新期间，您可以访问新包含表中所有字段的对象.如果您在插入/更新之前进行编辑并在新对象中编辑要更改的字段，它将成为调用语句的一部分，而不是单独执行(消除递归)

现在我不明白为什么这是递归的.我有一种情况，我有2个表table1和table2，并且我运行SQL查询为

now i cant understand why this is recursive. i have a case in which i have 2 tables table1 and table2 and i run an sql query as

update table1 set avail = 0 where id in (select id from table2 where duration < now() - interval 2 hour);

现在我在table1上有一个after update trigger作为

CREATE TRIGGER trig_table1 AFTER UPDATE ON table1
FOR EACH ROW begin
if old.avail=1 and new.avail=0 then
delete from table2 where id=new.id;
end if;

现在，当我执行更新查询时，出现1442错误. 在这种情况下，什么是递归的?

now when i execute the update query i get a 1442 error. whats recursive in this case?

is this error a lack of feature in mysql?
OR
does this have to do with how mysql executes queries?
OR
is there something logically wrong with executing such queries?

推荐答案

更新表时不能引用表.

/* my sql does not support this */
UPDATE tableName WHERE 1 = (SELECT 1 FROM tableName)

来自 MySQL文件:

触发器可以访问其自己的表中的旧数据和新数据.触发器也可以影响其他表，但是不允许修改调用该函数或触发器的语句已经在使用(用于读取或写入)的表. (在MySQL 5.0.10之前，触发器无法修改其他表.)

A trigger can access both old and new data in its own table. A trigger can also affect other tables, but it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger. (Before MySQL 5.0.10, a trigger cannot modify other tables.)

这篇关于mysql错误1442的真正原因是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！