Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / MySQL Новый топик    Ответить
 LOAD DATA INFILE в две таблицы сразу  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21200
Задача навеяна одной из тем на другом форуме. Показалась интересной. Решена.

Постановка задачи. Имеется текстовый файл формата JSONL, содержащий набор объектов в формате JSON. Каждая строка представляет из себя один валидный JSON, причём в нём присутствуют вложенные объекты. Весь файл, само собой, валидным JSON не является. Необходимо выполнить импорт данных в две таблицы с парсингом и с сохранением ссылочной целостности.

Использованный подход можно понять на примере следующей модели.

Структура таблиц:

CREATE TABLE master (master_id INT AUTO_INCREMENT PRIMARY KEY,
                     master_value VARCHAR(255));
CREATE TABLE slave (slave_id INT AUTO_INCREMENT PRIMARY KEY,
                    slave_value VARCHAR(255),
                    master_id INT,
                    CONSTRAINT fk_master FOREIGN KEY (master_id) REFERENCES master (master_id));


Пример текстового файла:

{"master_value":"master value 1", "slave_value":["slave value 1", "slave value 2", "slave value 3"]}
{"master_value":"master value 2", "slave_value":["slave value 4", "slave value 5"]}


Суть решения. Импорт выполняется запросом LOAD DATA INFILE. В процессе импорта используется препроцессинг, прочитанная строка JSON-объекта сохраняется в определённой пользователем переменной (@variable), которая определена для соединения и видна в нём, в том числе и в вызываемых объектах (функции, процедуры, триггеры). Парсинг в основную таблицу выполняется обычным образом. Парсинг в зависимую таблицу выполняется в триггере по событию вставки в основную таблицу.

Код триггера:

CREATE TRIGGER tr_ai_master_fill_slave
AFTER INSERT
ON master
FOR EACH ROW
INSERT INTO slave (slave_value, master_id)
SELECT jsontable.slave_value, NEW.master_id
FROM ( SELECT CAST(@master_tmp_var AS JSON) data ) source
CROSS JOIN JSON_TABLE( source.data,
                       '$.slave_value[*]' COLUMNS ( slave_value VARCHAR(255) PATH '$' ) ) jsontable;


Запрос на импорт данных:

LOAD DATA INFILE 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\source.json'
INTO TABLE master (@master_tmp_var)
SET master_value = JSON_UNQUOTE(JSON_EXTRACT(@master_tmp_var, '$.master_value'));


В результате данные помещены в обе таблицы с сохранением ссылочной целостности.

+ лог консоли
mysql> CREATE TABLE master (master_id INT AUTO_INCREMENT PRIMARY KEY,
    ->                      master_value VARCHAR(255));
Query OK, 0 rows affected (0.34 sec)

mysql> CREATE TABLE slave (slave_id INT AUTO_INCREMENT PRIMARY KEY,
    ->                     slave_value VARCHAR(255),
    ->                     master_id INT,
    ->                     CONSTRAINT fk_master FOREIGN KEY (master_id) REFERENCES master (master_id));
Query OK, 0 rows affected (0.79 sec)

mysql> CREATE TRIGGER tr_ai_master_fill_slave
    -> AFTER INSERT
    -> ON master
    -> FOR EACH ROW
    -> INSERT INTO slave (slave_value, master_id)
    -> SELECT jsontable.slave_value, NEW.master_id
    -> FROM ( SELECT CAST(@master_tmp_var AS JSON) data ) source
    -> CROSS JOIN JSON_TABLE( source.data,
    ->                        '$.slave_value[*]' COLUMNS ( slave_value VARCHAR(255) PATH '$' ) ) jsontable;
Query OK, 0 rows affected (0.10 sec)

mysql> LOAD DATA INFILE 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\source.json'
    -> INTO TABLE master (@master_tmp_var)
    -> SET master_value = JSON_UNQUOTE(JSON_EXTRACT(@master_tmp_var, '$.master_value'));
Query OK, 2 rows affected (0.06 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM master;
+-----------+----------------+
| master_id | master_value   |
+-----------+----------------+
|         1 | master value 1 |
|         2 | master value 2 |
+-----------+----------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM slave;
+----------+---------------+-----------+
| slave_id | slave_value   | master_id |
+----------+---------------+-----------+
|        1 | slave value 1 |         1 |
|        2 | slave value 2 |         1 |
|        3 | slave value 3 |         1 |
|        4 | slave value 4 |         2 |
|        5 | slave value 5 |         2 |
+----------+---------------+-----------+
5 rows in set (0.00 sec)

mysql>


Понятно, что JSON - это не обязательное условие. Таким же образом может быть выполнен импорт данных в любом формате - и в обычном CSV, и в сериализованном средствами PHP, и, в общем, каком угодно.

Ну и само собой надо не забыть немедленно по завершении импорта удалить триггер.

К сообщению приложен файл. Размер - 54Kb


Сообщение было отредактировано: 20 май 21, 15:46
20 май 21, 15:49    [22324811]     Ответить | Цитировать Сообщить модератору
Все форумы / MySQL Ответить