How to export JSON base to MYSQL?

Question

PHP

There is access to the database via API, the data from which is stored in a Json file

$pvoStorage = new \API\Parser\Json([
    'filename' => $pathData . 'pvo.txt'
]);

as:

{"id":4,"name":"Афганистан","full_name":"Исламское Государство Афганистан","alpha2":"AF"}
{"id":8,"name":"Албания","full_name":"Республика Албания","alpha2":"AL"}
{"id":10,"name":"Антарктида","full_name":"","alpha2":"AQ"}
{"id":12,"name":"Алжир","full_name":"Алжирская Народная Демократическая Республика","alpha2":"DZ"}
{"id":16,"name":"Американское Самоа","full_name":"","alpha2":"AS"}
{"id":20,"name":"Андорра","full_name":"Княжество Андорра","alpha2":"AD"}

It is necessary to export this database to mysql and update it once a day. Database size 10Gb+.
The question is how to set up saving in the MySQL database? I understand how to send certain values ​​to a cell, but how to send blocks?
It is clear that you need to create a table ( id, name, full_name, alpha2), but how to send data there?
I will be glad to some example on the basis of which you can understand the issue.
Thank you.

Answer 1

From experience, for such volumes, the fastest option would be to import data from a CSV file
dev.mysql.com/doc/refman/5.0/en/load-data.html
Sample SQL query syntax. Change for yourself.

LOAD DATA LOCAL INFILE '{$csv_file}'
INTO TABLE `{$table_tmp}`
FIELDS TERMINATED BY ','
ENCLOSED BY '\"'
LINES TERMINATED BY '\\n'
IGNORE 1 ROWS;

All you have to do is write a script that converts the json document into a csv file
and then import that CSV via the query above.
The order of the columns in the csv file must match the order of the columns in the table in the database.

Answer 2

1. not export, but import
2. google (Yandexite) import json to mysql (there is a lot of information)
3. if you import records as in the example into a simple table into a new database, then it’s easier and faster, as mentioned above, to load directly into the database from a CSV file, and convert json to CSV using sed for example (in one line)