Загрузка...
Поставил сам себе задачу разобраться как загрузить данные в MySQL из xml-файлов и csv-файлов используя LOAD DATA и LOAD XML операторы, которые создавал с помощью MS SQL Server 2019. Создал некое подобие обмена данными между двумя данными СУБД. И приведу результаты данных экспериментов в качестве примеров.
Для данного эксперимента используется программное обеспечение:
Железо - Ноутбук Acer Aspire E5-573-58KB:
Загрузка данный будет происходить с локального каталога и в MySQL данный каталог необходимо прописать в качестве параметра путь до этого каталога в системную переменную secure-file-priv, это можно сделать в конфигурационном файле my.ini:
[mysqld]
port = 3301
...........................................
secure-file-priv=d:\\c\\base\\mysql_test
..................................................
Так же порт в моих настройках отличается от порта по умолчанию 3306, но больших проблем это не вызовет. Его нужно будет явно указывать при подключении.
После того, как был прописан параметр secure-file-priv необходимо перезагрузить службу MySQL из-под админской учётки, либо с помощью оснастки services.msc, либо с помощью команд net stop <имя_службы> - для остановки службы и net start <имя_службы> - для запуска службы, но для этого необходимо знать имя службы, к примеру у меня имя службы отличается от имени по умолчанию. После того как был прописан параметр и служба перезагружена, то желательно бы проверить сработал ли параметр. Это можно сделать с помощью SQL-запроса: SELECT @@GLOBAL.secure_file_priv;
Также сразу лучше всего проверить возможность использовать оператор LOAD DATA..., для этого необходимо выполнить следующий запрос:
SHOW VARIABLES LIKE 'local_infile';
если в колонке Value значение OFF, то при попытке выполнить оператор LOAD DATA... MySQL выдаст ошибку:
ERROR 1148: The used command is not allowed with this MySQL version
чтобы исправить данное недоразумение достаточно выполнить следующий запрос:
SET GLOBAL local_infile = 1;
минус данного решения в том, что необходимы права администратора чтобы изменить данную глобальную переменную.
Для эксперимента создам новую базу данных:
CREATE DATABASE LoadDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
В loaddb создадим таблицу Employees для экспериментов:
CREATE TABLE Employees(
empid INT NOT NULL AUTO_INCREMENT,
lastname VARCHAR(255) NOT NULL,
firstname VARCHAR(255) NOT NULL,
title VARCHAR(255) NOT NULL,
CONSTRAINT PK_empid PRIMARY KEY (empid)
) ENGINE=INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Таблица для эксперимента готова.
Теперь необходимо создать файл cvs, его я создам из учебной базы TSQLV4 и возьму таблицу HR.Employees:
но выгрузку данных сделаю с помощью консольной утилиты SQLCMD:
SQLCMD -E -Q "SET NOCOUNT ON; select empid, lastname, firstname, title from TSQLV4.HR.Employees" -s ":" -o "d:\c\base\mysql_test\experiment_csv.txt"
SET NOCOUNT ON; - данная команда убирает метаданные, которые нам не нужны
результат на скрине ниже, но также добавил одну сроку на кириллице:
Ну что ж, теперь можно приступить к эксперименту по загрузке данных из CSV-файла:
Синтаксис оператора LOAD DATA взят из официальной документации - mysql-refman-5.7-en.a4.pdf:
LOAD DATA
[LOW_PRIORITY | CONCURRENT] [LOCAL]
INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[CHARACTER SET charset_name]
[ { FIELDS | COLUMNS}
[TERMINATED BY 'string']
[ [OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number { LINES | ROWS} ]
[(col_name_or_user_var
[, col_name_or_user_var] ...) ]
[SET col_name={ expr | DEFAULT} ,<
[, col_name={ expr | DEFAULT}] ...]
В планах провести 3 эксперимента с данным файлом, немного меняя запрос и сам csv файл.
Эксперимент №1 - загрузка всех колонок из файла.
LOAD DATA LOCAL INFILE 'd:\\c\\base\\mysql_test\\experiment_csv.txt'
INTO TABLE Employees
CHARACTER SET cp1251
FIELDS TERMINATED BY ':'
LINES TERMINATED BY '\r\n'
IGNORE 2 LINES;
где INFILE - файл источник
INTO TABLE - таблица, в которую загружаем данные
CHARACTER SET - кодировка самого загружаемого CSV файла (очень важно для кириллицы)
FIELDS TERMINATED BY - разделение колонок(в данном случае это двоеточие, но можно использовать и другие символы)
LINES TERMINATED BY - разделение линий, в данном файле это CR LF - чему соответсвуют \r\n
IGNORE number LINES - пропустить первые несколько строк, в данном случае 2, т.к. 1-я строка это название колонок, 2-я - разделитель.
Также на скрине ниже показана возможность узнать кодировку CSV файла, если его размер не очень велИк:
Эксперимент №2 - загрузка не всех колонок из файла.
Так как таблица создана с использованием параметра AUTO_INCREMENT в поле empid, то о его заполнении можно не беспокоится, MySQL его сам заполнит, соответственно удалим данную колонку из файла:
и попытаемся его загрузить:
LOAD DATA LOCAL INFILE 'd:\\c\\base\\mysql_test\\experiment_csv_2.txt'
INTO TABLE Employees
CHARACTER SET cp1251
FIELDS TERMINATED BY ':'
LINES TERMINATED BY '\r\n'
IGNORE 2 LINES;
и посмотрим на результат ниже на скриншоте:
Будьте осторожны с использованием запроса: truncate table employees; - данный запрос удаляет все данные из таблицы!!!!
При выполнении LOAD DATA ... возниколо 20 Wornings - это связано с тем, что произошёл сдвиг колонок. Т.к. колонка empid была удалена вместе с данными из файла experiment_csv_2.txt , то в результате запроса была попытка записать в колонку empid строки из lastname и т.д.
Чтобы обойти данную проблему, необходимо явно прописать колонки, в которые нужно сделать запись:
LOAD DATA LOCAL INFILE 'd:\\c\\base\\mysql_test\\experiment_csv_2.txt'
INTO TABLE Employees
CHARACTER SET cp1251
FIELDS TERMINATED BY ':'
LINES TERMINATED BY '\r\n'
IGNORE 2 LINES
(firstname, lastname, title);
результат ниже:
Эксперимент №3 - из файла cvs уберу колонку title вместе с параметрами:
но при этом колонку title из таблицы employees необходимо заполнить следуя какой-то логике, например в ней объединим колонки lastname и firstname, для этого добавим параметр SET, в котором можно использовать операторы, например CONCAT:
LOAD DATA LOCAL INFILE 'd:\\c\\base\\mysql_test\\experiment_csv_3.txt'
INTO TABLE Employees
CHARACTER SET cp1251
FIELDS TERMINATED BY ':'
LINES TERMINATED BY '\r\n'
IGNORE 2 LINES
(firstname, lastname)
SET title = CONCAT(lastname,' ', firstname);
Эксперимент №4 - использование параметра REPLACE.
Обмен данными с помощью CSV-формата на первый взляд очень легко если использовать простые типы данных, но если закрадётся разделительный символ в самой строке? Тогда будут проблемы как на скрине ниже:
Данный параметр REPLACE позволяет изменить данные, но для этого необходимо вернуть в CSV файл колонку empid, т.к. она является первичным ключом в таблице. Модифицируем запрос и вернём файл experiment_csv_3.txt в прежнее состояние:
LOAD DATA LOCAL INFILE 'd:\\c\\base\\mysql_test\\experiment_csv_3.txt'
REPLACE
INTO TABLE Employees
CHARACTER SET cp1251
FIELDS TERMINATED BY ':'
LINES TERMINATED BY '\r\n'
IGNORE 2 LINES
(empid, firstname, lastname)
SET title = CONCAT(lastname,' ', firstname);
С загрузкой данных через LOAD DATA в данной статье закончено.
Переходим к LOAD XML...
Полный синтаксис оператора LOAD XML взят из официальной документации:
LOAD XML
[LOW_PRIORITY | CONCURRENT] [LOCAL]
INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE [db_name.]tbl_name
[CHARACTER SET charset_name]
[ROWS IDENTIFIED BY '<tagname>' ]
[IGNORE number { LINES | ROWS } ]
[ ( field_name_or_user_var
[ , field_name_or_user_var ] ... ) ]
[ SET col_name = { expr | DEFAULT } ,
[ , col_name= { expr | DEFAULT } ] ... ]
Для последующих экспериментов подготовлю несколько XML файлов: атрибутивных и атрибутивно-элементных выгруженных из MS SQL Server 2019 из той же самой таблицы, запросы с помощью которых будут созданы XML файлы представлены ниже:
SELECT empid as [@empid]
, lastname as [@lastname]
, firstname as [@firstname]
, title as [@title]
FROM HR.Employees
ORDER BY empid ASC
FOR XML PATH ('Employee'),ROOT('result');
go
SELECT lastname as [@lastname]
, firstname
, title
FROM HR.Employees
ORDER BY empid ASC
FOR XML PATH ('Employee'),ROOT('resultset');
go
SELECT empid
, lastname
, firstname
, title
FROM HR.Employees
ORDER BY empid ASC
FOR XML PATH ,ROOT('result');
также в каждый XML файл вручную добавлю элемент с кириллицей, для проверки корректности работы.
Эксперимент №5 - загрузка атрибутивного xml.
В данном случае сразу поэкспериментируем с кодировкой и параметром REPLACE.
Работаем с первым файлом XML_F52E2B61-18A1-11d1-B105-00805F49916B8.xml, его содержимое ниже на скрине:
данный файл имеет кодировку UTF8, см. скрин ниже:
но данный файл буду загружать с кодировкой WINDOWS-1251(cp1251) чтобы посмотреть на проблему, с которой можно столкнуться:
LOAD XML LOCAL INFILE 'D:\\c\\base\\mysql_test\\XML_F52E2B61-18A1-11d1-B105-00805F49916B8.xml'
INTO TABLE Employees
CHARACTER SET cp1251
ROWS IDENTIFIED BY '<Employee>';
Использую неправильную кодировку возникает проблема с кириллицей:
обратите внимание, как отобразилась строка с empid=10.
Чтобы исправить сие недоразумение исправим кодировку на UTF8 и также добавим параметр REPLACE:
LOAD XML LOCAL INFILE 'D:\\c\\base\\mysql_test\\XML_F52E2B61-18A1-11d1-B105-00805F49916B8.xml'
REPLACE
INTO TABLE Employees
CHARACTER SET utf8mb4
ROWS IDENTIFIED BY '<Employee>';
Эксперимент №6 - загрузка атрибутивно-элементного xml.
Теперь используем XML_F52E2B61-18A1-11d1-B105-00805F49916B9.xml файл, в котором присутствуют как атрибуты так и элементы:
синтаксис оператора остался прежним, только изменили имя файла:
LOAD XML LOCAL INFILE 'D:\\c\\base\\mysql_test\\XML_F52E2B61-18A1-11d1-B105-00805F49916B9.xml'
INTO TABLE Employees
CHARACTER SET utf8mb4
ROWS IDENTIFIED BY '<Employee>';
результат выполнения см. на скрине ниже:
Эксперимент №7 - загрузка элементного xml.
Используем полностью элементный xml из файла XML_F52E2B61-18A1-11d1-B105-00805F49916B10.xml
В данном xml изменил элемент определяющий строку с <Employee> на <row> - только для эксперимента, соответственно необходимо подправить запрос:
LOAD XML LOCAL INFILE 'D:\\c\\base\\mysql_test\\XML_F52E2B61-18A1-11d1-B105-00805F49916B10.xml'
INTO TABLE Employees
CHARACTER SET utf8mb4
ROWS IDENTIFIED BY '<row>';
результат ниже:
Подведём промежуточный подытог: в предыдущих примерах с xml файлами MySQL корректно распознаёт и загружает элементные, атрибутивные и элементно-атрибутивные xml данные из файлов, но при условии что имена атрибутов и элементов совпадают с именами колонок таблицы!!! Но врят ли будут попадаться xml файлы созданные сторонними разработчиками, в которых элементы и атрибуты будут совпадать с нашими колонками таблиц. Как же быть в этом случае? А вот об этом поговорим в последнем эксперименте.
Эксперимент №8 - финальный.
Создадим xml файл с помощью запроса в MS SQL Server, приведённого ниже:
SELECT empid AS [@column1]
, lastname AS column2
, firstname AS column3
, title AS column4
FROM HR.Employees
ORDER BY empid ASC
FOR XML PATH ,ROOT('result');
в результате получили файл XML_F52E2B61-18A1-11d1-B105-00805F49916B14.xml с содержимым показанным на скрине ниже:
в данном xml нет ни одного атрибута или элемента, который бы совпадал с именем колонки в таблице employees, поэтому запрос нужно доработать:
LOAD XML LOCAL INFILE 'D:\\c\\base\\mysql_test\\XML_F52E2B61-18A1-11d1-B105-00805F49916B12.xml'
INTO TABLE Employees
CHARACTER SET utf8mb4
ROWS IDENTIFIED BY '<row>'
(@column1, @column2, @column3, @column4)
SET empid=@column1, lastname=@column2, firstname=@column3, title=@column4;
результат работы на скрие ниже:
Думаю стоит пояснить последний запрос.
После оператора ROWS IDENTIFIED BY '<row>' в скобках необходимо перечислить все имена атрибутов и элементов с символом @ в качестве префикса, из которых будем загружать данные. Эти названия атрибутов и элементов с символом @ в документации называются "пользовательские переменные"(user variables).
Далее в операторе SET происходит сопоставление колонок таблицы с данными пользовательскими переменными.
Вывод: Необходимо понимать все способы передачи данных как с помощью CSV так и с помощью XML способов. Так же рекомендую изучить более подробно данную тему в официальной документации. Так же ниже будет ссылка на файлы, которые использовались в экспериментах.
Ссылка на архив с файлами.