load csv file content into mysql table with validation
i want to import a large csv file (about 12MO) into mysql table , first i tried with LOAD DATA INFILE it work perfectly , but in my case i want firstly test the开发者_开发百科 csv rows to determine if i want to update data or insert new records so the solution is to read the file and compare the content of each row with the data already in the table , and make the right action this methode also works but takes lot of time and resources
now my question is
1 : can i use the import functions of PHPMYADMIN ( open source ) and my project is comercial
2 : if yes i can , do you know some tutorials about this ( any idea )
3 : if no i can't , is there such commercial frameworks for exporting/importing
thanks
This is actually pretty common SQL: you either want to insert or update, yes? So you need two statements (one for update, one for insert) and a way to tell if it should be inserted. What you really need is a unique key that will never be duplicated for the individual record (can be a composite key) and two statements, like this:
UPDATE right SET
right1 = left1,
right2 = left2,
etc
FROM the_import_table right
LEFT JOIN the_existing_data left ON left.key = right.key
WHERE right.key IS NOT NULL --note that I write in TSQL
INSERT INTO right (
right1,
right2,
etc
) SELECT
left1,
left2,
etc
FROM left
LEFT JOIN right on left.key = right.key
WHERE right.key IS NULL
Note that you can use composite keys using a set of ANDed values in the WHERE, and note that you're not updating the composite keys but you are probably inserting the composite keys. This should be good to get you a start. Before you ask for clarification update your question with actual code.
MySQL has specific insert syntax to deal with duplicate rows.
精彩评论