Change record status: 
Project: 
Introduced in branch: 
8.0.x
Introduced in version: 
8.0.0-beta15
Description: 

In order to optimize "update-or-insert" database writes, a new Upsert query class has been added.

The Upsert query is lock-free (where possible) and it is optimized for multiple-row inserts. It can only be used on tables that have just one unique / primary key index (e.g. cache tables).

In contrast with the current Merge query, which implements a subset of the ANSI SQL:2003 MERGE command, the Upsert query implements another desirable subset, one that SQLite implements with INSERT ON CONFLICT REPLACE and MySQL implements with REPLACE or INSERT ON DUPLICATE KEY UPDATE.

Other notes:

  1. The Upsert query does not support the INSERT ... SELECT syntax; in MySQL 5.6.4 and later, INSERT ... SELECT ON DUPLICATE KEY UPDATE statements are flagged as unsafe for statement-based replication. With this change, such statements produce a warning in the log when using statement-based mode
  2. The Upsert query does not support all sorts of UPDATE expressions because SQLite only supports the ON CONFLICT REPLACE syntax. The Merge query should be used for complex query expressions.
Impacts: 
Site builders, administrators, editors
Module developers
Updates Done (doc team, etc.)
Online documentation: 
Not done
Theming guide: 
Not done
Module developer documentation: 
Not done
Examples project: 
Not done
Coder Review: 
Not done
Coder Upgrade: 
Not done
Other: 
Other updates done