MySQL 存储过程使用或不使用它们

MySQL stored procedures use them or not to use them

提问by Emilio Nicolás

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

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

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.

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

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

另一方面，我们可以从模型层调用插入和更新，但不能在 MySQL 中，而是在 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: It is is a web project with mostly read and high performance is the most important requisite.

PS：这是一个以读取为主的web项目，高性能是最重要的要求。

回答by Bohemian

Unlike actual programming language code, they:

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

• not portable (every db has its own version of PL/SQL. Sometimes different versions of the samedatabase 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 modifythe 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 increasethe db server load, so using them will usually reduceyour maximum transaction throughput
• inability to efficiently share constants (normally solved by creating a table and questing it from within your procedure - very inefficient)
• etc.

• 不可移植（每个数据库都有自己的 PL/SQL 版本。有时同一数据库的不同版本是不兼容的 - 我见过！）
• 不容易测试 - 您需要一个真正的（开发）数据库实例来测试它们，因此作为构建的一部分对它们的代码进行单元测试几乎是不可能的
• 不容易更新/发布 - 您必须删除/创建它们，即修改生产数据库以发布它们
• 没有库支持（为什么在别人有的时候写代码）
• 不容易与其他技术集成（尝试从它们调用 Web 服务）
• 他们使用与 Fortran 一样原始的语言，因此很难完成有用的编码，因此很难表达业务逻辑，尽管这通常是他们的主要目的
• 不提供调试/跟踪/消息记录等（一些数据库可能支持这一点——不过我还没有看到）
• 缺乏像样的 IDE 来帮助处理语法和链接到其他现有过程（例如 Eclipse 为 Java 所做的那样）
• 能够熟练编码的人比应用程序编码员更稀少，也更昂贵
• 它们的“高性能”是一个神话，因为它们在数据库服务器上执行它们通常会增加数据库服务器的负载，因此使用它们通常会降低您的最大事务吞吐量
• 无法有效地共享常量（通常通过创建一个表并从您的程序中查询它来解决 - 非常低效）
• 等等。

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.

使用存储过程需自担风险（来自曾经去过那里并且永远不想回去的人）。我的建议是避免它们像瘟疫一样。

回答by davek

Unlike programming code, they:

与编程代码不同，它们：

• render SQL injection attacks almost impossible (unless you are are
  constructing and executing dynamic
  SQL from within your procedures)
• require far less data to be sent over the IPCas part of the callout
• enable the database to far better cache plans and result sets (this is admittedly not so effective with MySQL due to its internal caching structures)
• are easily testable in isolation (i.e. not as part of JUnit tests)
• are portable in the sense that they allow you to use db-specific features, abstracted away behind a procedure name (in code you are stuck with generic SQL-type stuff)
• are almost never slower than SQL called from code

• 使 SQL 注入攻击几乎不可能发生（除非您正在 从您的过程中
  构建和执行动态
  SQL）
• 作为标注的一部分，需要通过IPC发送的数据要少得多
• 使数据库能够更好地缓存计划和结果集（由于其内部缓存结构，这对 MySQL 来说肯定不是那么有效）
• 易于单独测试（即不作为 JUnit 测试的一部分）
• 是可移植的，因为它们允许您使用特定于数据库的功能，抽象在过程名称后面（在代码中，您被通用 SQL 类型的东西困住了）
• 几乎永远不会比从代码调用的 SQL 慢

but, as Bohemian says, there are plenty of cons as well (this is just by way of offering another perspectve). You'll have to perhaps benchmark before you decide what's best for you.

但是，正如 Bohemian 所说，也有很多缺点（这只是提供另一种视角的方式）。在决定什么最适合您之前，您可能必须先进行基准测试。

回答by Sebas

As for performances, they have the potential to be really performantin a future MySQL version (under SQL Server or Oracle, they are a real treat!). Yet, for all the rest... They totally blow up competition. I'll summarize:

至于性能，它们有可能在未来的 MySQL 版本中发挥真正的性能（在 SQL Server 或 Oracle 下，它们是一种真正的享受！）。然而，对于所有其他人......他们完全破坏了竞争。我总结一下：

• Security: You can give your app the EXECUTE right only, everything is fine. Your SP will insert update select ..., with no possible leak of any sort. It means global control over your model, and an enforced data security.

• Security 2: I know it's rare, but sometimes php code leaks out from the server (i.e. becomes visible to public). If it includes your queries, possible attackers know your model. This is pretty odd but I wanted to signal it anyway

• Task force: yes, creating efficient SQL SPs requires some specific resources, sometimes more expensive. But if you think you don't need these resources just because you're integrating your queries in your client... you're going to have serious problems. I'd mention the analogy of web development: it's good to separate the view from the rest because your designer can work on their own technology while the programmers can focus on programming the business layer.

• Encapsulating business layer: using stored procedures totally isolates the business where it belongs: the damn database.

• Quickly testable: one command line under your shell and your code is tested.

• Independence from the client technology: if tomorrow you'd like to switch from php to something else, no problem. Ok, just storing these SQL in a separate file would do the trick too, that's right. Also, good point in the comments about if you decide to switch sql engines, you'd have a lot of work to do. You have to have a good reason to do that anyway, because for big projects and big companies, that rarely happens (due to the cost and HR management mostly)

• Enforcing agile 3+-tier developments: if your database is not on the same server than your client code, you may have different servers but only one for the database. In that case, you don't have to upgrade any of your php servers when you need to change the SQL related code.

• 安全性：您只能为您的应用程序赋予 EXECUTE 权限，一切都很好。您的 SP 将插入更新选择 ...，没有任何可能的泄漏。这意味着对您的模型的全局控制，以及强制的数据安全性。

• 安全性 2：我知道这很少见，但有时 php 代码会从服务器泄漏（即对公众可见）。如果它包含您的查询，则可能的攻击者知道您的模型。这很奇怪，但我还是想发出信号

• 工作组：是的，创建高效的 SQL SP 需要一些特定的资源，有时更昂贵。但是，如果您认为您不需要这些资源只是因为您将查询集成到您的客户端中……那么您将遇到严重的问题。我会提到 Web 开发的类比：将视图与其他视图分开是很好的，因为您的设计师可以使用他们自己的技术，而程序员可以专注于对业务层进行编程。

• 封装业务层：使用存储过程完全隔离它所属的业务：该死的数据库。

• 可快速测试：您的 shell 下的一个命令行和您的代码已经过测试。

• 独立于客户端技术：如果明天你想从 php 切换到其他东西，没问题。好的，将这些 SQL 存储在单独的文件中也可以解决问题，没错。此外，关于如果您决定切换 sql 引擎的评论中的要点，您将有很多工作要做。无论如何，您必须有充分的理由这样做，因为对于大项目和大公司，这种情况很少发生（主要是由于成本和人力资源管理）

• 实施敏捷的 3+ 层开发：如果您的数据库与客户端代码不在同一台服务器上，您可能有不同的服务器，但数据库只有一台。在这种情况下，当您需要更改 SQL 相关代码时，您不必升级任何 php 服务器。

Ok, I think that's the most important thing I had to say on the subject. I developed in both spirits (SP vs client) and I really, really love the SP style one. I just wished Mysql had a real IDE for them because right now it's kind of a pain in the asslimited.

好的，我认为这是我必须就这个主题说的最重要的事情。我以两种精神（SP vs 客户端）开发，我真的非常喜欢 SP 风格。我只是希望Mysql的为他们真正的IDE，因为现在它是一种在屁股痛的限制。

回答by Abhay

Stored procedures are good to use because they keep your queries organized and allow you to perform a batch at once. Stored procedures are normally quick in execution because they are pre-compiled, unlike queries that are compiled on every run. This has significant impact in situations where database is on a remote server; if queries are in a PHP script, there are multiple communication between the application and the database server - the query is send, executed, and result thrown back. However, if using stored procedures, it only need to send a small CALL statement instead of big, complicated queries.

存储过程很好用，因为它们使您的查询井井有条，并允许您一次执行批处理。存储过程通常执行起来很快，因为它们是预编译的，这与每次运行时编译的查询不同。这对数据库位于远程服务器上的情况有重大影响；如果查询在 PHP 脚本中，则应用程序和数据库服务器之间存在多次通信 - 发送、执行查询并返回结果。但是，如果使用存储过程，它只需要发送一个小的 CALL 语句，而不是大的、复杂的查询。

It might take a while to adapt to programming a stored procedure because they have their own language and syntaxes. But once you are used to it, you'll see that your code is really clean.

适应存储过程编程可能需要一段时间，因为它们有自己的语言和语法。但是一旦你习惯了，你就会发现你的代码真的很干净。

In terms of performance, it might not be any significant gain if you use stored procedures or not.

在性能方面，无论您是否使用存储过程，都可能不会有任何显着的收益。

回答by Enyix Mexico

I will let know my opinion, despite my toughts possibly are not directly related to the question.:

我会告诉我我的意见，尽管我的想法可能与问题没有直接关系。：

As in many issues, reply about using Stored Procedures or an application-layer driven solution relies on questions that will drive the overall effort:

与许多问题一样，关于使用存储过程或应用层驱动的解决方案的回复依赖于将推动整体工作的问题：

• What you want to get.

• 你想得到什么。

Are you trying to do either batch operations or on-line operations? are they completely transactional? how recurrent are those operations? how heavy is the awaited workload for the database?

您是尝试进行批处理操作还是在线操作？它们完全是交易性的吗？这些操作的重复性如何？数据库等待的工作量有多大？

• What you have in order to get it.

• 你有什么才能得到它。

What kind of database technology you have? What kind of infrastucture? Is your team fully trained in the database technology? Is your team better capable of building a database-aegnostic solution?

你有什么样的数据库技术？什么样的基础设施？您的团队是否接受过数据库技术方面的全面培训？您的团队是否更有能力构建与数据库无关的解决方案？

• Time for get it.

• 是时候得到它了。

No secrets about that.

没有任何秘密。

• Architecture.

• 建筑学。

Is your solution required to be distributed onto several locations? is your solution required to use remote communications? is your solution working on several database servers, or possibly using a cluster-based architecture?

您的解决方案是否需要分发到多个位置？您的解决方案是否需要使用远程通信？您的解决方案是在多个数据库服务器上运行，还是可能使用基于集群的架构？

• Mainteinance.

• 维护。

How much is the application required to change? do you have personal specifically trained for maintain the solution?

应用程序需要更改多少？您是否有经过专门培训来维护解决方案的人员？

• Change Management.

• 更换管理层。

Do you see your database technology will change at a short, middle, long time? do you see will be required to migrate the solution frequently?

您认为您的数据库技术会在短期、中期、长期发生变化吗？您是否看到需要经常迁移解决方案？

• Cost

• 成本

How much will cost to implement that solution using one or another strategy?

使用一种或另一种策略实施该解决方案需要多少成本？

The overall of those points will drive the answer. So you have to care each of this points when making a decision about using or not any strategy. There are cases where using of stored procedures are better than application-layer managed queries, and others when, conducting queries and using an application-layer based solution is best.

这些点的总体将推动答案。因此，在决定使用或不使用任何策略时，您必须注意每一点。在某些情况下，使用存储过程比应用程序层托管查询更好，而在其他情况下，执行查询并使用基于应用程序层的解决方案是最好的。

Using of stored procedures tends to be more addequate when:

在以下情况下，使用存储过程往往更加充分：

1. Your database technology isn't provided to change at a short time.
2. Your database technology can handle parallelized operations, table partitions or anything else strategy for divide the workload onto several processors, memory and resources (clustering, grid).
3. Your database technology is fully integrated with the stored proceduce definition language, that is, support is inside the database engine.
4. You have a development team who aren't afraid about using a procedural language (3rd. Generation language) for getting a result.
5. Operations you wanna achieve are built-in or supported inside the database (Exporting to XML data, managing data integrity and coherence appropiately with triggers, scheduled operations, etc).
6. Portability isn't an important issue and you do not whatch a technology change at a short time into your organization, even, it is not desirable. Generally, portability is seen like a milestone by the application-driven and layered-oriented developers. From my point of view, portability isn't an issue when your application isn't required to be deployed for several platforms, less when there are no reasons for making a technology change, or the effort for migrating all the organizational data is higher than the benefit for making a change. What you can win by using an application-layer driven approach (portability) you can loose in performance and value obtained from your database (Why to spend thousands of dollars for to get a Ferrari that you'll drive no more than 60 milles/hr?).
7. Performance is an issue. First: In several cases, you can achieve better results by using a single stored procedure call than multiple requests for data from another application. Moreover, some characteristics you need to perform may be built-in your database and its use less expensive in terms of workload. When you use an application-layer driven solution you have to take in account the cost associated to make database connections, making calls to the database, network traffic, data wrapping (i.e., using either Java or .NET, there is an implicit cost when using JDBC/ADO.NET calls as you have to wrap your data into objects that represents the database data, so instantiation has an associated cost in terms of processing, memory, and network when data comes from and goes to outside).

1. 您的数据库技术不会在短时间内发生变化。
2. 您的数据库技术可以处理并行操作、表分区或任何其他将工作负载划分到多个处理器、内存和资源（集群、网格）的策略。
3. 您的数据库技术与存储过程定义语言完全集成，也就是说，支持在数据库引擎内部。
4. 你有一个开发团队，他们不害怕使用过程语言（第三代语言）来获得结果。
5. 您想要实现的操作是内置的或在数据库中受支持（导出到 XML 数据、通过触发器、计划操作等适当地管理数据完整性和一致性）。
6. 可移植性不是一个重要的问题，您不会在短时间内将技术更改引入您的组织，甚至，这也是不可取的。通常，可移植性被应用程序驱动和面向分层的开发人员视为里程碑。在我看来，当您的应用程序不需要为多个平台部署时，可移植性不是问题，当没有理由进行技术更改时，或者迁移所有组织数据的工作量高于做出改变的好处。使用应用程序层驱动的方法（可移植性）您可以赢得什么 您可能会失去从数据库中获得的性能和价值（为什么要花费数千美元购买一辆时速不超过 60 英里/小时的法拉利？ ？）。
7. 性能是个问题。第一：在一些情况下，与来自另一个应用程序的多个数据请求相比，使用单个存储过程调用可以获得更好的结果。此外，您需要执行的某些特性可能是内置在您的数据库中的，并且在工作负载方面使用成本较低。当您使用应用层驱动的解决方案时，您必须考虑与建立数据库连接、调用数据库、网络流量、数据包装相关的成本（即，使用 Java 或 .NET，当使用 JDBC/ADO.NET 调用，因为您必须将数据包装到表示数据库数据的对象中，因此当数据来自外部和外部时，实例化在处理、内存和网络方面具有相关的成本）。

Using of application-layer driven solutions tends to be more addequate when:

在以下情况下，使用应用层驱动的解决方案往往更充分：

1. Portability is an important issue.
2. Application will be deployed onto several locations with only one or few database repositories.
3. Your application will use heavy business-oriented rules, that need to be agnostic of the underlying database technology.
4. You have in mind to do change technology providers based on market tendencies and budget.
5. Your database isn't fully integrated with the stored procedure language that calls to the database.
6. Your database capabilities are limited and your requirement goes beyond what you can achieve with your database technology.
7. Your application can support the penalty inherent to external calls, is more transactional-based with business-specific rules and has to abstract the database model onto a business model for the users.
8. Parallelizing database operations isn't important, moreover, your database has not parallelization capabilities.
9. You have a development team which is not well-trained onto the database technology and is better productive by using an application-driven based technology.

1. 便携性是一个重要的问题。
2. 应用程序将部署到多个位置，只有一个或几个数据库存储库。
3. 您的应用程序将使用大量面向业务的规则，这些规则需要与底层数据库技术无关。
4. 您必须根据市场趋势和预算来改变技术提供商。
5. 您的数据库未与调用数据库的存储过程语言完全集成。
6. 您的数据库功能有限，您的需求超出了您使用数据库技术所能达到的范围。
7. 您的应用程序可以支持外部调用固有的惩罚，更基于业务特定规则的事务，并且必须将数据库模型抽象为用户的业务模型。
8. 并行化数据库操作并不重要，而且您的数据库没有并行化能力。
9. 您的开发团队未受过良好的数据库技术培训，但通过使用基于应用程序驱动的技术提高了工作效率。

Hope this may help to anyone asking himself/herself what is better to use.

希望这可以帮助任何人问自己什么更好用。

回答by MarkR

I would recommend you don't use stored procedures:

我建议您不要使用存储过程：

• Their language in MySQL is very crappy
• There is no way to send arrays, lists, or other types of data structure into a stored procedure
• A stored procedure cannot everchange its interface; MySQL permits neither named nor optional parameters
• It makes deploying new versions of your application more complicated - say you have 10x application servers and 2 databases, which do you update first?
• Your developers all need to learn and understand the stored procedure language - which is very crap (as I mentioned before)

• 他们在 MySQL 中的语言非常蹩脚
• 无法将数组、列表或其他类型的数据结构发送到存储过程中
• 存储过程永远不能改变它的接口；MySQL 既不允许命名参数也不允许可选参数
• 它使部署应用程序的新版本变得更加复杂——假设您有 10 个应用程序服务器和 2 个数据库，您先更新哪个？
• 你的开发人员都需要学习和理解存储过程语言——这是非常糟糕的（正如我之前提到的）

Instead, I recommend to create a layer / library and put all your queries in there

相反，我建议创建一个图层/库并将所有查询放在那里

You can

你可以

• Update this library and ship it on your app servers with your app
• Have rich data types, such as arrays, structures etc passed around
• Unit test this library, instead of the stored procedures.

• 更新此库并将其与您的应用一起发送到您的应用服务器上
• 有丰富的数据类型，如数组、结构等传递
• 单元测试这个库，而不是存储过程。

---

---

On performance:

关于性能：

• Using stored procedures will decrease the performance of your application developers, which is the main thing you care about.
• It is extremely difficult to identify performance problems within a complicated stored procedure (it is much easier for plain queries)
• You can submit a query batch in a single chunk over the wire (if CLIENT_MULTI_STATEMENTS flag is enabled), which means you don't get any more latency without stored procedures.
• Application-side code generally scales better than database-side code

• 使用存储过程会降低应用程序开发人员的性能，这是您关心的主要问题。
• 在复杂的存储过程中识别性能问题极其困难（对于普通查询来说要容易得多）
• 您可以通过网络在单个块中提交查询批处理（如果启用了 CLIENT_MULTI_STATEMENTS 标志），这意味着您不会在没有存储过程的情况下获得更多延迟。
• 应用程序端代码通常比数据库端代码具有更好的伸缩性

回答by R. van Twisk

If your database is complex and not a forum type with responses, but true warehousing SP will definitely benefit. You can out all your business logic in there and not a single developer is going to care about it, they just call your SP's. I have been doing this joining over 15 tables is not fun, and you cannot explain this to a new developer.

如果您的数据库很复杂并且不是带有回复的论坛类型，那么真正的仓储 SP 肯定会受益。您可以将所有业务逻辑都放在那里，没有一个开发人员会关心它，他们只是调用您的 SP。我一直在这样做，加入超过 15 个表并不有趣，您无法向新开发人员解释这一点。

Developers also don't have access to a DB, great! Leave that up to database designers and maintainers. If you also decide that the table structure is going to get changed, you can hide this behind your interface. n-Tier, remember??

开发人员也无权访问数据库，太好了！把这留给数据库设计者和维护者。如果您还决定要更改表结构，则可以将其隐藏在界面后面。n层，还记得吗？？

High performance and relational DB's is not something that goes together, not even with MySQL InnoDB is slow, MyISAM should be thrown out of the window by now. If you need performance with a web-app, you need proper cache, memcache or others.

高性能和关系型数据库不是在一起的东西，即使 MySQL InnoDB 很慢，MyISAM 也应该被扔出窗外。如果您需要 Web 应用程序的性能，则需要适当的缓存、内存缓存或其他。

in your case, because you mentioned 'Web' I would not use stored procedures, if it was data warehouse I would definitely consider it (we use SP's for our warehouse).

在你的情况下，因为你提到“Web”我不会使用存储过程，如果它是数据仓库我肯定会考虑它（我们使用 SP 作为我们的仓库）。

Tip: Since you mentioned Web-project, ever though about nosql sort of solution? Also, you need a fast DB, why not use PostgreSQL? (trying to advocate here...)

提示：既然你提到了 Web 项目，你有没有想过 nosql 类型的解决方案？另外，你需要一个快速的数据库，为什么不使用 PostgreSQL？（试图在这里倡导...）

回答by Charles Bryant

I used to use MySql and my understanding of sql was poor at best, I spent a fair amount of time using Sql Server, I have a clear separation of a data layer and an application layer, I currently look after a server with 0.5 terabytes.

我以前用过MySql，对sql的理解充其量也很差，我花了相当多的时间使用Sql Server，我有明确的数据层和应用层分离，我目前管理一个0.5TB的服务器。

I have felt frustrated at times not using an ORM as development is really quick with stored procedures it is much slower. I think much of our work could have been sped up by using an ORM.

有时我不使用 ORM 感到沮丧，因为存储过程的开发速度非常快，但速度要慢得多。我认为我们的大部分工作都可以通过使用 ORM 来加速。

When your application reaches critical mass, the ORM performance will suffer, a well written stored procedure, will give you your results faster.

当您的应用程序达到临界质量时，ORM 性能将受到影响，编写良好的存储过程将更快地为您提供结果。

As an example of performance I collect 10 different types of data in an application, then convert that to XML, which I process in the stored procedure, I have one call to the database rather than 10.

作为性能示例，我在应用程序中收集了 10 种不同类型的数据，然后将其转换为 XML，然后在存储过程中进行处理，我对数据库进行了一次调用，而不是 10 次。

Sql is really good at dealing with sets of data, one thing that gets me frustrated is when I see someone getting data from sql in a raw form and using application code to loop over the results and format and group them, this really is bad practice.

Sql 非常擅长处理数据集，让我感到沮丧的一件事是，当我看到有人以原始形式从 sql 获取数据并使用应用程序代码来循环结果和格式并对它们进行分组时，这确实是不好的做法.

My advice is to learn and understand sql enough and your applications will really benefit.

我的建议是充分学习和理解 sql，您的应用程序将真正受益。

回答by Richard

Lots of info here to confuse people, software development is a evolutionary. What we did 20 years ago isn't best practice now. Back in the day with classic client server you wouldnt dream of anything but SPs.

这里有很多信息让人们感到困惑，软件开发是一种进化。我们 20 年前所做的现在不是最佳实践。回到经典客户端服务器的那一天，除了 SP 之外，您不会梦想任何东西。

It is absolutely horses for courses, if you are a big organisation with you will use multi tier, and probably SPs but you will care little about them because a dedicated team will be sorting them out.

这绝对是课程的马匹，如果你是一个大组织，你会使用多层，可能还有 SP，但你不会关心它们，因为一个专门的团队会整理它们。

The opposite which is where I find myself trying to quickly knock up a web app solution, that fleshes out business requirements, it was super fast to leave the developer (remote to me) to knock up the pages and SQL queries and I define the DB structure.

相反，我发现自己试图快速构建一个 Web 应用程序解决方案，它充实了业务需求，让开发人员（对我来说是远程的）构建页面和 SQL 查询并且我定义数据库的速度非常快结构体。

However complexity is growing and without an easy way to provide APIs, I am staring to use SPs to contain the business logic. I think it is working well and sensible, I control this because I can build logic and provide a simple result set for my offshore developer to build a front end around.

然而，复杂性在增加，并且没有一种简单的方法来提供 API，我开始使用 SP 来包含业务逻辑。我认为它运行良好且明智，我可以控制它，因为我可以构建逻辑并为我的离岸开发人员提供一个简单的结果集来构建前端。

Should I find my software a phenomenal success, then more separation of concerns will occur and different implementations of n teir will come about but for now SPs are perfect.

如果我发现我的软件取得了惊人的成功，那么将会出现更多的关注点分离，并且会出现 n teir 的不同实现，但现在 SP 是完美的。

You should know all the tool sets available to you and match them is wise to start with. Unless you are building an enterprise system to start with then fast and simple is best.

您应该了解所有可用的工具集，并开始匹配它们是明智之举。除非您正在构建一个企业系统，否则快速和简单是最好的。

回答by Dave Babler

I think there is a lot of misinformation floating around about database stored queries.

我认为有很多关于数据库存储查询的错误信息。

I would recommend using MySQL Stored Procedures if you're doing many static queries for data manipulation. Especially if you're moving things from one table to another (i.e. moving from a live table to a historical table for whatever reason). There are drawbacks of course in that you'll have to keep a separate log of changes to them (you could in theory make a table that just holds changes to the stored procedures that the DBA's update). If you have many different applications interfacing with the database, especially if say you have a desktop program written in C# and a web program in PHP, it might be more beneficial to have some of your procedures stored in the database as they are platform independent.

如果您正在为数据操作进行许多静态查询，我会建议您使用 MySQL 存储过程。尤其是当您将事物从一张表移动到另一张时（即，无论出于何种原因，从活动表移动到历史表）。当然也有缺点，因为您必须为它们保留单独的更改日志（理论上您可以制作一个只保存对 DBA 更新的存储过程的更改的表）。如果您有许多不同的应用程序与数据库交互，尤其是假设您有一个用 C# 编写的桌面程序和一个用 PHP 编写的 Web 程序，那么将您的某些过程存储在数据库中可能会更有益，因为它们是独立于平台的。

This website has some interesting information on it you may find useful.

这个网站上有一些有趣的信息，你可能会觉得有用。

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

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

As always, build in a sandbox first, and test.

与往常一样，首先在沙箱中构建，然后进行测试。