MySqli Insert Mass Data

Question

I am working on a PHP script that will bulk insert data into a table.

I use the prepared statement as follows:

$sql = "INSERT INTO clans(id, clanid, name, badge, status, playercount, score, requiredtrophies, warswon, warslost, warstied, location,warfrequency, exp, level, description, playerjson, lastupdate)
            VALUES ('', ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, now())";
$stmt = $con->prepare($sql); //prepare update statement

$stmt->bind_param('ssisiiiiiissiiss',$clanid,$name,$badge,$status,$playercount,$score,$requiredtrophies,$warswon,$warslost,$warstied,$location,$warfrequency,$exp,$level,$description,$playerarray);

After that, I have a while loop that will execute many requests (hundreds of thousands or even several millions!), And the data in them is quite large! Playerjson is a huge json array.

The data will be obtained from the JSON response from the website API.

I looked at the transactions, but in the end they had 502 Bad Gateway, which, I believe, is due to too much data in memory. These are not timeout problems because I handled them in nginx andini_set

So what is the fastest way to mass input large amounts of data?

Answer 1

- LOAD DATA INFILE. !

tempory

create table clans_temp like clans;

. .

, .