Adding Mysql 8 'REPLACE' support

[JABirchall]

Mysql 8 added a new command 'REPLACE' documentation https://dev.mysql.com/doc/refman/8.0/en/replace.html

Replace acts as an INSERT on duplicate UPDATE

When replacing multiple rows if the primary key OR/AND unique key is passed and that value matches what's already in the database, it will update that row, else insert a new row.

To implement ID must be sent or else mysql will attempt to match all columns and if they match MYSQL: will increment the ID to the new incremental value.

To have the act as insert or update, sending id as null will act as an insert

This could be used to deprecate the upsert function for a more standard approach

[willrowe]

According to the page you linked to REPLACE does not update a duplicate row, it will delete it before inserting:

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted. See Section 13.2.6, “INSERT Statement”.

REPLACE is a MySQL extension to the SQL standard. It either inserts, or deletes and inserts. For another MySQL extension to standard SQL—that either inserts or updates—see Section 13.2.6.2, “INSERT ... ON DUPLICATE KEY UPDATE Statement”.

Also, I believe REPLACE has been around since at least v5.6, if not earlier.

[JABirchall]

@willrowe Yea I did speak to some laravel internal developers and it turns out to not be recommended to use due to the deleting rows part of replace. Apparently reeks havoc on tables with foreign keys.

I still think the option should be available for developers, they just need to be aware of how replace works and not use it incorrectly.