Large data import using PHP and mysql to multiple tables with foreign keys

Question

What is the better way to insert large data into multiple tables in DB from CSV with foreign keys?

I tried to import large csv file into multiple tables and I use the normal Mysql query with looping csv data, but it was very slow. Then I tried 'LOAD DATA' method.But it is only possible to insert data into a single table with same column order.

However, it will not work for because I need to insert data to multiple tables and need to use last

inserted id of one table to join another table.

Can anyone suggest a better way to upload data into multiple tables?

Here, I am using Zend Framework with Doctrine for handling database operations. However, from my understanding Doctrain is taking more time than normal MySQL query, that is the reason why I used direct MySQL Query.

Possible duplicate of [Best practices for importing large CSV files](https://stackoverflow.com/questions/4166506/best-practices-for-importing-large-csv-files) — R B, May 30 '18 at 13:06
However, above link does not discussing about foreign keys, that is 'id' of one table need to insert as foreign key in another table. — Intellect, May 31 '18 at 10:18

score 1 · Answer 1 · answered May 31 '18 at 10:07

1

You can import large files around millions of records in one shot from below method, please use mysql Load DATA

for ex-

LOAD DATA LOCAL INFILE  
'/PATH_TO_YOUR_CSV/YOUR_CSV.csv'
INTO TABLE MY_TABLE
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Please remember your table structure and your csv file structure (no of columns) should be same

answered May 31 '18 at 10:07

g8bhawani

674
4
8

Thanks for your replay. However I need to insert data to more than one table with foreign keys. ie, Last insert Id of one table should need to insert as foreign key in another table. – Intellect May 31 '18 at 10:20

score 0 · Answer 2 · edited Feb 08 '22 at 20:46

-- import the csvfile to this temp table , the column just like your csv header
create table IF NOT EXISTS my_db.csv_file (

    id int auto_increment primary key,
    vendor_name varchar(200),
    product_name varchar(200),
    product_price double
    
);

-- table one
create table if not exists my_db.product(
    id int auto_increment primary key,
    vendor_id int, -- foreign
    name varchar(200),
    price double
)

-- table two
create table if not exists my_db.vendor(

    id int auto_increment primary key,
    name varchar(200)
)

-- import csv data ,you can use LOAD command 
insert into my_db.csv_file(vendor_name,product_name,product_price)values
('A','book',1.00),
('B','computer',2.00),
('C','phone',3.00);


-- step 1:

insert into my_db.vendor(name)
(select vendor_name from my_db.csv_file group by vendor_name);

-- step 2:
insert into my_db.product(vendor_id , name , price)
(select vendor.id,temp.product_name,temp.product_price from my_db.csv_file as temp left join my_db.vendor as vendor on vendor.name = temp.vendor_name);

Please share more details. If the initial queries were too slow, how does your approach handle this better? — Nico Haase, Oct 22 '21 at 08:30

Large data import using PHP and mysql to multiple tables with foreign keys

2 Answers2