Menu
I have a huge dump file about 40G in size, and I need to dump that back into the database since there are some records are missing after a recovery. Is there any easy way I can covert the INSERT into INSERT IGNORE in the dump file to avoid duplicate entries errors? loading the file to a text editor seems a no go to me. thank you very much in advance
801
However, with the INSERT IGNORE statement, we can prevent such errors from popping up, especially when inserting entries in bulk and such errors can interrupt the flow of insertion. Instead, only a warning is generated. Upon insertion of NULL value where the column has a NOT NULL constraint.
The INSERT IGNORE command keeps the first set of the duplicated records and discards the remaining. The REPLACE command keeps the last set of duplicates and erases out any earlier ones. Another way to enforce uniqueness is to add a UNIQUE index rather than a PRIMARY KEY to a table.
Similar to deletes and replace into, with this scheme, “insert ignore” can be two orders of magnitude faster than insertions into a B-tree.
We can perform MySQL UPSERT operation mainly in three ways, which are as follows: UPSERT using INSERT IGNORE. UPSERT using REPLACE. UPSERT using INSERT ON DUPLICATE KEY UPDATE.
There is also a switch for mysqldump
--insert-ignore in mysqldump
71
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
