php mysql存储过程_MySQL和PHP中的存储过程

php mysql存储过程

Put simply, a Stored Procedure ("SP") is a procedure (written in SQL and other control statements) stored in a database which can be called by the database engine and connected programming languages.

简而言之，存储过程(“ SP”)是存储在数据库中的过程(用SQL和其他控制语句编写)，可以由数据库引擎和连接的编程语言调用。

In this tutorial, we will see how to create an SP in MySQL and execute it in MySQL server and in PHP.

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

Note: We are not going to cover the full aspect of the SP here. The official MySQL document should always be the place for reference.

注意：此处我们不会涵盖SP的全部方面。 官方的MySQL文档应始终作为参考。

SP are also available in other common database servers (Postgre for example) so what we will discuss here can be applied to those as well.

SP在其他常见的数据库服务器(例如Postgre)中也可用，因此我们在这里讨论的内容也可以应用于这些服务器。

为什么建议使用存储过程 (Why Stored Procedures are recommended)

Most of us are quite familiar with the normal setup to build a database application: creating database, creating tables, set up indexes, CRUD the data, issue queries from the client side and do further processing if necessary.

我们大多数人都非常熟悉构建数据库应用程序的常规设置：创建数据库，创建表，设置索引，对数据进行CRUD，从客户端发出查询并在必要时进行进一步处理。

That workflow works fine in most cases but there is one important aspect of database programming missing: the Stored Procedure.

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

There are at least four advantages I can think of to use an SP in a database application.

我可以想到在数据库应用程序中使用SP至少有四个优点。

Firstly, it reduces the network traffic and overhead. In a typical PHP database web application, there are four layers:

首先，它减少了网络流量和开销。 在典型PHP数据库Web应用程序中，有四层：

• The client layer, which is normally a web browser. It receives user interactions and presents the data in a UI.
  客户端层，通常是Web浏览器。 它接收用户交互并在UI中显示数据。
• The web server layer, which handles and dispatches user requests and sends back responses to the client layer.
  Web服务器层，用于处理和调度用户请求，并将响应发送回客户端层。
• The PHP layer, which handles all PHP interpretation, does the application logic and generates the PHP part of response.
  处理所有PHP解释PHP层执行应用程序逻辑并生成响应PHP部分。

• The database layer, which handles all database queries, including but not limited to a SELECT query, an INSERT statement, etc.

  数据库层，处理所有数据库查询，包括但不限于SELECT查询， INSERT语句等。

In a typical environment, these layers will most likely not reside on one single machine, maybe not even in one network, for larger applications.

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

Although network speed has tremendously increased in the past few years, it is still the slowest and most unreliable compared to other ways of transferring the data (CPU cache, memory, hard disk, etc). So, to save bandwidth and increase robustness, it is sometimes a good idea to have more processing and logic done on the server side (in particular, the MySQL server) and have less data transferred through the network.

尽管在过去几年中，网络速度得到了极大的提高，但与其他传输数据的方式(CPU缓存，内存，硬盘等)相比，它仍然是最慢和最不可靠的 。 因此，为了节省带宽并提高鲁棒性，有时最好在服务器端(尤其是MySQL服务器)上执行更多的处理和逻辑，并通过网络传输较少的数据。

Secondly, it improves the performance. SP is stored and run directly in the MySQL server. It can be pre-compiled and analyzed by the database server. This is quite different from issuing the same query from the client side, where the query will be parsed by database drivers, analyzed and optimized (if possible) every time the query statement is called. This is somehow quite like the interpreted language execution (at the client end) and the compiled language execution (at the database server end). And we know a compiled program will run faster.

其次，它提高了性能。 SP被存储并直接在MySQL服务器中运行。 可以由数据库服务器对其进行预编译和分析。 这与从客户端发出相同的查询有很大的不同，在客户端，查询将由数据库驱动程序解析，并在每次调用查询语句时进行分析和优化(如果可能)。 这有点像解释语言执行(在客户端)和编译语言执行(在数据库服务器端)。 而且我们知道编译后的程序将运行得更快。

Third, Write Once and Execute Anywhere. SQL is standard and purely 100% platform independent. It only relies on the database server. Consider how many different languages/libs there are that we can use to deal with the database. It increases efficiency to put the data retrieving and processing at the server end instead of writing the same processing logic in a different syntax provided by all these languages/libs, if the data processing logic is so commonly used.

第三，编写一次即可在任何地方执行。 SQL是标准的，并且完全是100％独立于平台的。 它仅依赖于数据库服务器。 考虑一下我们可以使用多少种不同的语言/库来处理数据库。 如果数据处理逻辑非常常用，则可以提高数据检索和处理在服务器端的效率，而不必以所有这些语言/库提供的不同语法编写相同的处理逻辑。

Last but not least, SP is a fundamental aspect of database security.

最后但并非最不重要的一点是，SP是数据库安全性的基本方面。

Let's consider a simple database setup. In a human resource information system (HRIS), it is reasonable to assume that there exists a table holding the salary information of each employee. An HR employee should have the right to grab some figures out of this table: total salary, average salary, etc but this employee should not see the detailed salary of each employee as this information will be too sensitive and should only be available to a few.

让我们考虑一个简单的数据库设置。 在人力资源信息系统(HRIS)中，可以合理地假设存在一个表，其中包含每个员工的薪水信息。 人力资源员工应有权从该表中获取一些数据：总薪水，平均薪水等，但该员工不应看到每位员工的详细薪水，因为此信息过于敏感，只能提供给少数员工。

We know MySQL has a comprehensive privilege control. In this case, it is obvious that we can't even grant SELECT privilege to this HR employee (which, if we do, means he/she can see the detailed salary of everyone). But if he/she can't access the salary table, how can this employee get the aggregation information related to salary? How can we allow the employee to grab that information without compromising the HR policy?

我们知道MySQL具有全面的权限控制。 在这种情况下，很明显我们甚至不能授予该HR员工SELECT特权(如果这样做，则意味着他/她可以看到每个人的详细工资)。 但是，如果他/她无法访问salary表，那么该员工如何获得与salary相关的汇总信息？ 我们如何允许员工在不损害人力资源政策的前提下获取这些信息？

The answer is using a Stored Procedure that returns the required information and grants that employee the EXECUTE privilege. (For a detailed list and explanation of MySQL privileges, please consult the official documentation. The link here is for MySQL 5.6. Please replace 5.6 with the version you are using.)

答案是使用存储过程，该存储过程返回所需的信息并向该员工授予EXECUTE特权。 (有关MySQL特权的​​详细列表和说明，请查阅官方文档 。此处的链接适用于MySQL 5.6。请用您使用的版本替换5.6 。)

SP is now a bridge, bridging the user (our HR employee) and the table (salary), to which the user has no direct access.

SP现在是一座桥梁，桥接了用户(我们的HR员工)和用户无法直接访问的表( salary )。

That's it! With SP, we can get the user to accomplish the task without compromising the database security (and HR policy)!

而已！ 使用SP，我们可以使用户完成任务而不会损害数据库安全性(和HR策略)！

使用存储过程的缺点 (Drawbacks of using Stored Procedures)

After naming all the advantages of using an SP, we need to be clear about some of the drawbacks and see if there are ways to improve.

在说明使用SP的所有优点之后，我们需要弄清一些缺点，并查看是否有改进的方法。

• No version control on SP itself. When an SP is modified, it is modified, no historical trails can be kept in the server side. It may create some frustrations when the user would like to rollback a change. My suggestion is to write the SP in your client side and put it under version control. When the SP is ready, it is easy to copy the code into, say MySQL Workbench and create it at the server side. By doing so, we can have some degree of version control.
  SP本身没有版本控制。 修改SP时，将修改它，则无法在服务器端保留任何历史记录。 当用户想要回滚更改时，它可能会产生一些挫败感。 我的建议是在客户端编写SP，并将其置于版本控制下。 准备好SP后，可以很容易地将代码复制到MySQL Workbench中，并在服务器端创建代码。 这样，我们可以进行一定程度的版本控制。
• No easy way to "synchronize" the changes applied and force everyone to use the latest version, in particular, when each of the team member has his/her own local database for development and testing purposes. Version control may be the solution but still requires manual intervention by updating the local copy of the SP in the local db server. Another way is to use "mocking". The team members can be divided so that at least one person will focus on the maintenance of the SP and the implementation of the calling to SP in the code. All others that need the results from the SP can develop and test their portion using mocking objects, i.e, always assuming the "faked" call to the SP will return a desired result. In a later stage, merging can be done to discard the mocking code.
  没有简单的方法可以“同步”应用的更改并迫使每个人都使用最新版本，尤其是当每个团队成员都有自己的本地数据库用于开发和测试时。 版本控制可能是解决方案，但仍然需要通过更新本地数据库服务器中SP的本地副本进行手动干预。 另一种方法是使用“模拟”。 团队成员可以分开，以便至少有一个人将专注于SP的维护和代码中对SP的调用的实现。 需要SP结果的所有其他对象都可以使用模拟对象来开发和测试其部分，即始终假定对SP的“伪造”调用将返回期望的结果。 在稍后的阶段，可以合并以丢弃模拟代码。

• Hard to backup/export. The SP is on the server side. Regular developers will only have basic privileges (SELECT, EXECUTE, etc) and no admin rights to backup and export. In a way, I won't call it a drawback but rather a fundamental aspect of db security. There is no way, and it is not recommended to get around this. It is suggested that, in a team, a dedicated DB admin will be appointed to do such jobs. A regular db backup can also serve the backup/export (and import) purpose.

  难以备份/导出。 SP在服务器端。 普通开发人员将仅具有基本特权( SELECT ， EXECUTE等)，而没有备份和导出的管理员权限。 在某种程度上，我不会称它为缺点，而是数据库安全性的基本方面。 没有办法，并且不建议您解决此问题。 建议在一个团队中任命一个专门的数据库管理员来完成这些工作。 常规数据库备份也可以用于备份/导出(和导入)目的。

在MySQL中创建存储过程 (Creating a Stored Procedure in MySQL)

As SPs are stored in the server, it is recommended to create the SP directly in the server, i.e., not by using PHP or other programming languages to issue SQL commands to do so.

由于SP存储在服务器中，因此建议直接在服务器中创建SP，即不要通过使用PHP或其他编程语言来发布SQL命令来创建SP。

Let's see how to create the SP in MySQL server, create a user and apply privileges and run (as that user) the SP to see if the result is correct. In my working environment, I am using MySQL Workbench. Other tools are available (PHPMyAdmin for example) so feel free to use the tools that best fit you.

让我们看看如何在MySQL服务器中创建SP，创建用户并应用特权并(以该用户身份)运行SP以查看结果是否正确。 在我的工作环境中，我正在使用MySQL Workbench 。 还可以使用其他工具(例如PHPMyAdmin)，以便随时使用最适合您的工具。

Assume we have a table like this:

假设我们有一个这样的表：

CREATE TABLE `salary` (
  `empid` int(11) NOT NULL,
  `sal` int(11) DEFAULT NULL,
  PRIMARY KEY (`empid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And for the HR employee that needs to get the aggregated information on salary (average, max, min, etc) from that table, we first create a user 'tr' like this:

对于需要从该表中获取薪水(平均，最高，最低等)汇总信息的HR员工，我们首先创建一个用户'tr'如下所示：

CREATE USER 'tr'@'localhost' IDENTIFIED BY 'mypass';

and for this user, we only grant EXECUTE privilege to the schema where the salary table resides:

对于该用户，我们仅授予EXECUTE特权给salary表所在的架构：

grant execute on hris.*  to tr@`%`

We can verify the necessary privilege is granted by visiting the "Users and Privileges" in MySQL Bench:

我们可以通过访问MySQL Bench中的“用户和特权”来验证是否授予了必要的特权：

Now let's create the SP like this:

现在让我们像这样创建SP：

DELIMITER $$

CREATE PROCEDURE `avg_sal`(out avg_sal decimal)
BEGIN
    select avg(sal) into avg_sal from salary;

END

NOTE: All the above operations will require an admin role in the MySQL server.

注意：以上所有操作都将需要MySQL服务器中的管理员角色。

After issuing the command in MySQL Workbench, the avg_sal SP will be created and ready to be called. It will return the average salary of table salary.

在MySQL Workbench中发出命令后，将创建avg_sal SP并准备调用它。 它将返回表salary的平均salary 。

To test if the user tr can actually run the SP but should not be able to access the salary table, we can switch the role by logging into the MySQL server using user tr. It can be done by creating another connection in MySQL Workbench using a different user/password pair.

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

After logging in as tr, the first thing we will notice is that the user will not be able to see any tables and can only see the SP:

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

It is clear that the user tr won't be able to select anything from any table (thus unable to see the detailed salary number of the salary table) but he/she is able to execute the SP we just created and get the average salary of the company:

显然，用户tr将无法从任何表中选择任何内容(因此无法查看salary表的详细工资编号)，但是他/她能够执行我们刚刚创建的SP并获得平均工资该公司的：

call avg_sal(@out);
select @out;

The average salary will be displayed.

将显示平均工资。

We have so far done all the preparation to create a user, grant privileges, create a SP and test the running of the SP. Next we will show how to call that SP from within PHP.

到目前为止，我们已经完成了创建用户，授予特权，创建SP以及测试SP的运行的所有准备工作。 接下来，我们将展示如何在PHP中调用该SP。

从PHP调用存储过程 (Calling a Stored Procedure from PHP)

With PDO, calling an SP is straightforward. The PHP code is as follows:

使用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);

The $res will contain the average salary of the table salary. The user can further process the output with PHP now.

$res将包含表salary的平均salary 。 用户现在可以使用PHP进一步处理输出。

结论 (Conclusion)

In this article, we reviewed the long-forgotten component in MySQL database: the Stored Procedure. The advantages to use an SP are obvious and let me re-emphasize: Stored Procedures allow us to apply stronger database access control to certain data to fit in with the business requirement.

在本文中，我们回顾了MySQL数据库中长期被遗忘的组件：存储过程。 使用SP的优势显而易见，让我再次强调： 存储过程使我们能够对某些数据应用更强大的数据库访问控制，以适应业务需求。

We also illustrated the basic steps to create Stored Procedures, create a user and assign a privilege, and how to call it in PHP.

我们还说明了创建存储过程，创建用户和分配特权的基本步骤，以及如何在PHP中调用它。

This article does not cover the full scope of Stored Procedures. Some important aspects like input/output parameters, control statement, cursors, the complete syntax, etc are not discussed in this short article.

本文没有涵盖存储过程的全部范围。 本文不讨论一些重要方面，例如输入/输出参数，控制语句，游标，完整语法等。

If you feel interested, please leave your comments here and we will be glad to bring more in-depth articles about this useful and powerful aspect of MySQL.

如果您有兴趣，请在这里留下您的评论，我们将很高兴为您带来有关MySQL有用和强大方面的更深入的文章。

翻译自: https://www.sitepoint.com/stored-procedures-mysql-php/

php mysql存储过程