Работаем с XML данными и XPath в MySQL

Базы данных, Программирование 13.04.2009

mysql-xpath

Как известно, в MySQL 5.1 внедрили функции для работы с XML, сегодня я расскажу про эти две функции более подробно, а так же вкратце опишу как работать с XPath в MySQL.

Для работы с XML есть две функции:

  1. ExtractValue () — Позволяет выбирать записи средствами XPAth;
  2. UpdateXML () — Возвращает измененный XML-фрагмент.

Функция ExtractValue и XPath

Синтаксис следующий:

ExtractValue (xml_frag, xpath_expr)

Для примеров, я буду работать с переменной, но с тем же успехом вы можете записать XML-фрагмент в любое поле с текстовым типом данных. XPath поддерживается на любом движке.

Создадим переменную @xml:

mysql> SET @xml = '<x id="xx"><y id="yy"><z id="zz">test</z></y></x>';

Выберем XML-фрагмент с помощью XPath:

mysql> SELECT @xml, ExtractValue(@xml, '//z');
+---------------------------------------------------+---------------------------+
| @xml                                              | ExtractValue(@xml, '//z') |
+---------------------------------------------------+---------------------------+
| <x id="xx"><y id="yy"><z id="zz">test</z></y></x> | test                      |
+---------------------------------------------------+---------------------------+
1 ROW IN SET (0.00 sec)

Как видно, мы выбрали содержимое ноды <z> -> «test».

Примеры:

Немного ознакомимся с языком запросов XPath и приведу несколько примеров.

Создадим для примера такую вот переменную:

mysql> SET @xml = '<z><x>x1</x><x>x2</x><y id="y-id">y-value</y></z>';

1) Посчитаем количество элементов с нодой <x>:

mysql> SELECT @xml, ExtractValue(@xml, 'count(//x)');
+---------------------------------------------------+----------------------------------+
| @xml                                              | ExtractValue(@xml, 'count(//x)') |
+---------------------------------------------------+----------------------------------+
| <z><x>x1</x><x>x2</x><y id="y-id">y-value</y></z> | 2                                |
+---------------------------------------------------+----------------------------------+
1 ROW IN SET (0.00 sec)

2) Выведем содержимое первой ноды <x>:

mysql> SELECT @xml, ExtractValue(@xml, '//x[1]');
+---------------------------------------------------+------------------------------+
| @xml                                              | ExtractValue(@xml, '//x[1]') |
+---------------------------------------------------+------------------------------+
| <z><x>x1</x><x>x2</x><y id="y-id">y-value</y></z> | x1                           |
+---------------------------------------------------+------------------------------+
1 ROW IN SET (0.00 sec)

3) Выведем содержимое ноды с атрибутом id и значением атрибута «y-id»:

mysql> SELECT @xml, ExtractValue(@xml, '//y[@id="y-id"]');
+---------------------------------------------------+---------------------------------------+
| @xml                                              | ExtractValue(@xml, '//y[@id="y-id"]') |
+---------------------------------------------------+---------------------------------------+
| <z><x>x1</x><x>x2</x><y id="y-id">y-value</y></z> | y-VALUE                               |
+---------------------------------------------------+---------------------------------------+
1 ROW IN SET (0.00 sec)

В данный момент MySQL поддерживает XPath версии 1.0, рекомендую посмотреть переведенную спецификацию по этой версии языка.

Функция UpdateXML

С помощью функции UpdateXML мы можем изменять какие-либо XML-фрагменты. Используется для изменения XML-фрагмента и сохранения в базу данных, например при UPDATE.

Синтаксис следующий:

UpdateXML (xml_target, xpath_expr, new_xml)

Пример:

1) Заменим первую ноду «<x>» на «<c>test</c>»:

mysql> SELECT @xml, UpdateXML(@xml, '//x[1]', '<c>test</c>');
+---------------------------------------------------+-----------------------------------------------------+
| @xml                                              | UpdateXML(@xml, '//x[1]', '<c>test</c>')            |
+---------------------------------------------------+-----------------------------------------------------+
| <z><x>x1</x><x>x2</x><y id="y-id">y-value</y></z> | <z><c>test</c><x>x2</x><y id="y-id">y-value</y></z> |
+---------------------------------------------------+-----------------------------------------------------+
1 ROW IN SET (0.00 sec)

2) Заменим содержимое ноды с атрибутом id и значением атрибута «y-id» на «<c>test</c>»:

mysql> SELECT @xml, UpdateXML(@xml, '//y[@id="y-id"]', '<c>test</c>');
+---------------------------------------------------+---------------------------------------------------+
| @xml                                              | UpdateXML(@xml, '//y[@id="y-id"]', '<c>test</c>') |
+---------------------------------------------------+---------------------------------------------------+
| <z><x>x1</x><x>x2</x><y id="y-id">y-value</y></z> | <z><x>x1</x><x>x2</x><c>test</c></z>              |
+---------------------------------------------------+---------------------------------------------------+
1 ROW IN SET (0.00 sec)

Еще больше примеров на странице руководства по XML-функциям, так же рекомендую почитать о XPath в википедии и ознакомится с переведенной спецификацией XPath версии 1.0.

Производительность

Потестим производительность на моей машине для разработки:

Microsoft Windows XP [Версия 5.1.2600] SP3
Mysql 5.1.26

Intel Core Duo T2050 1.6 GHz (L2 Cache 2MB, Bus Speed 133 MHZ, Rated FSB 533 MHz)
Memory Type DDR2, Dual Channels, Size 1024 MB (Frequency 266 MHz)

Для теста создадим переменную:

mysql> SET @xml = '<a><b></b><b><c id="c-id">c-value</c></b></a>';

Проверим на выборку 1 000 000 раз.

mysql> SELECT benchmark(1000000, ExtractValue(@xml, '/a/b/c'));
+--------------------------------------------------+
| benchmark(1000000, ExtractValue(@xml, '/a/b/c')) |
+--------------------------------------------------+
|                                                0 |
+--------------------------------------------------+
1 ROW IN SET (4.77 sec)

Проверим на изменение XML-фрагмента 1 000 000 раз.

mysql> SELECT benchmark(1000000, UpdateXML(@xml, '/a/b/c', '<c id="c-id">c-value</c>'));
+---------------------------------------------------------------------------+
| benchmark(1000000, UpdateXML(@xml, '/a/b/c', '<c id="c-id">c-value</c>')) |
+---------------------------------------------------------------------------+
|                                                                         0 |
+---------------------------------------------------------------------------+
1 ROW IN SET (4.47 sec)

Выводы никакие делать не буду, так как тесты слишком приблизительны. Надо тестировать на конкретной машине, желательно не под Windows (на Linux или под OpenSolaris с конкретно собранной для этой ОСи движком MySQL) и надо тестить конкретные XPath-запросы!

UPD: Moscow MySQL User Group: Для чего XPath в MySQL?

Retweet | Оставить комментарий | Комментарии в RSS | Теги: , ,

Назад: Отчет по Sun Tech Days 2009
Далее: Отговорки программистов, когда что-то не работает :)

Комментарии (6) на запись «Работаем с XML данными и XPath в MySQL»

» Трекбеки скрыты, показать их?
  1. CTAPbIu_MABP ↓ | 13.04.2009 в 21:11

    куль! тока нах это в реальных учловиях?

  2. adw0rd ↓ | 13.04.2009 в 21:55

    Если вы используете XML и XPath, то возможно вам будет удобнее оперировать данными в самом MySQL, не пользуясь сторонними средствами.

    Например, вы запрашиваете сначала XML (SQL SELECT), потом через XPath производите выборку (например средствами PHP), заменяете один XML-фрагмент на другой и сохраняете в БД (SQL UPDATE). А можете сразу же в MySQL это сделать.

    Так же можете создавать XML базы данных и использовать XPath как альтернативный язык запросов. От SQL синтаксиса вы конечно не сможете полностью отказаться, да и данные буду храниться в TEXT ячейках. Однако это просто еще один слой абстракции...

    Если вы работает с XML и XPath, то думаю найдете применение... :)

    pyha.ru/forum/topic/2312.0

  3. CTAPbIu_MABP ↓ | 13.04.2009 в 22:07

    лучше бы транзакции и хранимые процедуры оптимизировали

  4. adw0rd ↓ | 14.04.2009 в 19:31

    Вот тему открыл в MMUG — Moscow MySQL User Group: Для чего XPath в MySQL?

  5. korbax ↓ | 14.09.2009 в 21:47

    Скажите, а работают ли эти функции с клиентом mysql 5.0.81, или только начиная с 5.1.х?

  6. adw0rd ↓ | 14.09.2009 в 21:58

    korbax, это работает с версии 5.1

Оставить комментарий