触发器以静默方式忽略/删除INSERT上的重复条目 [英] Trigger to silently ignore/delete duplicate entries on INSERT

问题描述

我有以下表格：
T（ID主键，A，B）

我想让对（A，B）我不想对它们有约束唯一（A，B），因为它会插入错误。
相反，我希望MySQL默认忽略这样的插入。

我无法使用插入重复键忽略，因为我无法控制客户端的查询。

那么，我可以建立这样的触发器吗？或者也许有一些约束，允许静默忽略？

编辑：我挖了，我想我想要的东西像SQLite的

解决方案

>在mysql 5.5之前。不可能在触发器中停止插入。那里有一些丑陋的工作，但没有我会推荐。 5.5以后，您可以使用 SIGNAL 进行操作。

 分隔符// 
下拉触发器如果存在aborting_trigger // 
创建触发器aborting_trigger插入到t 
之前对于每行
 begin 
 set @found：= false; 
从t中选择true到@found，其中a = new.a和b = new.b; 
 
 if @found then 
 signal sqlstate'45000'set message_text ='duplicate insert'; 
 end if; 
 end // 
 
 delimiter; 
  

I have the following table: T(ID primary key, A, B)

I want to have pair (A, B) unique but I don't want to have constraint unique(A,B) on them because it will give error on insert. Instead I want MySQL to silently ignore such inserts.

I can't use "insert on duplicate keys ignore" because I can't control client's queries.

So, can I build such trigger? Or maybe there is some constraint that allows silent ignore?

Edit: I dug around and I think I want something like SQLite's "Raise Ignore" statement.

解决方案

Before mysql 5.5. it wasn't possible to stop an insert inside a trigger. There where some ugly work arounds but nothing I would recommend. Since 5.5 you can use SIGNAL to do it.

delimiter //
drop trigger if exists aborting_trigger //
create trigger aborting_trigger before insert on t
for each row
begin
  set @found := false;
  select true into @found from t where a=new.a and b=new.b;

  if @found then
    signal sqlstate '45000' set message_text = 'duplicate insert';
    end if;
  end   //

delimiter ;

这篇关于触发器以静默方式忽略/删除INSERT上的重复条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！