Проще говоря, хранимая процедура («SP») — это процедура (написанная на SQL и других управляющих операторах), хранящаяся в базе данных, которая может вызываться ядром базы данных и связанными языками программирования.
В этом уроке мы увидим, как создать SP в MySQL и выполнить его на сервере MySQL и в PHP.
Примечание: мы не собираемся охватить весь аспект СП здесь. Официальный документ MySQL всегда должен быть местом для ссылки.
SP также доступны на других распространенных серверах баз данных (например, Postgre), так что то, что мы обсудим здесь, может быть применено и к ним.
Почему хранимые процедуры рекомендуются
Большинство из нас хорошо знакомы с обычной настройкой для создания приложения базы данных: создание базы данных, создание таблиц, настройка индексов, CRUD-данные, выдача запросов со стороны клиента и дальнейшая обработка при необходимости.
Этот рабочий процесс в большинстве случаев работает нормально, но отсутствует один важный аспект программирования базы данных: хранимая процедура .
Есть как минимум четыре преимущества использования SP в приложении базы данных.
Во-первых, это уменьшает сетевой трафик и накладные расходы. В типичном веб-приложении базы данных PHP есть четыре уровня:
- Уровень клиента, который обычно является веб-браузером. Он получает взаимодействия с пользователем и представляет данные в пользовательском интерфейсе.
- Уровень веб-сервера, который обрабатывает и отправляет запросы пользователей и отправляет ответы на уровень клиента.
- Уровень PHP, который обрабатывает все интерпретации PHP, выполняет логику приложения и генерирует часть ответа PHP.
- Уровень базы данных, который обрабатывает все запросы к базе данных, включая, но не ограничиваясь, запрос
SELECT, операторINSERTи т. Д.
В типичной среде эти уровни, скорее всего, не будут размещаться на одном компьютере, может даже не в одной сети, для более крупных приложений.
Хотя скорость сети значительно возросла за последние несколько лет, она все еще является самой медленной и ненадежной по сравнению с другими способами передачи данных (кэш-память процессора, память, жесткий диск и т. Д.). Таким образом, чтобы сэкономить пропускную способность и повысить надежность, иногда полезно иметь больше обработки и логики на стороне сервера (в частности, на сервере MySQL) и меньше данных, передаваемых по сети.
Во-вторых, это улучшает производительность. SP хранится и запускается непосредственно на сервере MySQL. Он может быть предварительно скомпилирован и проанализирован сервером базы данных. Это сильно отличается от выдачи одного и того же запроса со стороны клиента, где запрос будет анализироваться драйверами базы данных, анализироваться и оптимизироваться (если возможно) при каждом вызове оператора запроса. Это как-то похоже на выполнение интерпретируемого языка (на стороне клиента) и выполнение скомпилированного языка (на стороне сервера базы данных). И мы знаем, что скомпилированная программа будет работать быстрее.
В-третьих, напишите один раз и выполните где угодно. SQL является стандартным и полностью независимым от платформы. Это зависит только от сервера базы данных. Подумайте, сколько существует разных языков / библиотек, которые мы можем использовать для работы с базой данных. Это повышает эффективность размещения данных, обрабатывающих и обрабатывающих на стороне сервера, вместо того, чтобы записывать одну и ту же логику обработки в другом синтаксисе, предоставляемом всеми этими языками / библиотеками, если логика обработки данных так широко используется.
И последнее, но не менее важное, SP является фундаментальным аспектом безопасности базы данных.
Давайте рассмотрим простую настройку базы данных. В информационной системе управления персоналом (HRIS) разумно предположить, что существует таблица, содержащая информацию о зарплате каждого сотрудника. Сотрудник отдела кадров должен иметь право извлекать из этой таблицы некоторые цифры: общую зарплату, среднюю зарплату и т. Д., Но этот сотрудник не должен видеть подробную зарплату каждого сотрудника, поскольку эта информация будет слишком конфиденциальной и должна быть доступна только немногим ,
Мы знаем, что MySQL имеет полный контроль над привилегиями. В этом случае очевидно, что мы даже не можем предоставить привилегию SELECT этому сотруднику отдела кадров (что означает, что он / она может видеть подробную зарплату каждого). Но если он / она не может получить доступ к таблице salary , как этот сотрудник может получить агрегированную информацию, относящуюся к salary ? Как мы можем позволить сотруднику получать эту информацию без ущерба для кадровой политики?
В ответе используется хранимая процедура, которая возвращает необходимую информацию и предоставляет этому сотруднику привилегию EXECUTE . (Для подробного списка и объяснения привилегий MySQL, пожалуйста, обратитесь к официальной документации . Ссылка здесь для MySQL 5.6. Пожалуйста, замените 5.6 на версию, которую вы используете.)
SP теперь является мостом, связывающим пользователя (нашего сотрудника отдела кадров) и таблицу ( salary ), к которой у пользователя нет прямого доступа.
Это оно! С помощью SP мы можем заставить пользователя выполнить задачу, не ставя под угрозу безопасность базы данных (и кадровую политику)!
Недостатки использования хранимых процедур
После того, как мы назвали все преимущества использования SP, нам нужно прояснить некоторые недостатки и посмотреть, есть ли способы улучшить их.
- Нет контроля версий на самом SP. Когда SP изменяется, он изменяется, никакие исторические следы не могут быть сохранены на стороне сервера. Это может создать некоторые разочарования, когда пользователь хочет отменить изменение. Я предлагаю написать SP на стороне клиента и поставить его под контроль версий. Когда SP готов, легко скопировать код, скажем, в MySQL Workbench, и создать его на стороне сервера. Таким образом, мы можем иметь некоторую степень контроля версий.
- Нелегкий способ «синхронизировать» примененные изменения и заставить всех использовать последнюю версию, в частности, когда каждый из членов команды имеет свою локальную базу данных для целей разработки и тестирования. Управление версиями может быть решением, но все же требует ручного вмешательства путем обновления локальной копии SP на локальном сервере БД. Другим способом является использование «издевательства». Члены команды могут быть разделены так, чтобы по крайней мере один человек сосредоточился на обслуживании SP и реализации вызова к SP в коде. Все остальные, которым нужны результаты от SP, могут разрабатывать и тестировать свою часть, используя объекты-насмешки, т. Е. Всегда предполагая, что «поддельный» вызов SP вернет желаемый результат. На более позднем этапе, слияние может быть сделано, чтобы отбросить код насмешки.
- Трудно сделать резервную копию / экспорт. SP находится на стороне сервера. Обычные разработчики будут иметь только базовые привилегии (
SELECT,EXECUTEи т. Д.) И не иметь прав администратора для резервного копирования и экспорта. В некотором смысле, я не буду называть это недостатком, а скорее фундаментальным аспектом безопасности БД. Нет пути, и не рекомендуется обойти это. Предполагается, что в команде будет назначен специальный администратор БД для выполнения таких работ. Регулярное резервное копирование БД также может служить для резервного копирования / экспорта (и импорта).
Создание хранимой процедуры в MySQL
Поскольку SP хранятся на сервере, рекомендуется создавать SP непосредственно на сервере, т. Е. Не использовать PHP или другие языки программирования для выдачи команд SQL для этого.
Давайте посмотрим, как создать SP на сервере MySQL, создать пользователя, применить привилегии и запустить (как этот пользователь) SP, чтобы проверить правильность результата. В моей рабочей среде я использую 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 только той схеме, где находится таблица salary :
grant execute on hris.* to tr@`%`
Мы можем убедиться, что необходимые привилегии предоставляются, посетив раздел «Пользователи и привилегии» в MySQL Bench:
Теперь давайте создадим SP следующим образом:
DELIMITER $$ CREATE PROCEDURE `avg_sal`(out avg_sal decimal) BEGIN select avg(sal) into avg_sal from salary; END
ПРИМЕЧАНИЕ. Все вышеперечисленные операции потребуют роли администратора на сервере MySQL.
После выполнения команды в MySQL Workbench будет создан avg_sal SP, готовый к avg_sal . Он вернет среднюю зарплату из таблицы salary .
Чтобы проверить, действительно ли пользователь tr может запустить SP, но не должен иметь доступ к таблице salary , мы можем поменять роль, войдя на сервер MySQL, используя пользователя tr . Это можно сделать, создав другое соединение в MySQL Workbench, используя другую пару пользователь / пароль.
После входа в систему как tr первое, что мы заметим, это то, что пользователь не сможет видеть какие-либо таблицы и может видеть только SP:
Ясно, что пользователь tr не сможет ничего выбрать из любой таблицы (таким образом, не сможет увидеть подробный номер salary таблице salary ), но он / она сможет выполнить только что созданного SP и получить среднюю зарплату компании:
call avg_sal(@out); select @out;
Средняя зарплата будет отображаться.
До сих пор мы проделали всю подготовку к созданию пользователя, предоставлению привилегий, созданию SP и тестированию работы SP. Далее мы покажем, как вызвать этот SP из PHP.
Вызов хранимой процедуры из PHP
С PDO вызывать SP просто. Код PHP выглядит следующим образом:
$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 . Теперь пользователь может дополнительно обработать вывод с помощью PHP.
Вывод
В этой статье мы рассмотрели давно забытый компонент в базе данных MySQL: хранимая процедура. Преимущества использования SP очевидны, и позвольте мне еще раз подчеркнуть: хранимые процедуры позволяют нам применять более строгий контроль доступа к базе данных к определенным данным, чтобы соответствовать требованиям бизнеса.
Мы также проиллюстрировали основные этапы создания хранимых процедур, создания пользователя и назначения привилегии, а также способы ее вызова в PHP.
Эта статья не охватывает весь объем хранимых процедур. Некоторые важные аспекты, такие как параметры ввода / вывода, оператор управления, курсоры, полный синтаксис и т. Д., Не обсуждаются в этой короткой статье.
Если вам интересно, пожалуйста, оставьте свои комментарии здесь, и мы будем рады представить более подробные статьи об этом полезном и мощном аспекте MySQL.