MySQL REPLACE statement – When to use it

MySQL implements an useful function – REPLACE INTO –  that developers should use to avoid coding logic already implemented at the DB level.

That function works exactly as an INSERT (same syntax), but it also performs a DELETE on the existing record when a duplicate-key occurs (same PRIMARY KEY or UNIQUE KEY on another column) in the same one atomic operation.
Note:  UNIQUE KEYS allows multiple NULL values. In that case there is no key violation and no replacement.
Note: DELETE operations are counted in the number of affected rows.
Note: the DELETE  triggers the innoDb “ON DELETE” .

Example: simple table with settings (containing pairs key->value)

CREATE TABLE IF NOT EXISTS `settings` (

`key` varchar(50) NOT NULL,
`value` varchar(255) NOT NULL,
PRIMARY KEY (`key`)
)

If we want to update a setting or create it when not existing, only one query is needed:

REPLACE INTO `settings` (`key`,`value`) VALUES ('version', 1)