PHP开发人员应该使用MySQL的存储过程吗? [英] Should PHP developers use MySQL's stored procedures?

问题描述

过去十年来，我一直在使用SQL Server后端编写asp.net应用程序.在那段时间里，我还写了一些PHP应用程序，但不是很多.

I've been writing asp.net apps with SQL Server back ends for the past 10 years. During that time, I have also written some PHP apps, but not many.

我将要将一些asp.net应用程序移植到PHP，并且遇到了一些问题.在Asp.net世界中，通常可以理解，在访问任何数据库时，首选使用视图或存储过程.

I'm going to be porting some of my asp.net apps to PHP and have run into a bit of an issue. In the Asp.net world, it's generally understood that when accessing any databases, using views or stored procedures is the preferred way of doing so.

我一直在阅读一些PHP/MySQL书籍，并开始给人一种不建议在MySQL中使用存储过程的印象.我不建议使用该词，但这只是我的感觉.

I've been reading some PHP/MySQL books and I'm beginning to get the impression that utilizing stored procedures in MySQL is not advisable. I hesitate in using that word, advisable, but that's just the feeling I get.

所以，我正在寻找的建议基本上是对还是错? PHP开发人员是否完全使用存储过程?或者，是被回避的东西?

So, the advice I'm looking for is basically, am I right or wrong? Do PHP developers use stored procedures at all? Or, is it something that is shunned?

推荐答案

是否使用存储过程更多地是在酒吧进行宗教或政治讨论.
需要做的是清楚地定义您的应用程序层，而不要越过这些边界.与在数据库外执行查询相比，存储过程有很多优点和缺点.

Whether to use Stored Procedures or not is more of a religious or political discussion at a bar than not.
What needs to be done is clearly define your application layers and not step over those boundaries. Stored procedures have several advantages and disadvantages over doing queries outside of the database.

优点1:存储过程是模块化的.从维护的角度来看，这是一件好事.当应用程序中出现查询问题时，您可能会同意，对存储过程进行故障诊断要比埋在许多行GUI代码中的嵌入式查询要容易得多.

Advantage 1: Stored procedures are modular. This is a good thing from a maintenance standpoint. When query trouble arises in your application, you would likely agree that it is much easier to troubleshoot a stored procedure than an embedded query buried within many lines of GUI code.

优点2:存储过程是可调的.通过使处理数据库的程序在您的界面上工作，您无需修改​​GUI源代码即可提高查询性能.可以对存储过程进行更改，例如连接方法，不同的表等，这些更改对前端接口是透明的.

Advantage 2: Stored procedures are tunable. By having procedures that handle the database work for your interface, you eliminate the need to modify the GUI source code to improve a query's performance. Changes can be made to the stored procedures--in terms of join methods, differing tables, etc.--that are transparent to the front-end interface.

优势3:存储过程从客户端抽象或分离了服务器端功能.编写GUI应用程序以调用过程比通过GUI代码构建查询要容易得多.

Advantage 3: Stored procedures abstract or separate server-side functions from the client-side. It is much easier to code a GUI application to call a procedure than to build a query through the GUI code.

优点4:存储过程通常由数据库开发人员/管理员编写.担任这些角色的人员通常在编写有效的查询和SQL语句方面更有经验.这使GUI应用程序开发人员可以将他们的技能用于应用程序的功能和图形表示部分.如果您让员工执行最适合他们的任务，那么最终您将产生一个更好的整体应用程序.

Advantage 4: Stored procedures are usually written by database developers/administrators. Persons holding these roles are usually more experienced in writing efficient queries and SQL statements. This frees the GUI application developers to utilize their skills on the functional and graphical presentation pieces of the application. If you have your people performing the tasks to which they are best suited, then you will ultimately produce a better overall application.

记住所有这些缺点.

缺点1: 如果逻辑完全在存储过程中实现，则涉及大量业务逻辑和处理的应用程序可能会对服务器造成过多的负载.此类处理的示例包括数据传输，数据遍历，数据转换和密集的计算操作.您应该将这种类型的处理移至业务流程或数据访问逻辑组件，这是比数据库服务器更具可伸缩性的资源.

Disadvantage 1: Applications that involve extensive business logic and processing could place an excessive load on the server if the logic was implemented entirely in stored procedures. Examples of this type of processing include data transfers, data traversals, data transformations and intensive computational operations. You should move this type of processing to business process or data access logic components, which are a more scalable resource than your database server.

缺点2: 不要将所有业务逻辑都放入存储过程中.当您必须用Sp语言修改业务逻辑时，应用程序的维护和敏捷性就成为一个问题.例如，支持多个RDBMS的ISV应用程序不需要为每个系统维护单独的存储过程.

Disadvantage 2: Do not put all of your business logic into stored procedures. Maintenance and the agility of your application becomes an issue when you must modify business logic in Sp language. For example, ISV applications that support multiple RDBMS should not need to maintain separate stored procedures for each system.

缺点3: 编写和维护存储过程通常是并非所有开发人员都具备的专业技能.这种情况可能会在项目开发进度表中造成瓶颈.

Disadvantage 3: Writing and maintaining stored procedures is most often a specialized skill set that not all developers possess. This situation may introduce bottlenecks in the project development schedule.

我可能已经错过了一些优点和缺点，请随时发表评论.

I have probably missed some advantages and disadvantages, feel free to comment.

这篇关于PHP开发人员应该使用MySQL的存储过程吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！