15.2.10 LOAD XML Statement — MySQL 8.4

15.2.10 Заявление 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}] ...]

Заявление LOAD XML считывает данные из файла XML в таблицу. file_name должно быть задано как строковая константа. tagname в необязательной фразе ROWS IDENTIFIED BY также должно быть задано как строковая константа и должно быть заключено в угловые скобки (< и >).

LOAD XML действует как дополнение к запуску клиента mysql в режиме вывода XML (то есть, запуск клиента с опцией --xml). Чтобы записать данные из таблицы в файл XML, вы можете вызвать клиент mysql с опциями --xml и -e из системной оболочки, как показано здесь:

$> mysql --xml -e 'SELECT * FROM mydb.mytable' > file.xml

Для чтения файла обратно в таблицу используйте LOAD XML. По умолчанию элемент <row> считается эквивалентом строки базы данных; это можно изменить, используя фразу ROWS IDENTIFIED BY.

Это заявление поддерживает три различных формата XML:

Имена столбцов в качестве атрибутов и значения столбцов в качестве значений атрибутов:
```
<row column1="value1" column2="value2" .../>
```
Имена столбцов в качестве тегов и значения столбцов в качестве содержимого этих тегов:
```
<row>
  <column1>value1</column1>
  <column2>value2</column2>
</row>
```
Имена столбцов являются атрибутами name тегов <field>, а значения — содержимым этих тегов:
```
<row>
  <field name='column1'>value1</field>
  <field name='column2'>value2</field>
</row>
```
Этот формат используется другими инструментами MySQL, такими как mysqldump.

Все три формата могут использоваться в одном файле XML; процедура импорта автоматически определяет формат для каждой строки и интерпретирует его правильно. Теги сопоставляются на основе имени тега или атрибута и имени столбца.

Следующие фразы работают по существу одинаково для LOAD XML, как и для LOAD DATA:

LOW_PRIORITY или CONCURRENT
LOCAL
REPLACE или IGNORE
CHARACTER SET
SET

См. Раздел 15.2.9, «Заявление LOAD DATA» для получения дополнительной информации об этих фразах.

(field_name_or_user_var, ...) — это список одного или нескольких разделенных запятыми полей XML или пользовательских переменных. Имя пользовательской переменной, используемой в этой цели, должно совпадать с именем поля из файла XML, с префиксом @. Вы можете использовать имена полей для выбора только необходимых полей. Пользовательские переменные могут использоваться для хранения соответствующих значений поля для последующего повторного использования.

Фраза IGNORE number LINES или IGNORE number ROWS приводит к пропуску первых number строк в файле XML. Это аналогично фразе IGNORE ... LINES заявления LOAD DATA.

Предположим, что у нас есть таблица с именем person, созданная как показано здесь:

USE test;

CREATE TABLE person (
    person_id INT NOT NULL PRIMARY KEY,
    fname VARCHAR(40) NULL,
    lname VARCHAR(40) NULL,
    created TIMESTAMP
);

Предположим также, что эта таблица изначально пуста.

Теперь предположим, что у нас есть простой файл XML person.xml, содержимое которого приведено ниже:

<list>
  <person person_id="1" fname="Kapek" lname="Sainnouine"/>
  <person person_id="2" fname="Sajon" lname="Rondela"/>
  <person person_id="3"><fname>Likame</fname><lname>Örrtmons</lname></person>
  <person person_id="4"><fname>Slar</fname><lname>Manlanth</lname></person>
  <person><field name="person_id">5</field><field name="fname">Stoma</field>
    <field name="lname">Milu</field></person>
  <person><field name="person_id">6</field><field name="fname">Nirtam</field>
    <field name="lname">Sklöd</field></person>
  <person person_id="7"><fname>Sungam</fname><lname>Dulbåd</lname></person>
  <person person_id="8" fname="Sraref" lname="Encmelt"/>
</list>

В этом примере файла представлены все допустимые форматы XML, обсуждавшиеся ранее.

Чтобы импортировать данные из person.xml в таблицу person, вы можете использовать это утверждение:

mysql> LOAD XML LOCAL INFILE 'person.xml'
    ->   INTO TABLE person
    ->   ROWS IDENTIFIED BY '<person>';

Query OK, 8 rows affected (0.00 sec)
Records: 8  Deleted: 0  Skipped: 0  Warnings: 0

Здесь мы предполагаем, что person.xml находится в каталоге данных MySQL. Если файл не найден, возникает следующая ошибка:

ERROR 2 (HY000): File '/person.xml' not found (Errcode: 2)

Фраза ROWS IDENTIFIED BY '<person>' означает, что каждый элемент <person> в файле XML считается эквивалентным строке в таблице, в которую должны быть импортированы данные. В этом случае это таблица person в базе данных test.

Как видно из ответа сервера, 8 строк были импортированы в таблицу test.person. Это можно проверить с помощью простого заявления SELECT:

mysql> SELECT * FROM person;
+-----------+--------+------------+---------------------+
| person_id | fname  | lname      | created             |
+-----------+--------+------------+---------------------+
|         1 | Kapek  | Sainnouine | 2007-07-13 16:18:47 |
|         2 | Sajon  | Rondela    | 2007-07-13 16:18:47 |
|         3 | Likame | Örrtmons   | 2007-07-13 16:18:47 |
|         4 | Slar   | Manlanth   | 2007-07-13 16:18:47 |
|         5 | Stoma  | Nilu       | 2007-07-13 16:18:47 |
|         6 | Nirtam | Sklöd      | 2007-07-13 16:18:47 |
|         7 | Sungam | Dulbåd     | 2007-07-13 16:18:47 |
|         8 | Sreraf | Encmelt    | 2007-07-13 16:18:47 |
+-----------+--------+------------+---------------------+
8 rows in set (0.00 sec)

Это демонстрирует, как упоминалось ранее в этом разделе, что любой или все 3 разрешенных формата XML могут появляться в одном файле и читаться с помощью LOAD XML.

Обратную операцию импорта, то есть экспорт данных таблицы MySQL в файл XML, можно выполнить с помощью клиента mysql из системной оболочки, как показано здесь:

$> mysql --xml -e "SELECT * FROM test.person" > person-dump.xml
$> cat person-dump.xml
<?xml version="1.0"?>

<resultset statement="SELECT * FROM test.person" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
	<field name="person_id">1</field>
	<field name="fname">Kapek</field>
	<field name="lname">Sainnouine</field>
  </row>

  <row>
	<field name="person_id">2</field>
	<field name="fname">Sajon</field>
	<field name="lname">Rondela</field>
  </row>

  <row>
	<field name="person_id">3</field>
	<field name="fname">Likema</field>
	<field name="lname">Örrtmons</field>
  </row>

  <row>
	<field name="person_id">4</field>
	<field name="fname">Slar</field>
	<field name="lname">Manlanth</field>
  </row>

  <row>
	<field name="person_id">5</field>
	<field name="fname">Stoma</field>
	<field name="lname">Nilu</field>
  </row>

  <row>
	<field name="person_id">6</field>
	<field name="fname">Nirtam</field>
	<field name="lname">Sklöd</field>
  </row>

  <row>
	<field name="person_id">7</field>
	<field name="fname">Sungam</field>
	<field name="lname">Dulbåd</field>
  </row>

  <row>
	<field name="person_id">8</field>
	<field name="fname">Sreraf</field>
	<field name="lname">Encmelt</field>
  </row>
</resultset>

Примечание

Опция --xml заставляет клиент mysql использовать форматирование XML для своего вывода; опция -e заставляет клиента немедленно выполнить SQL-запрос, следующий за опцией. См. Раздел 6.5.1, «mysql — Клиент командной строки MySQL».

Вы можете проверить, что дамп действителен, создав копию таблицы person и импортировав файл дампа в новую таблицу, как показано здесь:

mysql> USE test;
mysql> CREATE TABLE person2 LIKE person;
Query OK, 0 rows affected (0.00 sec)

mysql> LOAD XML LOCAL INFILE 'person-dump.xml'
    ->   INTO TABLE person2;
Query OK, 8 rows affected (0.01 sec)
Records: 8  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM person2;
+-----------+--------+------------+---------------------+
| person_id | fname  | lname      | created             |
+-----------+--------+------------+---------------------+
|         1 | Kapek  | Sainnouine | 2007-07-13 16:18:47 |
|         2 | Sajon  | Rondela    | 2007-07-13 16:18:47 |
|         3 | Likema | Örrtmons   | 2007-07-13 16:18:47 |
|         4 | Slar   | Manlanth   | 2007-07-13 16:18:47 |
|         5 | Stoma  | Nilu       | 2007-07-13 16:18:47 |
|         6 | Nirtam | Sklöd      | 2007-07-13 16:18:47 |
|         7 | Sungam | Dulbåd     | 2007-07-13 16:18:47 |
|         8 | Sreraf | Encmelt    | 2007-07-13 16:18:47 |
+-----------+--------+------------+---------------------+
8 rows in set (0.00 sec)

Нет требования, чтобы каждое поле в файле XML соответствовало столбцу в соответствующей таблице. Поля, не имеющие соответствующих столбцов, пропускаются. Вы можете увидеть это, сначала очистив таблицу person2 и удалив столбец created, а затем используя то же утверждение LOAD XML, что мы использовали ранее, как показано здесь:

mysql> TRUNCATE person2;
Query OK, 8 rows affected (0.26 sec)

mysql> ALTER TABLE person2 DROP COLUMN created;
Query OK, 0 rows affected (0.52 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE person2\G
*************************** 1. row ***************************
       Table: person2
Create Table: CREATE TABLE `person2` (
  `person_id` int NOT NULL,
  `fname` varchar(40) DEFAULT NULL,
  `lname` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> LOAD XML LOCAL INFILE 'person-dump.xml'
    ->   INTO TABLE person2;
Query OK, 8 rows affected (0.01 sec)
Records: 8  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM person2;
+-----------+--------+------------+
| person_id | fname  | lname      |
+-----------+--------+------------+
|         1 | Kapek  | Sainnouine |
|         2 | Sajon  | Rondela    |
|         3 | Likema | Örrtmons   |
|         4 | Slar   | Manlanth   |
|         5 | Stoma  | Nilu       |
|         6 | Nirtam | Sklöd      |
|         7 | Sungam | Dulbåd     |
|         8 | Sreraf | Encmelt    |
+-----------+--------+------------+
8 rows in set (0.00 sec)

Порядок, в котором поля задаются в каждой строке файла XML, не влияет на работу LOAD XML; порядок полей может меняться от строки к строке и не обязательно должен совпадать с порядком соответствующих столбцов в таблице.

Как упоминалось ранее, вы можете использовать список (field_name_or_user_var, ...) одного или нескольких полей XML (для выбора только необходимых полей) или пользовательских переменных (для хранения соответствующих значений полей для последующего использования). Пользовательские переменные могут быть особенно полезны, когда вы хотите вставить данные из файла XML в столбцы таблицы, имена которых не совпадают с именами полей XML. Чтобы увидеть, как это работает, мы сначала создадим таблицу с именем individual, структура которой соответствует структуре таблицы person, но имена столбцов разные:

mysql> CREATE TABLE individual (
    ->     individual_id INT NOT NULL PRIMARY KEY,
    ->     name1 VARCHAR(40) NULL,
    ->     name2 VARCHAR(40) NULL,
    ->     made TIMESTAMP
    -> );
Query OK, 0 rows affected (0.42 sec)

В этом случае вы не можете просто загрузить файл XML непосредственно в таблицу, потому что имена полей и столбцов не совпадают:

mysql> LOAD XML INFILE '../bin/person-dump.xml' INTO TABLE test.individual;
ERROR 1263 (22004): Column set to default value; NULL supplied to NOT NULL column 'individual_id' at row 1

Это происходит потому, что сервер MySQL ищет имена полей, соответствующие именам столбцов целевой таблицы. Вы можете обойти эту проблему, выбрав значения полей в пользовательские переменные, а затем установив столбцы целевой таблицы равными значениям этих переменных с использованием SET. Вы можете выполнить обе эти операции в одном заявлении, как показано здесь:

mysql> LOAD XML INFILE '../bin/person-dump.xml'
    ->     INTO TABLE test.individual (@person_id, @fname, @lname, @created)
    ->     SET individual_id=@person_id, name1=@fname, name2=@lname, made=@created;
Query OK, 8 rows affected (0.05 sec)
Records: 8  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM individual;
+---------------+--------+------------+---------------------+
| individual_id | name1  | name2      | made                |
+---------------+--------+------------+---------------------+
|             1 | Kapek  | Sainnouine | 2007-07-13 16:18:47 |
|             2 | Sajon  | Rondela    | 2007-07-13 16:18:47 |
|             3 | Likema | Örrtmons   | 2007-07-13 16:18:47 |
|             4 | Slar   | Manlanth   | 2007-07-13 16:18:47 |
|             5 | Stoma  | Nilu       | 2007-07-13 16:18:47 |
|             6 | Nirtam | Sklöd      | 2007-07-13 16:18:47 |
|             7 | Sungam | Dulbåd     | 2007-07-13 16:18:47 |
|             8 | Srraf  | Encmelt    | 2007-07-13 16:18:47 |
+---------------+--------+------------+---------------------+
8 rows in set (0.00 sec)

Имена пользовательских переменных должны совпадать с именами соответствующих полей из файла XML с добавлением требуемого префикса @, чтобы указать, что это переменные. Пользовательские переменные не обязательно должны быть перечислены или назначены в том же порядке, что и соответствующие поля.

Используя фразу ROWS IDENTIFIED BY '<tagname>', можно импортировать данные из одного и того же файла XML в таблицы базы данных с различными определениями. Для этого примера предположим, что у вас есть файл с именем address.xml, который содержит следующее XML:

<?xml version="1.0"?>

<list>
  <person person_id="1">
    <fname>Robert</fname>
    <lname>Jones</lname>
    <address address_id="1" street="Mill Creek Road" zip="45365" city="Sidney"/>
    <address address_id="2" street="Main Street" zip="28681" city="Taylorsville"/>
  </person>

  <person person_id="2">
    <fname>Mary</fname>
    <lname>Smith</lname>
    <address address_id="3" street="River Road" zip="80239" city="Denver"/>
    <!-- <address address_id="4" street="North Street" zip="37920" city="Knoxville"/> -->
  </person>

</list>

Вы снова можете использовать таблицу test.person, как определено ранее в этом разделе, после очистки всех существующих записей из таблицы и отображения ее структуры, как показано здесь:

mysql< TRUNCATE person;
Query OK, 0 rows affected (0.04 sec)

mysql< SHOW CREATE TABLE person\G
*************************** 1. row ***************************
       Table: person
Create Table: CREATE TABLE `person` (
  `person_id` int(11) NOT NULL,
  `fname` varchar(40) DEFAULT NULL,
  `lname` varchar(40) DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

Теперь создайте таблицу address в базе данных test, используя следующее утверждение CREATE TABLE:

CREATE TABLE address (
    address_id INT NOT NULL PRIMARY KEY,
    person_id INT NULL,
    street VARCHAR(40) NULL,
    zip INT NULL,
    city VARCHAR(40) NULL,
    created TIMESTAMP
);

Чтобы импортировать данные из файла XML в таблицу person, выполните следующее утверждение LOAD XML, которое указывает, что строки должны быть заданы элементом <person>, как показано здесь:

mysql> LOAD XML LOCAL INFILE 'address.xml'
    ->   INTO TABLE person
    ->   ROWS IDENTIFIED BY '<person>';
Query OK, 2 rows affected (0.00 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

Вы можете проверить, что записи были импортированы, используя утверждение SELECT:

mysql> SELECT * FROM person;
+-----------+--------+-------+---------------------+
| person_id | fname  | lname | created             |
+-----------+--------+-------+---------------------+
|         1 | Robert | Jones | 2007-07-24 17:37:06 |
|         2 | Mary   | Smith | 2007-07-24 17:37:06 |
+-----------+--------+-------+---------------------+
2 rows in set (0.00 sec)

Поскольку элементы <address> в файле XML не имеют соответствующих столбцов в таблице person, они пропускаются.

Для импорта данных из элементов <address> в таблицу address используйте оператор LOAD XML, показанный здесь:

mysql> LOAD XML LOCAL INFILE 'address.xml'
    ->   INTO TABLE address
    ->   ROWS IDENTIFIED BY '<address>';
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Вы можете увидеть, что данные были импортированы с помощью оператора SELECT, например, такого:

mysql> SELECT * FROM address;
+------------+-----------+-----------------+-------+--------------+---------------------+
| address_id | person_id | street          | zip   | city         | created             |
+------------+-----------+-----------------+-------+--------------+---------------------+
|          1 |         1 | Mill Creek Road | 45365 | Sidney       | 2007-07-24 17:37:37 |
|          2 |         1 | Main Street     | 28681 | Taylorsville | 2007-07-24 17:37:37 |
|          3 |         2 | River Road      | 80239 | Denver       | 2007-07-24 17:37:37 |
+------------+-----------+-----------------+-------+--------------+---------------------+
3 rows in set (0.00 sec)

Данные из элемента <address>, заключённого в XML-комментарии, не импортируются. Однако, поскольку в таблице address есть столбец person_id, значение атрибута person_id из родительского элемента <person> для каждого элемента <address> импортируется в таблицу address.

Меры безопасности. Как и с оператором LOAD DATA, передача XML-файла с хоста клиента на хост сервера инициируется сервером MySQL. Теоретически, можно создать модифицированный сервер, который будет указывать клиентской программе на передачу файла по выбору сервера, а не файла, указанного клиентом в операторе LOAD XML. Такой сервер мог бы получить доступ к любому файлу на хосте клиента, к которому у пользователя клиента есть доступ для чтения.

В веб-среде клиенты обычно подключаются к MySQL с веб-сервера. Пользователь, который может выполнить любую команду на сервере MySQL, может использовать LOAD XML LOCAL для чтения любых файлов, к которым у процесса веб-сервера есть доступ для чтения. В этой среде клиент по отношению к серверу MySQL фактически является веб-сервером, а не удалённой программой, выполняемой пользователем, который подключается к веб-серверу.

Вы можете отключить загрузку XML-файлов с клиентов, запустив сервер с параметром --local-infile=0 или --local-infile=OFF. Этот параметр также можно использовать при запуске клиента mysql для отключения LOAD XML на период сеанса клиента.

Чтобы предотвратить загрузку XML-файлов клиентом с сервера, не предоставляйте привилегию FILE соответствующей учётной записи пользователя MySQL или отозвите эту привилегию, если учётная запись пользователя клиента уже её имеет.

Важно

Лишение привилегии FILE (или отказ от её предоставления изначально) позволяет пользователю только не выполнять оператор LOAD XML (а также функцию LOAD_FILE()); это не мешает пользователю выполнять оператор LOAD XML LOCAL. Чтобы запретить выполнение этого оператора, необходимо запустить сервер или клиента с параметром --local-infile=OFF.

Другими словами, привилегия FILE влияет только на возможность клиента читать файлы на сервере; она не влияет на возможность клиента читать файлы на локальной файловой системе.