Description:
mysqldump currently supports two different options for dealing with PK collisions:
--insert-ignore (uses INSERT IGNORE)
--replace (uses REPLACE INTO instead of INSERT INTO)
I'd like to request an additional option that uses INSERT ... ON DUPLICATE KEY UPDATE ... when used.
This will allow one database to be used as a source to update another database without the following problems:
Using INSERT IGNORE will ignore any updates to existing rows that are coming from the file being loaded "on top of" an existing database.
Using REPLACE ends up churning the primary index, failing when foreign keys point to the record being updated (REPLACED), or ON DELETE CASCADE wiping-out records in other tables as the records in the target table are "updated" (using REPLACE).
There are two significant downsides to using ON DUPLICATE KEY UPDATE, of course:
1. The dump file will get much bigger, because the bulk-loading syntax for INSERT can no longer be used.
2. The dump file will get much longer, because ON DUPLICATE KEY UPDATE requires that all values be specified twice: once in the VALUES() section and then a second time in the field=value, field=value part after "ON DUPLICATE KEY UPDATE" phrase.
Bug 11422 [http://bugs.mysql.com/bug.php?id=11422] requests the ability to simply say "ON DUPLICATE KEY UPDATE" and allow the engine to use the VALUES to update all fields in the table. Fixing bug 11422 and using the solution here would mitigate both downsides because then extended syntax could (possibly?) be used and the values would not have to be mentioned twice in the dump file.
I have found many posts online about how to do something like this and the responses always steer the person toward --insert-ignore or --replace but there are many scenarios where the above request would be preferable.
How to repeat:
(This is a feature request)
