Использование INFORMATION_SCHEMA в хранимых процедурах
В MySQL версии 5.0 и выше есть база INFORMATION_SCHEMA. Это виртуальная база (не хранится в виде файлов, а формируется во время запуска сервера), содержащая метаданные баз данных, т.е. информацию о структуре баз данных. Доступна только для чтения.
Преимущества INFORMATION_SCHEMA:
- Для получения любых данных из INFORMATION_SCHEMA мы всегда используем синтаксис оператора SELECT, в то время как запросы семейства SHOW имеют разный синтаксис для разных данных.
- Большая свобода в выводе данных за счет использования ограничений выборки, объединений, подзапросов, группировок, сортировок и т.д.
- Использование выражений семейства SHOW позволяет получить лишь малую часть метаданных доступных через INFORMATION_SCHEMA
- Это стандарт SQL, в отличии от запросов семейства SHOW, являющихся расширением MySQL. Таким образом, приложения использующие INFORMATION_SCHEMA легче переносятся на другие базы данных. (Не следует, однако, думать, что вся структура INFORMATION_SCHEMA регламентируется стандартом - значительное количество таблиц и колонок специфичны для MySQL)
Многие задачи можно решить только с использованием INFORMATION_SCHEMA. Например, часто встречающаяся задача выборки записей из таблицы по совпадению шаблона с некоторым столбцом. Т.е. нужно чтобы процедура принимала строку str и если имя столбца(ов) совпадает с шаблоном, то возвращала результат выборки по этим столбцам. Сложность заключается в том, что синтаксис MySQL запрещает использовать регулярные выражения в части перечисления столбцов выражения SELECT (исключением является *). Для преодоления этого затруднения нам нужно использовать подготовленные выражения (prepared statement). Но чтобы создать подготовленное выражение с заранее известным количеством столбцов и их имен, необходимо сначала определить столбцы удовлетворяющие шаблону str. Для этой цели и потребуются возможности предоставляемые INFORMATION_SCHEMA.
Создадим для иллюстрации таблицу следующего вида:
Query OK, 0 rows affected (0.16 sec)
В INFORMATION_SCHEMA есть таблица COLUMNS, содержащая информацию о колонках в таблицах. С её помощью можно определить столбцы, удовлетворяющие шаблону. Например, для определения столбцов, начинающихся на col, из нашей тестовой таблицы следует использовать следующее выражение:
test' AND table_name='test_table' AND column_name LIKE 'col%';
+---------------------------+
| group_concat(column_name) |
+---------------------------+
| col1,col2 |
+---------------------------+
1 row in set (0.00 sec)
Для создания нужного нам подготовленного выражения с заранее известными столбцами, мы будем использовать результат выборки из INFORMATION_SCHEMA.COLUMNS, сохраненный в пользовательской переменной. Однако если в таблице не окажется столбцов удовлетворяющих шаблону, то результатом выборки будет NULL, а попытка создать подготовленное выражение из NULL приведет к ошибке:
to your MySQL server version for the right syntax to use near 'NULL' at line 1
В результате процедура, принимающая в качестве параметров имя таблицы и строку str и выводящая содержимое тех столбцов, имена которых содержат в себе строку str, будет иметь следующий вид.
create procedure search_patten(str char(64), table_name char(64))
begin
declare exit handler for sqlstate '42000' select 'not found';
set @v = concat("select group_concat(column_name) from information_schema.columns where
table_schema='test' and table_name='",table_name,"' and column_name like '%",str,"%' into @var");
prepare zxc from @v;
execute zxc;
set @v = concat('select ',@var, ' from ', table_name);
prepare zxc from @v;
execute zxc;
end;
//
Проверим работу созданной нами процедуры:
Query OK, 1 row affected (0.42 sec)
test >call search_patten('col','test_table');
+------+------+
| col1 | col2 |
+------+------+
| 1 | 2 |
+------+------+
1 row in set (0.02 sec)
test >call search_patten('no_such_column','test_table');
+-----------+
| not found |
+-----------+
| not found |
+-----------+
1 row in set (0.03 sec)
© Все права на данную статью принадлежат порталу webew.ru. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в печатных изданиях допускается только с разрешения редакции.
