Хранимые процедуры в MySQL и PHP
Если по простому, то хранимые процедуры (« ХП ») представляют собой хранимые в базе данных процедуры (написанные с помощью SQL и других операторов), которые можно назвать движком базы данных или связанным языком программирования.
В этой статье я расскажу, как с помощью MySQL создать ХП и выполнить ее на сервере MySQL и через PHP .
Примечание : Мы не собираемся осветить здесь все аспекты, касающиеся ХП. Для получения справки по незатронутым в статье вопросам вы всегда можете воспользоваться официальной документацией MySQL .
ХП также поддерживаются на других распространенных серверах баз данных ( Postgre , например), так что то, что мы сегодня будем обсуждать, также применимо и к ним.
Почему рекомендуют использовать хранимые процедуры
Большинство из нас хорошо знакомы с обычными настройками, которые позволяют создать приложение базы данных: создание базы данных, создание таблиц, создание индексов, CRUD-данные, формирование запросов со стороны клиента и дальнейшая обработка, если это необходимо .
Этот процесс отлично работает в большинстве случаев, но есть один важный аспект, который программирование баз данных не охватывает: хранимая процедура.
Я могу назвать, по крайней мере, четыре веских причины использовать ХП в приложениях баз данных. Во-первых , она уменьшает сетевой трафик и нагрузку на сервер.
В стандартном PHP веб-приложении базы данных существует четыре составляющих:
- Клиентский уровень , который обычно представлен веб-браузером. Он предоставляет возможность взаимодействия с пользователем и позволяет вводить данные через интерфейс пользователя;
- Уровень веб-сервера , на котором обрабатываются запросы пользователей и отправляются ответы назад на клиентский уровень;
- PHP-уровень , который обрабатывает все PHP-составляющие, создает логику приложений и генерирует PHP часть ответа;
- Уровень базы данных , на котором обрабатываются все запросы к базе данных, включая (но, не ограничиваясь только ими) запросы SELECT , операторы INSERT , и т.д.
В типичном случае, если речь идет о большом приложении, эти элементы, скорее всего, не находятся на одной и той же машине, возможно, даже, что они находятся не в одной сети.
Хотя скорость работы сети в последние несколько лет значительно возросла, это по-прежнему самый медленный и самый ненадежный (по сравнению с другими) канал передачи данных (кэша процессора, памяти, жесткого диска и т.д.)
Таким образом, чтобы увеличить скорость работы приложения и повысить надежность, в некоторых случаях прибегают к тому, чтобы больше операций по обработке данных и логики выполнялось на стороне сервера (в частности, сервера MySQL ), и меньше данных передавалось по сети.
Во-вторых , это повышает производительность. ХП хранятся и запускаются непосредственно на сервере MySQL . Они могут быть предварительно скомпилированы и проанализированы на сервере базы данных.
Это существенным образом отличается от обработки того же запроса со стороны клиента, где запрос разбирается драйверами базы данных, анализируется и оптимизируется (если это возможно) каждый раз, когда вызывается оператор запроса.
Это похоже на исполнение интерпретируемого языка (на стороне клиента) и компилируемого языка (на стороне сервера базы данных). А мы знаем, что скомпилированная программа будет работать быстрее.
В-третьих , написанные однажды ХП выполняются где угодно. SQL является стандартной и 100% независимой платформой. Она опирается только на сервер баз данных. Подумайте, как много существует различных языков/библиотек, которые используются для работы с базой данных.
Они акцентируют внимание на повышении эффективности извлечения и обработки данных на стороне сервера, вместо того, чтобы задать одинаковую логику обработки данных, по — разному синтаксически описываемую всеми этими языками/библиотеками. Коль скоро эта логика обработки данных столь широко используется.
Не в последнюю очередь, ХП является фундаментальным аспектом безопасности баз данных.
Давайте рассмотрим простую установку для базы данных. Допустим в информационной системе управления персоналом ( HRIS ) существует таблица, содержащая информацию о зарплате каждого сотрудника. Сотрудник отдела кадров должен иметь возможность получить некоторые данные из этой таблицы: общие суммы зарплаты, средняя зарплата и т.д.
Но этот сотрудник не должен иметь доступа к детальной информации по зарплате каждого сотрудника, так как эта информация является конфиденциальной и может быть доступна только для некоторых руководителей.
Мы знаем, что MySQL имеет систему полного управления привилегиями. Очевидно, что в этом случае мы не можем предоставить этому сотруднику отдела кадров даже привилегию SELECT (потому что, если мы это сделаем, это будет означать, что он / она может видеть детальную зарплату каждого сотрудника).
Но если он /она не может получить доступ к таблице зарплаты, то, как этот сотрудник сможет получить сводные данные по зарплате? Как мы можем обеспечить работнику отдела кадров доступ к этой информации без ущерба для кадровой политики компании?
Решить эту проблему можно с помощью хранимой процедуры, которая выдает запрашиваемую информацию и гранты сотруднику, который имеет привилегию EXECUTE . (Подробный список и описание привилегий MySQL вы можете найти в официальной документации .
Данная ссылка предоставляет информацию по MySQL 5.6 . Если вы используете другую версию, просто замените цифру 5.6 в адресе страницы на нужную вам).
В данном случае ХП является мостом между пользователем (нашим сотрудником отдела кадров) и таблицей ( salary ), к которой пользователь не имеет прямого доступа:
Вот и все! С помощью ХП мы можем обеспечить пользователю возможность выполнить задачу без ущерба для безопасности базы данных (и кадровой политики)!
Недостатки использования хранимых процедур
После перечисления всех преимуществ использования ХП мы должны получить четкое представление о некоторых недостатках и посмотреть, есть ли способы улучшить ситуацию:
- Отсутствие контроля версии самой ХП. Когда изменяется версия ХП, она изменяется без сохранения истории действий для предыдущих версий на стороне сервера. Это может создать некоторые неудобства, когда пользователь хотел бы откатить изменения. Я предлагаю в таких случаях писать ХП на стороне клиента и обеспечивать контроль версии здесь. Когда ХП готова, код можно легко скопировать, скажем, в MySQL Workbench и создать процедуру на стороне сервера. Таким образом, мы можем получить некоторую степень контроля версии.
- Отсутствие простого способа « синхронизировать » введение в силу изменений и заставить все субъекты использовать последнюю версию, в частности, когда каждый из членов команды имеет свою собственную локальную базу данных для разработки и тестирования. Контроль версий может стать решением, но все еще требуется ручное вмешательство через обновление локальной копии ХП на локальном сервере БД. Другой способ заключается в использовании « условного объекта ». Членов команды можно разделить так, чтобы хотя бы один человек отвечал на поддержание ХП и решения, использующие ее вызов через код. Остальные участники группы, которым нужны результаты исполнения ХП, могут разрабатывать и тестировать свою часть с помощью условного объекта, предполагая, что « условные » вызовы ХП будут выдавать нужный результат. На более позднем этапе, можно объединить различные части, удалив условный код.
- Сложности с созданием резервных копий/экспортом. ХП находится на стороне сервера. Разработчики будут иметь только основные привилегии ( SELECT , EXECUTE и т.д.) и никаких прав администратора для резервного копирования и экспорта. В некотором смысле, это является вовсе не недостатком, а скорее одним из основополагающих аспектов безопасности баз данных. И обойти это ограничение нельзя, это и не рекомендуется. Предполагается, что в команде будет назначен отдельный администратор базы данных, который будет заниматься этой работой. Регулярное резервное копирование БД может также служить для целей экспорта (и импорта).
Создание хранимой процедуры в MySQL
Так как ХП хранятся на сервере, рекомендуется создавать их непосредственно там же. То есть, не используя PHP или другие языки программирования для выполнения команд SQL .
Давайте рассмотрим, как создать ХП в сервере MySQL , создать пользователя, назначить ему привилегии и запустить (под этим логином) ХП, чтобы проверить, правильно ли обрабатываются данные. В своей рабочей среде я использую MySQL Workbench .
Существуют также и другие инструменты ( PHPMyAdmin , например), так что вы можете выбрать то, что лучше всего подходят именно вам.
Предположим, у нас есть такая таблица:
CREATE TABLE `salary` ( `empid` int(11) NOT NULL, `sal` int(11) DEFAULT NULL, PRIMARY KEY (`empid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Сперва мы создаем пользователя ‘tr’ для сотрудника отдела кадров, который должен получить из этой таблицы сводную информацию о зарплате (средняя зарплата по фирме, максимальная, минимальная и т.д.):
CREATE USER 'tr'@'localhost' IDENTIFIED BY 'mypass';
Этому пользователю мы назначаем только привилегию EXECUTE для таблицы, в которой содержатся данные по зарплате:
grant execute on hris.* to tr@`%`
Необходимость предоставления тех или иных привилегий мы можем уточнить, ознакомившись с разделом « Пользователи и привилегии » документации MySQL :
Теперь давайте создадим ХП следующим образом:
DELIMITER $$ CREATE PROCEDURE `avg_sal`(out avg_sal decimal) BEGIN select avg(sal) into avg_sal from salary; END
ПРИМЕЧАНИЕ : Все вышеперечисленные операции требует прав администратора на сервере MySQL .
После выполнения команды в MySQL Workbench , ХП avg_sal будет создана и готова к вызову. Она выводит среднюю зарплату из таблицы salary .
Чтобы проверить, может ли пользователь tr запустить ХП, не получая при этом доступ к таблице salary , мы можем сменить свою роль, войдя на сервер MySQL под логином пользователя tr. Это можно сделать, создав новое соединение в MySQL Workbench с другими логином и паролем.
После входа в систему от имени пользователя tr первое, что мы увидим, это то, что мы не можем видеть таблицы, нам доступны только ХП:
Ясно, что пользователь tr не сможет извлечь какие-либо данные из любой таблицы (то есть будет не в состоянии увидеть конкретные суммы зарплаты из таблицы salary ), но он будет в состоянии выполнить ХП, которую мы только что создали для определения средней зарплаты по компании:
call avg_sal(@out); select @out;
В результате выводится средняя зарплата.
К этому моменту мы выполнили всю подготовительную работу: создали пользователя, назначили ему привилегии, создали ХП и проверили ее исполнение. Далее мы покажем, как вызвать эту ХП из PHP.
Вызов хранимой процедуры из PHP
С помощью PDO вызов ХП выполняется довольно просто. Код РНР выглядит следующим образом:
$dbms = 'mysql'; //Replace the below connection parameters to fit your environment $host = '192.168.1.8'; $db = 'hris'; $user = 'tr'; $pass = 'mypass'; $dsn = "$dbms:host=$host;dbname=$db"; $cn=new PDO($dsn, $user, $pass); $q=$cn->exec('call avg_sal(@out)'); $res=$cn->query('select @out')->fetchAll(); print_r($res); $res будет содержать значение средней зарплаты из таблицы salary . После этого пользователь сможет производить дальнейшую обработку исходящих данных.
Заключение
В этой статье мы рассмотрели неотъемлемый компонент базы данных MySQL : хранимые процедуры.
Преимущества использования ХП очевидны, но позвольте мне еще раз их подчеркнуть: хранимые процедуры позволяют обеспечить строгий контроль доступа к некоторым данным в базе данных, чтобы отвечать требованиям бизнеса .
Мы также показали основные этапы создания хранимых процедур, создания пользователя и назначения привилегий, и как вызывать ХП через PHP.
Данная статья не охватывает все аспекты, которые касаются хранимых процедур. Некоторые важные моменты, такие как параметры ввода / вывода, операторы управления, курсоры, полный синтаксис и т.д. в этой короткой статье мы не рассматривали.
Если вас заинтересовала эта статья, пожалуйста, оставьте свой комментарий, и мы будем рады разместить более углубленные материалы об этом полезном и мощном элементе MySQL .