MySQL存储过程使用它们或不使用它们 [英] MySQL stored procedures use them or not to use them

问题描述

我们正处于一个新项目的开始，我们真的想知道我们是否应该在MySQL中使用存储过程。

We are at the begining of a new project, and we are really wondering if we should use stored procedures in MySQL or not.

我们将使用存储过程仅用于插入和更新业务模型实体。有几个表代表一个模型实体，我们将在那些存储过程中插入/更新它们。

We would use the stored procedures only to insert and update business model entities. There are several tables which represent a model entity, and we would abstract it in those stored procedures insert/update.

另一方面，我们可以调用insert和update

On the other hand, we can call insert and update from the Model layer but not in MySQL but in PHP.

根据您的经验，哪个是最好的选择？两种方法的优点和缺点。

In your experience, Which is the best option? advantages and disadvantages of both approaches. Which is the fastest one in terms of high performance?

PS：这是一个大部分读取的网络项目，高性能是最重要的必要条件。

PS: It is is a web project with mostly read and high performance is the most important requisite.

推荐答案

与实际的编程语言代码不同，它们是：

Unlike actual programming language code, they:

• 不可移植（每个数据库都有自己的PL / SQL版本，有时不同版本的数据库不兼容 - 我已经看过了）


• 不容易测试 - 您需要一个真实的（dev）数据库实例来测试它们，因此在构建过程中对代码进行单元测试几乎是不可能的


• 不容易更新/释放 - 您必须删除/创建它们，即修改生产数据库以释放它们


• 没有库支持当别人有）


• 不容易与其他技术集成（尝试从他们调用web服务）


• 他们使用一种语言作为原语作为Fortran，因此获得有用的编码是不方便和费力的，所以很难表达业务逻辑，即使通常这是他们的主要目的是


• 不提供调试/跟踪/消息日志等（有些dbs可能支持这个 - 我还没有看到它）


• 缺乏一个合适的IDE来帮助语法和链接到其他现有的程序对于java）


• 熟练地编写代码的人比应用程序代码更为稀有和昂贵。


• 他们的高性能是一个神话，在数据库服务器上，他们通常增加db服务器负载，因此使用它们通常会减少您的最大事务吞吐量


• 无法有效共享常数（通常通过创建一个表，并从你的过程中寻找它来解决 - 非常低效）


• 等。

• not portable (every db has its own version of PL/SQL. Sometimes different versions of the same database are incompatible - I've seen it!)
• not easily testable - you need a real (dev) database instance to test them and thus unit testing their code as part of a build is virtually impossible
• not easily updatable/releasable - you must drop/create them, ie modify the production db to release them
• do not have library support (why write code when someone else has)
• are not easily integratable with other technologies (try calling a web service from them)
• they use a language about as primitive as Fortran and thus are inelegant and laborious to get useful coding done, so it is difficult to express business logic, even though typically that is what their primary purpose is
• do not offer debugging/tracing/message-logging etc (some dbs may support this - I haven't seen it though)
• lack a decent IDE to help with syntax and linking to other existing procedures (eg like Eclipse does for java)
• people skilled in coding them are rarer and more expensive than app coders
• their "high performance" is a myth, because they execute on the database server they usually increase the db server load, so using them will usually reduce your maximum transaction throughput
• inability to efficiently share constants (normally solved by creating a table and questing it from within your procedure - very inefficient)
• etc.

如果你有一个非常特定的数据库操作（例如用于维护数据库完整性的事务内操作），或者保持你的过程非常原子和简单，或许你可以考虑它们。

If you have a very database-specific action (eg an in-transaction action to maintain db integrity), or keep your procedures very atomic and simple, perhaps you might consider them.

前面指定高性能时，建议小心。

Caution is advised when specifying "high performance" up front. It often leads to poor choices at the expense of good design and it will bite you much sooner than you think.

使用存储过程在你自己的危险（从某人谁是有和没有想回去）。我的建议是避免他们像瘟疫。

Use stored procedures at your own peril (from someone who's been there and never wants to go back). My recommendation is to avoid them like the plague.

这篇关于MySQL存储过程使用它们或不使用它们的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！