Как правильно импортировать много данных из json в mysql?

Question

[Artikul2]

Допустим, есть json такого плана, получаемый по ссылке со стороннего сервера:

пример json

{
  "data": [
    [
      "Tiger Nixon",
      "System Architect",
      "Edinburgh",
      "5421",
      "2011/04/25",
      "$320,800"
    ],
    [
      "Garrett Winters",
      "Accountant",
      "Tokyo",
      "8422",
      "2011/07/25",
      "$170,750"
    ],
    [
      "Ashton Cox",
      "Junior Technical Author",
      "San Francisco",
      "1562",
      "2009/01/12",
      "$86,000"
    ],
    [
      "Cedric Kelly",
      "Senior Javascript Developer",
      "Edinburgh",
      "6224",
      "2012/03/29",
      "$433,060"
    ],
    [
      "Airi Satou",
      "Accountant",
      "Tokyo",
      "5407",
      "2008/11/28",
      "$162,700"
    ],
    [
      "Brielle Williamson",
      "Integration Specialist",
      "New York",
      "4804",
      "2012/12/02",
      "$372,000"
    ],
    [
      "Herrod Chandler",
      "Sales Assistant",
      "San Francisco",
      "9608",
      "2012/08/06",
      "$137,500"
    ],
    [
      "Rhona Davidson",
      "Integration Specialist",
      "Tokyo",
      "6200",
      "2010/10/14",
      "$327,900"
    ],
    [
      "Colleen Hurst",
      "Javascript Developer",
      "San Francisco",
      "2360",
      "2009/09/15",
      "$205,500"
    ],
    [
      "Sonya Frost",
      "Software Engineer",
      "Edinburgh",
      "1667",
      "2008/12/13",
      "$103,600"
    ],
    [
      "Jena Gaines",
      "Office Manager",
      "London",
      "3814",
      "2008/12/19",
      "$90,560"
    ],
    [
      "Quinn Flynn",
      "Support Lead",
      "Edinburgh",
      "9497",
      "2013/03/03",
      "$342,000"
    ],
    [
      "Charde Marshall",
      "Regional Director",
      "San Francisco",
      "6741",
      "2008/10/16",
      "$470,600"
    ],
    [
      "Haley Kennedy",
      "Senior Marketing Designer",
      "London",
      "3597",
      "2012/12/18",
      "$313,500"
    ],
    [
      "Tatyana Fitzpatrick",
      "Regional Director",
      "London",
      "1965",
      "2010/03/17",
      "$385,750"
    ],
    [
      "Michael Silva",
      "Marketing Designer",
      "London",
      "1581",
      "2012/11/27",
      "$198,500"
    ],
    [
      "Paul Byrd",
      "Chief Financial Officer (CFO)",
      "New York",
      "3059",
      "2010/06/09",
      "$725,000"
    ],
    [
      "Gloria Little",
      "Systems Administrator",
      "New York",
      "1721",
      "2009/04/10",
      "$237,500"
    ],
    [
      "Bradley Greer",
      "Software Engineer",
      "London",
      "2558",
      "2012/10/13",
      "$132,000"
    ],
    [
      "Dai Rios",
      "Personnel Lead",
      "Edinburgh",
      "2290",
      "2012/09/26",
      "$217,500"
    ],
    [
      "Jenette Caldwell",
      "Development Lead",
      "New York",
      "1937",
      "2011/09/03",
      "$345,000"
    ],
    [
      "Yuri Berry",
      "Chief Marketing Officer (CMO)",
      "New York",
      "6154",
      "2009/06/25",
      "$675,000"
    ],
    [
      "Caesar Vance",
      "Pre-Sales Support",
      "New York",
      "8330",
      "2011/12/12",
      "$106,450"
    ],
    [
      "Doris Wilder",
      "Sales Assistant",
      "Sydney",
      "3023",
      "2010/09/20",
      "$85,600"
    ],
    [
      "Angelica Ramos",
      "Chief Executive Officer (CEO)",
      "London",
      "5797",
      "2009/10/09",
      "$1,200,000"
    ],
    [
      "Gavin Joyce",
      "Developer",
      "Edinburgh",
      "8822",
      "2010/12/22",
      "$92,575"
    ],
    [
      "Jennifer Chang",
      "Regional Director",
      "Singapore",
      "9239",
      "2010/11/14",
      "$357,650"
    ],
    [
      "Brenden Wagner",
      "Software Engineer",
      "San Francisco",
      "1314",
      "2011/06/07",
      "$206,850"
    ],
    [
      "Fiona Green",
      "Chief Operating Officer (COO)",
      "San Francisco",
      "2947",
      "2010/03/11",
      "$850,000"
    ],
    [
      "Shou Itou",
      "Regional Marketing",
      "Tokyo",
      "8899",
      "2011/08/14",
      "$163,000"
    ],
    [
      "Michelle House",
      "Integration Specialist",
      "Sydney",
      "2769",
      "2011/06/02",
      "$95,400"
    ],
    [
      "Suki Burks",
      "Developer",
      "London",
      "6832",
      "2009/10/22",
      "$114,500"
    ],
    [
      "Prescott Bartlett",
      "Technical Author",
      "London",
      "3606",
      "2011/05/07",
      "$145,000"
    ],
    [
      "Gavin Cortez",
      "Team Leader",
      "San Francisco",
      "2860",
      "2008/10/26",
      "$235,500"
    ],
    [
      "Martena Mccray",
      "Post-Sales support",
      "Edinburgh",
      "8240",
      "2011/03/09",
      "$324,050"
    ],
    [
      "Unity Butler",
      "Marketing Designer",
      "San Francisco",
      "5384",
      "2009/12/09",
      "$85,675"
    ],
    [
      "Howard Hatfield",
      "Office Manager",
      "San Francisco",
      "7031",
      "2008/12/16",
      "$164,500"
    ],
    [
      "Hope Fuentes",
      "Secretary",
      "San Francisco",
      "6318",
      "2010/02/12",
      "$109,850"
    ],
    [
      "Vivian Harrell",
      "Financial Controller",
      "San Francisco",
      "9422",
      "2009/02/14",
      "$452,500"
    ],
    [
      "Timothy Mooney",
      "Office Manager",
      "London",
      "7580",
      "2008/12/11",
      "$136,200"
    ],
    [
      "Jackson Bradshaw",
      "Director",
      "New York",
      "1042",
      "2008/09/26",
      "$645,750"
    ],
    [
      "Olivia Liang",
      "Support Engineer",
      "Singapore",
      "2120",
      "2011/02/03",
      "$234,500"
    ],
    [
      "Bruno Nash",
      "Software Engineer",
      "London",
      "6222",
      "2011/05/03",
      "$163,500"
    ],
    [
      "Sakura Yamamoto",
      "Support Engineer",
      "Tokyo",
      "9383",
      "2009/08/19",
      "$139,575"
    ],
    [
      "Thor Walton",
      "Developer",
      "New York",
      "8327",
      "2013/08/11",
      "$98,540"
    ],
    [
      "Finn Camacho",
      "Support Engineer",
      "San Francisco",
      "2927",
      "2009/07/07",
      "$87,500"
    ],
    [
      "Serge Baldwin",
      "Data Coordinator",
      "Singapore",
      "8352",
      "2012/04/09",
      "$138,575"
    ],
    [
      "Zenaida Frank",
      "Software Engineer",
      "New York",
      "7439",
      "2010/01/04",
      "$125,250"
    ],
    [
      "Zorita Serrano",
      "Software Engineer",
      "San Francisco",
      "4389",
      "2012/06/01",
      "$115,000"
    ],
    [
      "Jennifer Acosta",
      "Junior Javascript Developer",
      "Edinburgh",
      "3431",
      "2013/02/01",
      "$75,650"
    ],
    [
      "Cara Stevens",
      "Sales Assistant",
      "New York",
      "3990",
      "2011/12/06",
      "$145,600"
    ],
    [
      "Hermione Butler",
      "Regional Director",
      "London",
      "1016",
      "2011/03/21",
      "$356,250"
    ],
    [
      "Lael Greer",
      "Systems Administrator",
      "London",
      "6733",
      "2009/02/27",
      "$103,500"
    ],
    [
      "Jonas Alexander",
      "Developer",
      "San Francisco",
      "8196",
      "2010/07/14",
      "$86,500"
    ],
    [
      "Shad Decker",
      "Regional Director",
      "Edinburgh",
      "6373",
      "2008/11/13",
      "$183,000"
    ],
    [
      "Michael Bruce",
      "Javascript Developer",
      "Singapore",
      "5384",
      "2011/06/27",
      "$183,000"
    ],
    [
      "Donna Snider",
      "Customer Support",
      "New York",
      "4226",
      "2011/01/25",
      "$112,000"
    ]
  ]
}

Раньше, я обрабатывал его так:
1. json_decode($json)
2. затем цикл foreach с модификацией данных (подставлял внешний id в каждую запись.
3. в каждой итерации средствами фреймворка (не прямая работа с mysql) делал запись в базу данных, проверяя нет ли уже такой записи в базе.

Скрипт php работал на кроне и по специфике задания требовалось забирать обновленные данные каждые 10-30 минут.
Естественно этот говнометод был нереально долгим, грузящий неслабый сервер, так ещё не успел закончиться запущенный процесс, запускался новый. Данные по api идут постоянно (задача загрузить все архивные данные за год, и поддерживать обновление в течение дня)

Теперь я хочу оптимизировать все процессы, а потому хочу сделать так:
1. не модифицировать данные с помощью php
2. не проверять на дубли (с этим проблема, в мускул можно сделать так - если запись есть, то update, если нет, то добавить без проверки на php?)
3. Собрать данные и одним запросом поместить в mysql
Прочитал это надо делать одной транзакцией:
типа

Старт
-Вставить строку ("МОЙ ВНЕШНИЙ ID, ""Donna Snider", "Customer Support", "New York", "4226", "2011/01/25","$112,000");
-Вставить строку ("МОЙ ВНЕШНИЙ ID, " "Michael Bruce","Javascript Developer","Singapore","5384","2011/06/27","$183,000");
Финиш

Старт
-Вставить строку ("МОЙ ВНЕШНИЙ ID, "Дата последней загрузки данных для этого ID");
Финиш

Собственно вопрос:
Правильно ли собрался делать? и был бы рад примеру кода как json в mysql вставлять.
Может можно обойтись без json_decode?
(это мой первый опыт и напрямую с mysql я не работал, только через php-фреймворки)

Comments

[rPman]

Что именно грузит твой сервер?
* декодирования json?
* средствами фреймворка (не прямая работа с mysql) делал запись в базу данных, проверяя нет ли уже такой записи в базе.?
* запись в базу (вызов insert и update)?

потому что в зависимости от ответа на этот вопрос, будут даны и рекомендации по ускорению

[Artikul2]

rPman,
на каждый запрос на внешний сервер я получаю, допустим 1000 записей, где каждая запись имеет 20 значений.
этот json я превращаю в массив и прогоняю в цикле два запроса
1. Проверяю есть ли такая запись (поиск в базе)
2. Если есть, то обновляю, если нет, то добавляю
В этом и есть корень тормозов.
Но по сути, каждый момент можно оптимизировать, если применить правильный подход.
На использование транзакции подтолкнул этот пост
https://qna.habr.com/q/82104
Но в силу того, что я не работал никогда с mysql напрямую я не знаю как применить данный метод, мне бы пример.

Пока прикидываю такой пример:

mysql_connect(....) ;
mysql_select_db("mytable");
$Data = file_get_contents("data.json");
$object = json_decode($Data);


/* Начало транзакции */
$mysqli->begin_transaction();

try {

foreach($Data as $items)
{
$query = "INSERT INTO employee (ssn,name,phone) values ($items['ssn'],$items['name'],$items['phone'])";

};

    $mysqli->commit();
} catch (mysqli_sql_exception $exception) {
    $mysqli->rollback();

    throw $exception;
}

Т.е. использование в циклах обращений к базе и есть тормоз.

[rPman]

1000 записей тормоза?
у тебя правильно организована транзакция, одна на все инсерты

в примере нет проверок на существующие записи
если есть поле, через которое определяется уникальность записи, делай ему primary key индекс в таблице и тогда сможешь использовать
insert ignore или on duplecate key update

иначе предварительно сгенерируй select id from ... where id in (список идентификаторов через запятую) (id этот уникальный ключ, осторожно со строками, используй биндинг параметров) и получи список id которые уже в базе есть и для них не делай insert

[Artikul2]

у тебя правильно организована транзакция, одна на все инсерты

о, нет. Этот пример еще не реализован. В рабочем варианте так как описано в посте.
Я хочу попробовать переделать по своему примеру, но если говорите, что правильно организовано, то хорошо.

Я не знаю только:

foreach($Data as $items)
{
$query = "INSERT INTO employee (ssn,name,phone) values ($items['ssn'],$items['name'],$items['phone'])";

};

мне кажется тут ошибка в написании кода,
надо в одну переменную все собрать, а так она у меня переписывается?!

[Akina]

А версию MySQL мы узнаем? или это военная тайна?

[Artikul2]

Akina, MySQL 8.0.28-0ubuntu0.20.04.3

Answer 1

[Akina]

Artikul2,

MySQL 8.0.28-0ubuntu0.20.04.3

Не делай ничего с этим JSON. Отдай его как есть на MySQL - у него парсинг получится лучше...

INSERT INTO test (name, position, city, postcode, entered_at, income)
SELECT data ->> '$[0]' name,
       data ->> '$[1]' position,
       data ->> '$[2]' city,
       data ->> '$[3]' postcode,
       STR_TO_DATE(data ->> '$[4]', '%Y\/%m\/%d') entered_at,
       REPLACE(SUBSTRING(data ->> '$[5]' FROM 2), ',', '') income
FROM JSON_TABLE(@data,
                '$.data[*]' COLUMNS (data JSON PATH '$')) jsontable

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=8c5e1c...

Если надо отсеивать дубликаты - создать соотв. уникальный индекс, и использовать INSERT ODKU, REPLACE INTO либо INSERT IGNORE.

Comments

[Artikul2]

Спасибо большое, что не обошли вопрос стороной, как я вижу - это отличное решение.
Если так, как я вижу в примере по ссылке, то это супер.
Я попробую и обязательно отвечу здесь еще.

[Artikul2]

Akina
не получилось провести тест, но я я понимаю, что это то решение, что нужно.
Не могли бы вы уточнить по моментам, которые вызвали сложности по неопытности:

FROM JSON_TABLE(@data, '$.data[*]' COLUMNS (data JSON PATH '$')) jsontable

Как сюда вставить физический json?
Сначала я тупил и хотел указать путь на хостинге, типа ...../upload/myfile.json в этой строке, но потом понял, что вы имели ввиду - загрузить как есть.
Правильно ли я понял, что мне надо создать временную таблицу, назвать ее JSON_TABLE и создать одно поле с названием data, как то загрузить туда json, а после того как разложу по полям, очищать таблицу?

Второй момент: в примере использовался json

spoiler

{
  "data": [
    [
      "Tiger Nixon",
      "System Architect",
      "Edinburgh",
      "5421",
      "2011/04/25",
      "$320,800"
    ]
]
}

На практике у меня структура такая, без data((

spoiler

[{"name":"ivan","date":"2022-04-14T10:19:43","kurs":"1"},{"name":"lena","date":"2022-04-10T10:22:41","kurs":"5"},{"name":"misha","date":"2022-04-11T10:11:22","kurs":"3"}]

помогите мне - как изменится код? Я готов немного отблагодарить вас и прислать вам денег, если вы напишите мне свой номер.

[Akina]

Artikul2, https://dev.mysql.com/doc/refman/8.0/en/json-table...

[Artikul2]

Akina,

<?php 
      $connection = mysqli_connect("localhost",".............","..........","...........");

      if(!$connection){ // проверка правильности подключения
         echo 'Ошибка соединения: ' . mysqli_connect_error() . '<br>';
         echo 'Код ошибки: ' . mysqli_connect_errno();
      }else{ // подключение успешно установлено

         // текст SQL запроса, который будет передан базе
            $query = "CREATE TABLE test (name VARCHAR(255),
                   position VARCHAR(255),
                   city VARCHAR(255),
                   postcode INT,
                   entered_at DATE,
                   income INT);";
            $result = mysqli_query($connection, $query);


            $query = "create table jsontable( data json )";
            $result = mysqli_query($connection, $query);

            $query = "LOAD DATA INFILE '/var/lib/mysql-files/myfile.json' INTO TABLE jsontable";
            $result = mysqli_query($connection, $query);

            if(!$result){ // запрос завершился ошибкой
               echo 'Ошибка запроса: ' . mysqli_error($connection) . '<br>';
               echo 'Код ошибки: ' . mysqli_errno($connection);
            }else{ // запрос успешно выполнился
               while($row = $result->fetch_assoc()){
                  // обрабатываем полученные данные
               }
            }
         // закрываем соединение с базой
            mysqli_close($connection);
      }
?>

json, который myfile.json

{
  "data": [
    [
      "Tiger Nixon",
      "System Architect",
      "Edinburgh",
      "5421",
      "2011/04/25",
      "$320,800"
    ],
    [
      "Garrett Winters",
      "Accountant",
      "Tokyo",
      "8422",
      "2011/07/25",
      "$170,750"
    ],
    [
      "Ashton Cox",
      "Junior Technical Author",
      "San Francisco",
      "1562",
      "2009/01/12",
      "$86,000"
    ],
    [
      "Cedric Kelly",
      "Senior Javascript Developer",
      "Edinburgh",
      "6224",
      "2012/03/29",
      "$433,060"
    ],
    [
      "Airi Satou",
      "Accountant",
      "Tokyo",
      "5407",
      "2008/11/28",
      "$162,700"
    ],
    [
      "Brielle Williamson",
      "Integration Specialist",
      "New York",
      "4804",
      "2012/12/02",
      "$372,000"
    ],
    [
      "Herrod Chandler",
      "Sales Assistant",
      "San Francisco",
      "9608",
      "2012/08/06",
      "$137,500"
    ],
    [
      "Rhona Davidson",
      "Integration Specialist",
      "Tokyo",
      "6200",
      "2010/10/14",
      "$327,900"
    ],
    [
      "Colleen Hurst",
      "Javascript Developer",
      "San Francisco",
      "2360",
      "2009/09/15",
      "$205,500"
    ],
    [
      "Sonya Frost",
      "Software Engineer",
      "Edinburgh",
      "1667",
      "2008/12/13",
      "$103,600"
    ],
    [
      "Jena Gaines",
      "Office Manager",
      "London",
      "3814",
      "2008/12/19",
      "$90,560"
    ],
    [
      "Quinn Flynn",
      "Support Lead",
      "Edinburgh",
      "9497",
      "2013/03/03",
      "$342,000"
    ],
    [
      "Charde Marshall",
      "Regional Director",
      "San Francisco",
      "6741",
      "2008/10/16",
      "$470,600"
    ],
    [
      "Haley Kennedy",
      "Senior Marketing Designer",
      "London",
      "3597",
      "2012/12/18",
      "$313,500"
    ],
    [
      "Tatyana Fitzpatrick",
      "Regional Director",
      "London",
      "1965",
      "2010/03/17",
      "$385,750"
    ],
    [
      "Michael Silva",
      "Marketing Designer",
      "London",
      "1581",
      "2012/11/27",
      "$198,500"
    ],
    [
      "Paul Byrd",
      "Chief Financial Officer (CFO)",
      "New York",
      "3059",
      "2010/06/09",
      "$725,000"
    ],
    [
      "Gloria Little",
      "Systems Administrator",
      "New York",
      "1721",
      "2009/04/10",
      "$237,500"
    ],
    [
      "Bradley Greer",
      "Software Engineer",
      "London",
      "2558",
      "2012/10/13",
      "$132,000"
    ],
    [
      "Dai Rios",
      "Personnel Lead",
      "Edinburgh",
      "2290",
      "2012/09/26",
      "$217,500"
    ],
    [
      "Jenette Caldwell",
      "Development Lead",
      "New York",
      "1937",
      "2011/09/03",
      "$345,000"
    ],
    [
      "Yuri Berry",
      "Chief Marketing Officer (CMO)",
      "New York",
      "6154",
      "2009/06/25",
      "$675,000"
    ],
    [
      "Caesar Vance",
      "Pre-Sales Support",
      "New York",
      "8330",
      "2011/12/12",
      "$106,450"
    ],
    [
      "Doris Wilder",
      "Sales Assistant",
      "Sydney",
      "3023",
      "2010/09/20",
      "$85,600"
    ],
    [
      "Angelica Ramos",
      "Chief Executive Officer (CEO)",
      "London",
      "5797",
      "2009/10/09",
      "$1,200,000"
    ],
    [
      "Gavin Joyce",
      "Developer",
      "Edinburgh",
      "8822",
      "2010/12/22",
      "$92,575"
    ],
    [
      "Jennifer Chang",
      "Regional Director",
      "Singapore",
      "9239",
      "2010/11/14",
      "$357,650"
    ],
    [
      "Brenden Wagner",
      "Software Engineer",
      "San Francisco",
      "1314",
      "2011/06/07",
      "$206,850"
    ],
    [
      "Fiona Green",
      "Chief Operating Officer (COO)",
      "San Francisco",
      "2947",
      "2010/03/11",
      "$850,000"
    ],
    [
      "Shou Itou",
      "Regional Marketing",
      "Tokyo",
      "8899",
      "2011/08/14",
      "$163,000"
    ],
    [
      "Michelle House",
      "Integration Specialist",
      "Sydney",
      "2769",
      "2011/06/02",
      "$95,400"
    ],
    [
      "Suki Burks",
      "Developer",
      "London",
      "6832",
      "2009/10/22",
      "$114,500"
    ],
    [
      "Prescott Bartlett",
      "Technical Author",
      "London",
      "3606",
      "2011/05/07",
      "$145,000"
    ],
    [
      "Gavin Cortez",
      "Team Leader",
      "San Francisco",
      "2860",
      "2008/10/26",
      "$235,500"
    ],
    [
      "Martena Mccray",
      "Post-Sales support",
      "Edinburgh",
      "8240",
      "2011/03/09",
      "$324,050"
    ],
    [
      "Unity Butler",
      "Marketing Designer",
      "San Francisco",
      "5384",
      "2009/12/09",
      "$85,675"
    ],
    [
      "Howard Hatfield",
      "Office Manager",
      "San Francisco",
      "7031",
      "2008/12/16",
      "$164,500"
    ],
    [
      "Hope Fuentes",
      "Secretary",
      "San Francisco",
      "6318",
      "2010/02/12",
      "$109,850"
    ],
    [
      "Vivian Harrell",
      "Financial Controller",
      "San Francisco",
      "9422",
      "2009/02/14",
      "$452,500"
    ],
    [
      "Timothy Mooney",
      "Office Manager",
      "London",
      "7580",
      "2008/12/11",
      "$136,200"
    ],
    [
      "Jackson Bradshaw",
      "Director",
      "New York",
      "1042",
      "2008/09/26",
      "$645,750"
    ],
    [
      "Olivia Liang",
      "Support Engineer",
      "Singapore",
      "2120",
      "2011/02/03",
      "$234,500"
    ],
    [
      "Bruno Nash",
      "Software Engineer",
      "London",
      "6222",
      "2011/05/03",
      "$163,500"
    ],
    [
      "Sakura Yamamoto",
      "Support Engineer",
      "Tokyo",
      "9383",
      "2009/08/19",
      "$139,575"
    ],
    [
      "Thor Walton",
      "Developer",
      "New York",
      "8327",
      "2013/08/11",
      "$98,540"
    ],
    [
      "Finn Camacho",
      "Support Engineer",
      "San Francisco",
      "2927",
      "2009/07/07",
      "$87,500"
    ],
    [
      "Serge Baldwin",
      "Data Coordinator",
      "Singapore",
      "8352",
      "2012/04/09",
      "$138,575"
    ],
    [
      "Zenaida Frank",
      "Software Engineer",
      "New York",
      "7439",
      "2010/01/04",
      "$125,250"
    ],
    [
      "Zorita Serrano",
      "Software Engineer",
      "San Francisco",
      "4389",
      "2012/06/01",
      "$115,000"
    ],
    [
      "Jennifer Acosta",
      "Junior Javascript Developer",
      "Edinburgh",
      "3431",
      "2013/02/01",
      "$75,650"
    ],
    [
      "Cara Stevens",
      "Sales Assistant",
      "New York",
      "3990",
      "2011/12/06",
      "$145,600"
    ],
    [
      "Hermione Butler",
      "Regional Director",
      "London",
      "1016",
      "2011/03/21",
      "$356,250"
    ],
    [
      "Lael Greer",
      "Systems Administrator",
      "London",
      "6733",
      "2009/02/27",
      "$103,500"
    ],
    [
      "Jonas Alexander",
      "Developer",
      "San Francisco",
      "8196",
      "2010/07/14",
      "$86,500"
    ],
    [
      "Shad Decker",
      "Regional Director",
      "Edinburgh",
      "6373",
      "2008/11/13",
      "$183,000"
    ],
    [
      "Michael Bruce",
      "Javascript Developer",
      "Singapore",
      "5384",
      "2011/06/27",
      "$183,000"
    ],
    [
      "Donna Snider",
      "Customer Support",
      "New York",
      "4226",
      "2011/01/25",
      "$112,000"
    ]
  ]
}

Ошибка запроса: Invalid JSON text: "Missing a name for object member." at position 1 in value for column 'jsontable.data'.
Код ошибки: 3140
Ругается на

$query = "LOAD DATA INFILE '/var/lib/mysql-files/myfile.json' INTO TABLE jsontable";

Хотя если запросы вставлять в консоли, а не в php, то все отрабатывает.
Я уже весь измучился(

[Akina]

Слушайте, ну Вы хоть когда-нибудь намерены начать читать описание того, что используете? За каким хреном тут CREATE TABLE jsontable? и LOAD DATA INFILE?

Читайте описание функции LOAD_FILE(). Читайте его всё, от начала до конца, и до полного понимания.

[Artikul2]

Akina,

<?php 
$connection = mysqli_connect("localhost",".............","..........","...........");
      if(!$connection){ // проверка правильности подключения
         echo 'Ошибка соединения: ' . mysqli_connect_error() . '<br>';
         echo 'Код ошибки: ' . mysqli_connect_errno();
      }else{ // подключение успешно установлено

         // текст SQL запроса, который будет передан базе
            $query = "CREATE TABLE test (name VARCHAR(255),
                   position VARCHAR(255),
                   city VARCHAR(255),
                   postcode INT,
                   entered_at DATE,
                   income INT) DEFAULT CHARSET UTF8MB4;";
            $result = mysqli_query($connection, $query);

            $query = "

INSERT INTO test (name, position, city, postcode, entered_at, income)
SELECT data ->> '$[0]' name,
       data ->> '$[1]' position,
       data ->> '$[2]' city,
       data ->> '$[3]' postcode,
       STR_TO_DATE(data ->> '$[4]', '%Y\/%m\/%d') entered_at,
       REPLACE(SUBSTRING(data ->> '$[5]' FROM 2), ',', '') income
FROM JSON_TABLE((CONVERT(LOAD_FILE('/var/lib/mysql-files/myfile.json') using utf8mb4)),
                '$.data[*]' COLUMNS (data JSON PATH '$')) jsontable

";
            $result = mysqli_query($connection, $query);



            if(!$result){ // запрос завершился ошибкой
               echo 'Ошибка запроса: ' . mysqli_error($connection) . '<br>';
               echo 'Код ошибки: ' . mysqli_errno($connection);
            }else{ // запрос успешно выполнился
               while($row = $result->fetch_assoc()){
                  // обрабатываем полученные данные
               }
            }
         // закрываем соединение с базой
            mysqli_close($connection);
      }
?>

Я сделал так, и у меня кажется получилось.
Теперь буду думать как обработать json как у меня (без data вложения)

[{"name":"ivan","date":"2022-04-14T10:19:43","kurs":"1"},{"name":"lena","date":"2022-04-10T10:22:41","kurs":"5"},{"name":"misha","date":"2022-04-11T10:11:22","kurs":"3"}]

[Akina]

Теперь буду думать как обработать json как у меня (без data вложения)

До тех пор, пока мануал по JSON_TABLE() не будет полностью прочитан и понят (включая принцип формирования имен и путей в JSON), если вдруг получится - то только случайно.

[Artikul2]

Akina, буду читать и читаю. Искал в интернете примеры, но все на зарубежных сайтах и практически одни дубли ответов, как и документация, вроде понятно, но как применить пока не разобрался.

Сейчас накидал такой код для теста, он создает нужное кол-во строк из json, но все поля null

SET @json = CONVERT(LOAD_FILE('/var/lib/mysql-files/myfile.json') using utf8mb4);
REPLACE INTO test (name, city, kurs)
SELECT   
  JSON_VALUE(@json, '$.name') as name,
  JSON_VALUE(@json, '$.city') as city,
  JSON_VALUE(@json, '$.kurs') as kurs
FROM JSON_TABLE(@json,'$[*]' COLUMNS (data JSON PATH '$')) jsontable

[Akina]

Ну так использованный JSON path ('$.name', '$.city', '$.kurs') никакого отношения к показанному JSON не имеет...

[
  {
    "name":"ivan",
    "date":"2022-04-14T10:19:43",
    "kurs":"1"
  },
  {
    "name":"lena",
    "date":"2022-04-10T10:22:41",
    "kurs":"5"
  },
  {
    "name":"misha",
    "date":"2022-04-11T10:11:22",
    "kurs":"3"
  }
]

Верхний уровень агрегации - массив. А у тебя в пути нет даже намёка на массив... поэтому и NULL.

[Artikul2]

Akina,

select * from json_table(@json,'$[*]' columns(
name varchar(20) path '$.name',
city varchar(20) path '$.city',
kurs varchar(20) path '$.kurs')) as jsontable

Я всё сделал.
Вам спасибо!

Answer 2

[Владимир Коротенко]

Предложу один из вариантов
https://linuxhint.com/bulk-insert-mysql/

первичные ключи держите в редисе
Для вашего случая конвертите json в csv отбрасывая записи которые уже есть по первичному ключу
используете пакетную вставку с транзакцией

Comments

[Artikul2]

О, это наверное то, что нужно.
Я хочу попробовать этот способ и отпишусь по результату.