MySQL - insert if doesn't exist yet

Question

I want to execute this MySQL query:

INSERT INTO `cron-stats` (`user`) VALUES (".(int)$d['by-user'].")

Whenever such user doesn't exist yet, as in:

SELECT 1
FROM `cron-stats`
WHERE `user` = ".(int)$d['by-user']."

How can I execute this in one query?

Solution 1

you can use ON DUPLICATE KEY UPDATE

INSERT INTO `cron-stats` (`user`) VALUES ('yourValue')
ON DUPLICATE KEY UPDATE user = user;

• ON DUPLICATE KEY UPDATE

but in order to perform the INSERT statement well, you need to set a UNIQUE index on column user.

if the column has no index yet, execute the statement below,

 ALTER TABLE `cron-stats` ADD CONSTRAINT tb_un UNIQUE (`user`)

Solution 2

A little bit hacky, but if you use a SELECT derived table instead of VALUES you can do:

INSERT INTO `cron-stats`(`user`)
SELECT u
FROM (SELECT @dByUser AS u) x
WHERE NOT EXISTS(SELECT 1 FROM `cron-stats` WHERE `user` = @dByUser)

SQL Fiddle demo

Comments

[Frantisek]

Not sure if this helps. I'm using this in a CRON, where I'm running UPDATE for existing entries. Before the UPDATE runs, I want to prepare not existing entries by INSERTing null values. Is this the proper solution?

[John Woo]

what's the reason why you need to insert null values first?

[Frantisek]

Oh well, it doesn't work. Here's the full query with the error: pastebin.com/PyHMY9RG

[John Woo]

you have key word SET in the query which is not suppose to be there. remove it and execute it again.

[John Woo]

it's not the same. Oh, i see a WHERE clause at the end of the query. remove it also.

[John Woo]

let us continue this discussion in chat

[Brett]

This is a terrible solution since you're losing out hugely on performance benefits and ACID. You'd really need to wrap this in a transaction to make it work right, and why would you do that anyway when there are clear commands that are so much better?

[Rich S]

Interestingly, when you use the 'duplicate_key_update' statement and it prevents an insert, it still advances the AUTO_INCREMENT for that table.

[Cruncher]

Is this guaranteed to be atomic? That is, if two inserts come in at the same time, could they both pass the WHERE NOT EXISTS part and both insert?