Mysql, replace into and on duplicate key update

Programming

04 Sep 2015

Mysql, replace into and on duplicate key update

Alex Stupak 0 Comments

If you need to do “update or create” like in django ORM Model.objects.update_or_create(defaults=None, **kwargs). You can use at least 3 ways:
1) Check whether there is a record in the table, if not create the record, if there is update.

2) REPLACE

3) INSERT INTO ON DUPLICATE KEY UPDATE

 

The first method does not work for high load systems without locks and plus it’s slow.
The second and third method is similar, but there is one important point:

If you don’t know primary key statement REPLACE will delete record appropriate query and create new with new id 

once statement INSERT INTO ON DUPLICATE KEY UPDATE update the record without changes primary key.