mysql conditional insert?

I have trawled through tens of forums now to find MySql syntax to insert a row if there is no other row in the table that contains a give value.

I know it has to be simple but so far have found nothing that does what I need...

My requirement is simply:

if not exists (select * from table1 where int_value2 = 123) then insert into table1 (value1, int_value2, value3) values ('a', 1, 'a');

I apologise for how simple I know this is going to be but thanks in advance for any help开发者_运维百科 you can offer.

Define a UNIQUE constraint if it not already exists:

ALTER TABLE table1  ADD UNIQUE(int_value2);

INSERT IGNORE INTO table1 (value1, int_value2, value3) VALUES ('a', 1, 'a');

Note the 'IGNORE` bit.

If you need 'fresh' data in value1/value3, you could look at ON DUPLICATE KEY UPDATE.

INSERT INTO table1 (value1, int_value2, value3)
SELECT 'a', 1, 'a' FROM DUAL 
WHERE NOT EXISTS(SELECT * FROM table1 WHERE int_value2=123);