Хранимые процедуры в&nb p;вашем арсенале

Хорошее веб-приложение — быстрое веб-приложение. Один из подходов для создания быстрых приложений — разделение труда. Под трудом в данном контексте понимается обслуживание запросов пользователей, поэтому имеет смысл разгрузить переутомившийся сервер приложений (скажем, PHP) и напрячь прохлаждающийся сервер базы данных (скажем, MySQL). Один из подходов этого направления предполагает перенос логики на сервер БД, что заключается в создании хранимых процедур — нескольких SQL-запросов, объединенных под одним именем. Как это сделать и что это дает?

 

 

Давайте посмотрим.

Сразу стоит отметить, что хранимые процедуры — отнюдь не панацея, и стоит отдавать себе отчет, когда их использовать, а когда воздержаться: вместо ускорения работы приложения можно получить реальное замедление, что никак не порадует ваших пользователей.

Чаще всего хранимые процедуры применяются в таких случаях:

• Чтобы ускорить приложение, в систему добавляется еще один физический сервер специально под базу данных и максимум работы делается на нем (скажем, теперь данные из базы поступают уже в отформатированном виде или рекурсивный обход таблиц для построения дерева наследования записей делается одним обращением к хранимой процедуре). При этом меньший объем информации передается с сервера базы данных или на него, что может быть весьма критично, если серверы связаны не локальной сетью, а Интернетом, что нередко встречается в крупных веб-системах.
• Клиентские приложения написаны на разных языках (PHP, .NET, Python) или работают на разных платформах (Windows, Linux, Mac). Это чревато как минимум мелкими неудобствами вроде того, являются ли имена таблиц регистрочувствительными или нет. Чтобы этого избежать, создаются «хранимки» (так хранимые процедуры называются на сленге программистов), а клиентские приложения работают с базой данных через идентичные вызовы этих процедур.
• Безопасность превыше всего. Например, создается веб-приложение для банка, в таком случае вся логика перемещается в «хранимки», что, во-первых, гарантирует, что каждая операция будет залогирована, и, во-вторых, пользователям БД даются разрешения на хранимые процедуры и забираются на остальные объекты базы: теперь никто, кроме хранимых процедур, не сможет напрямую работать с таблицами, а значит, последние в относительной безопасности от злоумышленников.

Скомпилированные функции выполняются намного быстрее

Также хранимые процедуры могут ускорять работу приложения за счет того, что они хранятся на сервере БД в скомпилированном виде, что предотвращает потерю времени на повторный разбор и интерпретацию SQL-запроса.

Вообще, кроме хранимых процедур бывают еще и функции (они же UDF — User Defined Functions). Разница между ними в том, что по определению функция возвращает всегда только одно значение, поэтому может использоваться в выражениях, а процедура вызывается сама по себе. На практике в MySQL кроме этого различаются и SQL-команды для вызова процедур и функций, но об этом позже.

В MySQL хранимые процедуры и функции поддерживаются начиная с версии 5. Официальный сайт гласит, что эта часть сервера еще в доработке, но уже то, что готово на данный момент, позволяет творить чудеса. Чтобы не быть голословным, мы напишем функцию для транслитерации текста силами базы данных MySQL 5.

Транслитерация текста — это замена, скажем, русских букв похожими по звучанию английскими, то есть было «привет», а после транслитерации стало privet. Фокус в том, что транслитерицировать можно не только с русского языка, но и с любого не английского. "Зачем?" — спросит любознательный читатель. А, например, для полной интеграции с индексирующим роботом Google, ведь согласно «Справочному центру Google для веб-мастеров» нужно, чтобы адреса ваших страниц были «говорящими» (или так называемыми человекопонятными) и не содержали &id= в адресе, следовательно, данные со страницы, скажем название текущего продукта, можно транслитерицировать и добавить в URL.

Итак, сейчас мы напишем функцию translit(), которая в итоге будет вызываться с помощью SQL-запроса следующим образом:

SELECT translit('привет');

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

CREATE TABLE IF NOT EXISTS `translit` (
`id` int(10) unsigned NOT NULL auto_increment,
`char_from` char(1) NOT NULL,
`char_to` char(3) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Данные в нее можно добавлять подобными запросами:

INSERT INTO `translit` VALUES (1, 'а', 'a');
INSERT INTO `translit` VALUES (2, 'б', 'b');
INSERT INTO `translit` VALUES (3, 'в', 'v');
INSERT INTO `translit` VALUES (4, 'г', 'g');

Теперь создайте текстовый файл translit.sql. Вообще, рекомендуется давать «говорящие» имена файлам, чтобы потом не было мучительно больно при поисках исходников нужной функции. Этот файл будет содержать несколько команд, которые одна за другой будут обработаны сервером БД после того, как мы укажем данный файл для запуска.

Первой строкой в таком файле обычно идет:

delimiter //;

Эта незамысловатая команда заменяет стандартный разделитель SQL-запросов «точка с запятой» на новый — «два обратных слеша». Это делается для того, чтобы наша многострочная функция могла содержать в себе команды с точкой с запятой между ними и парсер продолжал считать нашу функцию единым целым, а не набором разрозненных команд.

Следующая строка нужна для отладки — мы будем изменять нашу функцию, и, чтобы изменения вступали в силу незамедлительно, саму функцию нужно предварительно удалять из БД.

DROP FUNCTION IF EXISTS translit;\g

Директива IF EXISTS страхует нас от появления сообщений об ошибке, когда функция еще не создана. Команда "\g" в конце сигнализирует компилятору о том, что команду нужно выполнить немедленно.

Далее следует текст функции (с комментариями по ходу действия).

В итоге наш файл будет выглядеть так:

delimiter //;
DROP FUNCTION IF EXISTS translit;\g
CREATE FUNCTION translit(s VARCHAR(500) CHARACTER SET utf8)
RETURNS VARCHAR(500) CHARACTER SET utf8
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE output VARCHAR(500) CHARACTER SET utf8 DEFAULT '';
DECLARE str_len INT DEFAULT CHAR_LENGTH(s);
DECLARE c VARCHAR(1) CHARACTER SET utf8; /* очередной символ */
DECLARE is_trans_present TINYINT UNSIGNED; /* есть ли перевод */
DECLARE c_trans VARCHAR(3) CHARACTER SET utf8; /* транс-ный символ */
DECLARE ok_symbols VARCHAR(255) CHARACTER SET utf8
DEFAULT '-01234567890 abcdefghijklmnopqrstuvwxyz';
/* символы, которые переводить вообще не надо, - они и так готовы для работы */

SET s = TRIM(s); /* аккуратно обрезаем строку и переводим в нижний регистр */
SET s = LCASE(CONVERT(s USING utf8));

WHILE i <=str_len DO /* проходим по входящей строке */
SET c_trans = '';
SET is_trans_present = 0;
SET c = CONVERT(SUBSTR(s, i, 1) USING utf8); /* берем очередной символ */

IF LOCATE(c, ok_symbols) = 0 THEN /* нет в списке готовых символов,
будет обрабатывать */
SELECT COUNT(`char_to`) INTO is_trans_present FROM `translit` WHERE `char_from`=c;
IF is_trans_present > 0 THEN
SELECT `char_to` INTO c_trans FROM `translit` WHERE `char_from`=c LIMIT 1;
/* теперь перевод попал в переменную c_trans */
IF c_trans IS NOT NULL AND c_trans<>'' THEN /* символ был успешно переведен */
SET output = CONCAT(output, c_trans);
END IF;
END IF;
ELSE
SET output = CONCAT(output, c); /* текущий символ находится в списке готовых - просто копируем его в выходную строку */
END IF;
SET i = i + 1;
END WHILE;

RETURN output;
END\g

Теперь необходимо «скормить» этот файл базе данных. Для этого создаем файл translit.sql.bat, содержащий всего одну строку:

mysql -u USERNAME --password=PASSWORD DATABASE < translit.sql

Запускаем его.

Три слова в верхнем регистре (имя пользователя, пароль и имя БД) нужно заменить своими данными. Также нужно проследить, чтобы путь к файлу mysql.exe был представлен в переменной окружения PATH, иначе его придется прописывать явно (как правило, это что-то вроде "C:\Program Files\MySQL\MySQL Server 5.0\bin\").

Кодировка utf8 упрощает работу с разными языками

Теперь можно пользоваться. Чтобы добавить поддержку, скажем, французского языка, просто вбейте специальные французские символы (они с крышечкой сверху) в таблицу переводов и, соответственно, английский аналог. Если возникнут проблемы с отображением русских букв, попробуйте сразу после старта своего приложения выполнять такой запрос:

SET NAMES utf8;

Функция может вызываться в выражениях, например, прямо в SELECT-запросе, как в примере выше. Хранимая процедура, напротив, вызывается специальной командой:

CALL simpleproc(@a);

Создавая свои функции и процедуры, держите в голове эти не совсем очевидные моменты:

1. Хранимые процедуры не могут содержать некоторые SQL-запросы вроде LOAD DATA INFILE или OPTIMIZE TABLES.
2. Функция возвращает только скалярные значения — числа, строки и так далее, т. е. вы не можете вернуть рекорд-сет (таблицу данных).
3. Если вам нужно обработать каким-либо образом каждую запись рекорд-сета, используйте специальную фичу — курсоры. Примеры есть в мануале.

Теперь полученных знаний достаточно для решения целого спектра общих задач. Просто начните — и все получится.

Ссылки по теме

• MySQL 5.0 Reference Manual
• Справочный центр Google для веб-мастеров
• Google-группа UA MYSQL

Статья получена: hostinfo.ru

загрузка...