I am trying to import a large database dump (~30 GB) with millions of rows. There are some duplicate rows in there for sure, and I'd like MySQL to just skip those rows and move on without giving me the following error:

ERROR 1062 (23000) at line 23: Duplicate entry '31' for key 'PRIMARY'

If you're bringing the data into an otherwise empty table, then drop the primary key. You'll be able to import the data as is, clean it up, make sure that the primary key column(s) are now unique, then put the primary key back in place.

I would not recommend bringing the data into a table with other data directly, as you know the data does need to be cleaned up. Import the "dirty" data into a staging table, clean it up, decide how you need to merge it into the existing table (as in, if there is duplication in keys between the data in the target table and the "dirty" data, which data should be kept, and which should be discarded; this may differ on a record-by-record basis).

Note: The links on merging data may work, if you already know which table's data should be kept in all cases; however, it sounds like the data coming in did not have a properly enforced primary key, and thus the incoming data may have multiple records with the same primary key value. I'm not certain if all those options work as desired in that case.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.