MySQL: Insert multiple values if they don't exist, but need a multiple column check -

i have simpe query so:

 insert mytable (col1, col2) values    (1,2),   (1,3),   (2,2) 

i need check no duplicate values have been added check needs happen across both column: if value exists in col1 , col2 don't want insert. if value exists in 1 of columns not both then insert should go through..

in other words let's have following table:

 +-------------------------+  |____col1____|___col2_____|  |      1     |     2      |  |      1     |     3      |  |______2_____|_____2______| 

inserting values (2,3) , (1,1) allowed, (1,3) not allowed.

is possible where not exists check single time? may need insert 1000 values @ 1 time , i'm not sure whether doing where check on every single insert row efficient.

edit: add question - if there's duplicate value across both columns, i'd query ignore specific row , continue onto inserting other values rather throwing error.

what might want use either primary key or unique index across columns. afterwards, can use either replace into or insert ignore:

create table mytable (     int,     b int,     primary key (a,b) );  -- variant 1 replace mytable(a,b) values (1, 2);  -- variant 2 insert ignore mytable(a,b) values (1,2); 

see insert ignore , replace into

using latter variant has advantage don't change record if exists (thus no need rebuild index) , best match needs regarding question.

if, however, there other columns need updated when inserting record violating unique constraint, can either use replace into or insert ... on duplicate key update.

replace into perform real deletion prior inserting new record, whereas insert ... on duplicate key update perform update instead. although 1 might think result same, why there statement both operations, answer can found in side-effects:

replace into delete old record before inserting new one. causes index updated twice, delete , insert triggers executed (if defined) and, important, if have foreign key constraint (with on delete restrict or on delete cascade) defined, constraint behave same way if deleted record manually , inserted new version later on. means: either operation fails because restriction in place or delete operation gets cascaded target table (i.e. deleting related records there, although changed column data).

on other hand, when using on duplicate key update, update triggers fired, indexes on changed columns rewritten once and, if foreign key defined on update cascade 1 of columns being changed, operation performed well.

to answer question in comments, stated in manual:

if use ignore modifier, errors occur while executing insert statement ignored. example, without ignore, row duplicates existing unique index or primary key value in table causes duplicate-key error , statement aborted. ignore, row discarded , no error occurs. ignored errors may generate warnings instead, although duplicate-key errors not.

so, violations treated warnings rather errors, causing insert complete. otherwise, insert applied partially (except when using transactions). violations of duplicate key, however, not produce such warning. nonetheless, records violating constraint won't inserted @ all, ignore ensure valid records inserted (given there no system failure or out-of-memory condition).