在MySQL和PHP存储过程_php职业规划

在MySQL和PHP存储过程

简单地说，一个存储过程（“SP”）是存储在一个可以由数据库引擎被调用，连接编程语言数据库的过程（SQL写的和其他控制语句）。

在本教程中，我们将看到如何在MySQL中创建的SP和MySQL服务器和PHP中执行它。

注意：我们不打算在这里介绍了SP的全方面。官方的MySQL文档应该总是以供参考的地方。

SP也可在其他常见的数据库服务器（的Postgre为例），所以我们将在这里讨论可以适用于那些为好。

为什么存储过程建议

我们大多数人都相当熟悉的正常设置，以建立一个数据库应用程序：创建数据库，创建表，建立索引，CRUD从客户端的数据，发出查询并做进一步处理，如果必要的。

工作流工作正常，在大多数情况下，但有数据库编程缺少一个重要的方面：存储过程。

至少有四个好处我能想到的使用的SP中的数据库应用程序。

首先，它降低了网络流量和开销。在一个典型的PHP数据库的Web应用程序，有四层：

客户端层，这通常是一个网络浏览器。它接收用户交互，并提出在UI中的数据。
Web服务器层，处理和分派用户的请求并发送回响应给客户端层。
PHP的层，它处理所有的PHP解释，做应用程序逻辑，并生成响应的PHP的一部分。
数据库层，它处理所有的数据库查询，包括但不限于一个SELECT查询，一个INSERT语句，等等。

在典型环境中，这些层很可能不会驻留在一台机器，甚至不是在一个网络中，对于较大的应用程序。

虽然网络速度在过去几年大幅增加，它仍然是最慢的，最不可靠的相比，传输数据的其他方式（CPU缓存，内存，硬盘等）。因此，以节省带宽和提高的鲁棒性，有时有更多的处理和逻辑在服务器端进行（特别是MySQL服务器），并有较少的通过网络传输的数据是个好主意。

其次，提高了性能。 SP的存储和直接在MySQL服务器上运行。它可以被预编译和数据库服务器进行分析。这是自发出从客户端，其中查询将数据库驱动程序进行解析，分析和优化（如果可能的话）的每个查询语句被调用时相同的查询完全不同。这是某种挺像的解释型语言执行（在客户端）和编译型语言执行（在数据库服务器端）。我们知道编译的程序将运行得更快。

三，一次写入和执行任何地方。 SQL是标准的，纯粹100％的与平台无关的。它只依赖于数据库服务器上。考虑有多少种不同的语言/库有，我们可以用它来处理数据库。它可以提高效率投入，而不是写相同的处理逻辑在所有这些语言/库提供了不同的语法中的数据检索和处理在服务器端，如果数据处理逻辑是如此的普遍使用。

最后但并非最不重要的，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服务器，创建用户和应用的权限和运行（作为用户）买卖，看看结果是正确的。在我的工作环境，我使用MySQL的工作台。其他工具可用（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工作台：

现在，让我们创建一个这样的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将被创建，并随时可以调用。它会返回表的平均月薪salary 。

要测试用户是否tr可以实际运行的SP的，但不应该能够访问salary表中，我们可以通过登录到MySQL服务器使用的用户切换角色tr 。它可以通过使用不同的用户名/密码对建立在MySQL Workbench中的另一个连接来完成。

登录后为tr ，我们会注意到的第一件事情是，用户将无法看到任何表，只能看到SP：

很显然，用户tr将无法选择从任何表任何东西（因而无法看到的细节薪水多少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方面。

相关阅读：