CODEWITHSUNDEEP
mysqlinsertlocking
benjamin.cohen-solal
benjamin.cohen-solal

Reputation: 481

How to insert into a mysql table only if not already existing?

A very simple question but I don't have the answer. I would like to insert a record into a table, but only if it doesn't already exist.

These are my tables:

I would like to insert into table2 only if the value to insert (unik) doesn't exist.

My first intuitions was to lock for update:

START TRANSACTION;
SELECT * FROM table2 WHERE unik = :unik FOR UPDATE;
IF (NOT EXISTS) 
  INSERT INTO table1 SET ...;
  INSERT INTO table2 SET idTable1 = LAST_INSERT_ID(), ...;
COMMIT;

But if the value doesn't exist, it won't lock anything and if two scripts run together, they will insert the same record.

My second intuition was to insert on duplicate key:

INSERT INTO table1 SET ...;
INSERT INTO table2 SET idTable1 = LAST_INSERT_ID(), ... ON DUPLICATE KEY UPDATE idTable1 = idTable1;

But I already have to insert into table1 before table2 so what if I insert into table1 if finally nothing will be inserted into table2?

Upvotes: 1

Views: 253

Answers (3)

nemenems
nemenems

Reputation: 1092

And why not doing something like:

START TRANSACTION

... DO SOME STUFF HERE
SAVEPOINT 'before_rollback';
INSERT INTO table1 SET ...;
INSERT INTO table2 SET idTable1 = LAST_INSERT_ID(), ... ON DUPLICATE KEY UPDATE idTable1 = idTable1;

# A record exists, rollback to remove record in table1
if (affected_rows == 1)
{
   rollback 'before_rollback';
}
else
{
   commit;
}

Be careful: maybe MySQL will not "release" the consumed autincrement (depending of your MySQL conf: https://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html)

Upvotes: 1

nemenems
nemenems

Reputation: 1092

What about this? (or something like this)

INSERT INTO table1 (id) (   
    SELECT * FROM (SELECT NULL) TMP 
    WHERE NOT EXISTS (SELECT 1 FROM table2 WHERE uniq = :uniq)
)

Upvotes: 0

Robert
Robert

Reputation: 2824

u can use count to see if there are or not like this

select count(*) thecount from table2 where unik = unikvalue

unikvalue is the value U want to search if exist or not
thecount here will have the number of row that founded

the general syntax is

SELECT COUNT(column_name) FROM table_name;

Upvotes: 0

Related Questions