MySQL的BEFORE INSERT触发器把重复的主键插入到更新
我试图通过phpMyAdmin来执行数据库中的这个查询MySQL的BEFORE INSERT触发器把重复的主键插入到更新
create trigger avoid_duplicated_sharing
before insert on sharingevents
for each row
begin
if (select count(*) from sharingevents where shared_note_id = NEW.shared_note_id AND shared_to = NEW.shared_to > 0) then
delete from sharingevents where shared_note_id = NEW.shared_note AND shared_to = NEW.shared_to
END IF;
END
但的phpmyadmin给了我以下错误:
MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'END IF' at line 7
两个问题:
- 我的脚本有什么问题?
- 经过
BEFORE INSERT
触发后,是否会执行INSERT
操作?如果它没有我将不得不删除INSERT INTO SharingEvents (SELECT * FROM NEW);
使用exists
:
if (exists (select 1 from sharingevents where shared_note_id = new.shared_note_id AND shared_to = new.shared_to) > 0) then
insert into sharingevents (shared_note_id,shared_to,permission_level)
values (NEW.shared_note_id,NEW.shared_to,NEW.permission_level);
end if;
或者,更好,在sharingevents(shared_note-id, shared_to)
添加唯一索引,然后使用:
insert into sharingevents (shared_note_id, shared_to, permission_level)
values (NEW.shared_note_id, NEW.shared_to, NEW.permission_level)
on duplicate key update shared_note_id = values(shared_note_id);
这将忽略表中已存在对的任何更新。否需要if
。
同一个错误:/ '#1064 - 你的SQL语法错误;请检查与您的MariaDB服务器版本相对应的手册,以便在第5行' 插入到sharingevents(shared_note_id,shared_to,permission_level)'旁边使用正确的语法' – Maximetinu
括号不平衡,您不需要使用'> 0'来测试'exists()'的结果。 – Barmar
你的第二个建议是好的,但可能需要更改他不想接触的应用程序代码,所以他使用触发器自动化了它。 – Barmar
count(shared_note_id, shared_to)
是无效的语法。当您使用count(DISTINCT ...)
时,只能在COUNT()
内放置多个列名称。就你而言,你根本不需要输入列名,只需使用COUNT(*)
来计算匹配条件的行数。
见count(*) and count(column_name), what's the diff?了解更多信息,当你应该把列名COUNT()
不幸的是,固定的语法错误不会真正解决你的问题,因为你不能使用触发器来做出改变的同一张桌子。从FAQ:
Can triggers access tables?
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.
你需要重新编写呼叫者使用INSERT ... ON DUPLICATE KEY UPDATE
,或等同的东西,做到这一点。
我已经做了这个改变,是的,这是一个错误!谢谢。但即使这样也行不通。现在它在END IF – Maximetinu
我用下面的代码解决这个问题:
delimiter $$
create trigger avoid_duplicated_sharing
before insert on sharingevents
for each row
begin
if (select count(*) from sharingevents where shared_note_id = NEW.shared_note_id AND shared_to = NEW.shared_to > 0) then
delete from sharingevents where shared_note_id = NEW.shared_note_id AND shared_to = NEW.shared_to;
end if;
END$$
问题是分隔符。
即便如此,我的触发器不起作用。当应用程序插入复制的主键的MySQL引发以下错误:
#1442 - Can't update table 'sharingevents' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
给我一个错误触发器不允许修改它们触发的同一个表。 – Barmar
你不能转动将其插入到一个更新,你把它变成相同的插入。 – Barmar
你说得对,我把查询改为:'从sharingevents中删除where(shared_note = NEW.shared_note_id AND shared_to = NEW.shared_to);' – Maximetinu
'INSERT INTO ... ON DUPLICATE KEY UPDATE'有什么问题或者INSERT IGNORE'查询?它们不适合你的用例吗? – Mjh