How to efficiently extract data from a csv file and save it in mysql

Question

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.

Answer 1

If there are several rows with a consistent number of columns in the CSV, then

1. CREATE TABLE with lots of columns
2. LOAD DATA into it.
3. 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.

• Akina: CREATE TABLE with lots of columns It is possible that the values pairs max amount cannot be predicted. I'd recommend to create 2-fields table - title field and the whole slack field, and load data into it with dividing to title and slack data using UDV. Then unpivot it by SP or by a query using generated numbers table (in CTE or subquery) where max number is not less that max pairs amount.
• hstk: Yeah, one row will has inconsistent number of columns, I mean one row have one book record and several scene record.