MYSQL update or insert with PHP

Question

I need to update a record in the database, but I don't know if that record already exists. If it exists, update it, else insert it. Now I came up with this code:

1: <?php
2: $query = "UPDATE user_meta SET active = '0' WHERE user_id = '125'";
3: $mysqli->query($query);
4: 
5: if($mysqli->affected_rows == 0){
6:     $query = "INSERT INTO user_meta (user_id, active) VALUES ('125', 0)";
7:     $mysqli->query($query);
8: }

The table:

CREATE TABLE `user_meta` (
  `user_id` int(11) DEFAULT NULL,
  `active` tinyint(4) DEFAULT NULL,
  UNIQUE KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This code is working fine for me, but it actually generates an error which I neglect.

The problem is in this line 5. The affected_rows is 0 if nothing is changed, even if the record already exists. But this will trigger the INSERT statement. This one fails because the user_id field is unique, now I'm ignoring this so my code is working fine.

But is this the way to go? Or should I do first a select and than update or insert?

Answer 1

You can solve this using INSERT ... ON DUPLICATE KEY

Using this method will be transactionally safe, as MySQL is handling it in one implicit transaction. Your current method allows a split moment where two concurrent requests could interfere with one another.

Assuming you're on MySQL 5.7: https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

Your query would look like this:

INSERT INTO user_meta SET user_id = 125, active = 0
  ON DUPLICATE KEY UPDATE active = 0

---

Update: Just to elaborate on why your current method is transactionally unsafe, imagine two concurrent requests executing in parallel.

1. Request 1 attempts an update, sees 0 affected rows.
2. Request 2 attempts an update, sees 0 affected rows.
3. Request 1 inserts a new record.
4. Request 2 inserts a new record.

Whether you explicitly start and complete a transaction, or whether you implicitly do (e.g. through INSERT ... ON DUPLICATE KEY) MySQL will take the responsibility for ensuring that the aforementioned faulty scenario doesn't occur by blocking a second request until the first finishes.

Answer 2

MySQL has a built-in solution for this - the on duplicate key update clause:

INSERT INTO user_meta (user_id, active) 
VALUES (125, 0)
ON DUPLICATE KEY UPDATE active = 0

Answer 3

use mysql ON DUPLICATE KEY UPDATE to check the record is already exist in the db or not. Your table must have a column having unique type for this. Here your user_id column is unique type. Check the documention here

INSERT INTO user_meta (user_id, active) 
VALUES (125, 0)
ON DUPLICATE KEY UPDATE active = 0