[wp-trac] [WordPress Trac] #22196: add $wpdb->upsert function

[WordPress Trac]

#22196: add $wpdb->upsert function
-------------------------------------------------+-------------------------
 Reporter:  thomask                              |       Owner:
     Type:  enhancement                          |      Status:  reopened
 Priority:  normal                               |   Milestone:  Future
Component:  Database                             |  Release
 Severity:  normal                               |     Version:  3.4.2
 Keywords:  has-patch needs-testing reporter-    |  Resolution:
  feedback needs-refresh needs-unit-tests        |     Focuses:
-------------------------------------------------+-------------------------
Changes (by pento):

 * keywords:  has-patch needs-testing =>
     has-patch needs-testing reporter-feedback needs-refresh needs-unit-
     tests
 * milestone:   => Future Release


Comment:

 Thanks for the patch, @donpark!

 MySQL's `INSERT ... ON DUPLICATE KEY UPDATE` is... kind of weird,
 particularly on tables with multiple unique columns. If the `INSERT`
 clashes with multiple rows, only the first row encountered will be
 updated. I think the behaviour of which row will be updated is undefined.
 It's useful in certain circumstances, but can be horrifyingly hard to
 debug when used incorrectly. I'm not convinced that it's wise to be
 encouraging the use of upserts with a CRUD function, though I'm interested
 in your thoughts on the matter.

 For the patch itself. I think that, instead of `$keys`, we'd want to pass
 `$update_columns` - the array of columns to be used in the `UPDATE` part
 of the query. This matches the behaviour of the actual query (you specify
 the columns to update, not the columns from the `INSERT` part to ''not''
 update), and it's probably easier to maintain against table schema updates
 - adding a new unique index to a table would involve updating every
 `upsert()` call for that query.

 I don't think there'd be any reason to change the `mysqli_affected_rows()`
 call to recognise upserts. It already has well defined behaviour for when
 the `INSERT` vs. the `UPDATE` part of the query was run.

 There are also a couple of places in Core where we use upserts, updating
 them so we're using the functionality in Core would be good.

 Also, this will need many unit tests. :-)

--
Ticket URL: <https://core.trac.wordpress.org/ticket/22196#comment:4>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform