13.2.7 LOAD XML Statement — MySQL 5.7

13.2.7 Заявление 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; процедура импорта автоматически определяет формат для каждой строки и интерпретирует ее правильно. Теги сопоставляются на основе имени тега или атрибута и имени столбца.

В MySQL 5.7, LOAD XML не поддерживает CDATA секции в исходном XML. Это ограничение устранено в MySQL 8.0. (Ошибка #30753708, Ошибка #98199)

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

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

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

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

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

Предположим, что у нас есть таблица с именем 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, следующий за этой опцией. См. Раздел 4.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(11) NOT NULL,
  `fname` varchar(40) DEFAULT NULL,
  `lname` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
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=MyISAM DEFAULT CHARSET=latin1
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 с сервера, не предоставляйте соответствующей учётной записи MySQL привилегию FILE, или отозвать эту привилегию, если учётная запись пользователя-клиента уже имеет её.

Важно

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

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

Для разнесённых таблиц, использующих движки хранения, которые используют блокировки таблиц, такие как MyISAM, любые блокировки, вызванные оператором LOAD XML, применяют блокировки ко всем разделам таблицы. Это не относится к таблицам, использующим движки хранения, которые используют блокировку на уровне строк, такие как InnoDB. Более подробную информацию см. в разделе 22.6.4, «Разбиение и блокировка».