webew
Войти » Регистрация
 
MySQL/MariaDB

Использование INFORMATION_SCHEMA в хранимых процедурах

22 февраля 2008, 3:46

В 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.

Создадим для иллюстрации таблицу следующего вида:

test > CREATE TABLE test_table (id int, col1 int, col2 int, other text);
Query OK, 0 rows affected (0.16 sec)

В INFORMATION_SCHEMA есть таблица COLUMNS, содержащая информацию о колонках в таблицах. С её помощью можно определить столбцы, удовлетворяющие шаблону. Например, для определения столбцов, начинающихся на col, из нашей тестовой таблицы следует использовать следующее выражение:

test >SELECT group_concat(column_name) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='
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 приведет к ошибке:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds
 to your MySQL server version for the right syntax to use near 'NULL' at line 1
Для избежания прерывания процедуры с выводом ошики, необходимо в тело процедуры включить обработчик этой ошибки
declare exit handler for sqlstate '42000' select 'not found';

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

delimiter //
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;
//

Проверим работу созданной нами процедуры:

test >insert into test_table values(0,1,2,'other');
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. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в печатных изданиях допускается только с разрешения редакции.
Добавить комментарий
© 2008—2017 webew.ru, связаться: x собака webew.ru
Сайт использует Flede и соответствует стандартам WAI-WCAG 1.0 на уровне A.
Rambler's Top100

Реклама: