How to efficiently extract data from a csv file and save it in mysql I have a csv file, maybe 1000 or more rows, and echo row’s data is related to two table in mysql. Example,  

table `book`(id, title, …)
table `scene`(id, book_id, short_content, position…)


And the csv file look like:

title,short_content1,position1,short_content2,position2,…
...


I want to extract data from it and save it to mysql. Also in the mysql maybe already have the same record, so I don’t want save the same data to mysql.
 My solution is:


First, fetch all the data related to the file, a big data set labeled BS.
Second, for each row in file compare to the BS and create the book, and store the book_id for his scene.
Finally, batch create the scene.


This is my poor solution, and if the csv file has 1000 rows, then the solution will take a large memory space and 1000 + 1 times mysql calls.

I see Bulk database update/insert from CSV file and Update Oracle database from CSV post. But I still confuse how to deal with csv file which related to multiple tables.

Can anyone give me a better solution.
 Thanks.
If there are several rows with a consistent number of columns in the CSV, then


CREATE TABLE with lots of columns
LOAD DATE into it.
Do "unpivot" code to rearrange it into the 'real' tables you want.


If there is only one row in your CSV, then write code in your favorite programming language to break it apart.

Not sure what to do if you have an inconsistent number of columns.

You can use INSERT IGNORE and/or IODKU` to deal with duplicates.