in reply to DBI Question: bulk insertion with Unique-key constraint

The LOAD DATA INFILE command will read your CSV file directly and you can have it either ignore duplicates or replace (i.e. keep the first or last). Just read the docs for that command and you should be all set.

In general, don't use Perl for bulk data inserts. The MySQL bulk load is massively faster.

Replies are listed 'Best First'.
Re^2: DBI Question: bulk insertion with Unique-key constraint
by lihao (Monk) on Apr 24, 2008 at 20:45 UTC
    But I need to pre-process some columns, otherwise some data got corrupted, like datetime format. So I guess I need at least to use Perl to go through the CSV file and adjust some columns like datetime format, save them into another file and then load data with MySQL. :)
[reply]
      Yes, doing a pass on the CSV file with perl and generating another CSV file to pass to MySQL is still probably faster than loading it row by row.

      By the way, INSERT IGNORE or INSERT ON DUPLICATE UPDATE are both typically faster than REPLACE if you do need to do it row by row.

[reply]

In Section Seekers of Perl Wisdom