问题:

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.

回答1:

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 ;