mysql insert主键_MySQL INSERT（如果不存在）（不使用主键）

how can i insert into a row if the pair does not exist?

* NOTE these are not primary keys, my primary KEY is set to auto increment

tried insert ignore but did not work

INSERT IGNORE INTO mytable (`myid`, `theirid`) VALUES ('5', '1')

ON DUPLICATE KEY

table looks like:

CREATE TABLE `mytable` (

`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

`myid` bigint(20) NOT NULL,

`theirid` bigint(20) NOT NULL,

`activated` tinyint(1) NOT NULL DEFAULT '0',

`dateStamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1$$

解决方案

1) Can you add a UNIQUE constraint on (myid, theirid)? If yes, add this constraint and use:

INSERT INTO mytable (myid, theirid)

VALUES (5, 1) ;

and ignore the produce warnings (or replace the above with INSERT IGNORE)

2) If you can't add such a constraint (e.g. you sometimes want to allow such duplicates and other times you don't), you can use this:

INSERT INTO mytable (myid, theirid)

SELECT 5, 1

FROM dual

WHERE NOT EXISTS

( SELECT *

FROM mytable

WHERE myid = 5

AND theirid = 1

) ;