删除INSERT上的重复条目

删除INSERT上的重复条目

本文介绍了触发以静默方式忽略/删除INSERT上的重复条目的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有下面的表:
T(ID主键,A,B)



我想要对(A,B)我不想有约束唯一(A,B)对他们,因为它会给插入错误。
我希望MySQL忽略这样的插入。



我不能使用在重复键上插入忽略,因为我不能控制客户端的查询。



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



编辑:我挖了一下,我想我想要像SQLite的语句。

解决方案

Before mysql 5.5。不可能在触发器内停止插入。在那里有一些丑陋的工作,但没有什么,我会推荐。自5.5版以来,您可以使用进行操作。



分隔符//
删除触发器(如果存在)aborting_trigger //
创建触发器aborting_trigger,然后在t
上插入每行
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;


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上的重复条目的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

07-27 21:10