Create OR Update -> Mysql

Asked

Viewed 56 times

0

There is something 'native' from MYSQL that does an Update OR Create?

Something that is more performative than SELECT, validation and INSERT/UPDATE as is normally done.

I do not refer to methods that simplify this via application, my doubt is about this action under the responsibility of the database itself.

Example of how to use in Laravel:

App\Flight::updateOrCreate(
    ['departure' => 'Oakland', 'destination' => 'San Diego'],
    ['price' => 99]
);

However, this is still only an abstraction of SELECT and the condition.

On Mongodb, we have this:

Person.update(
  { name : 'TED' },
  { name : 'TED', age : 50 },
  { upsert : true }
,callback );

The parameter upsert makes update or create work.

mysql

  • I believe all these methods internally check whether there is something in the DB through some kind of query and validation, so I believe that there is no change in performance when encapsulating in a method, the advantage would be only in simplifying and avoiding repetitions.

  • Caique, the problem is that this check is already in fact a query, so it means that to update something I have to give two commands in the database, understand? Soon, it doubles the process time. For few items is imperceptible but I am working with many items and this is making enough difference.

2 answers

1

1


If you have a primary key or a single index, you can use the event ON DUPLICATE KEY, would look something like this:

INSERT INTO `sua_tabela` (`campo1`, `campo2`)
VALUES ('informação1', 'informação2')
ON DUPLICATE KEY UPDATE `campo1` = 'informação1', `campo2` = 'informação2';

See more here.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.