php 准备好的语句与存储过程

Prepared Statement vs. Stored Procedure

提问by John

If you are using php5 and mysql5, is there a substantial advantage to using stored procs over prepared statements? ( i read somewhere you may not get substantial performance gains from mysql5 stored proc)

如果您使用的是 php5 和 mysql5，那么使用存储过程比准备好的语句有很大的优势吗？（我在某处读到您可能无法从 mysql5 存储过程中获得实质性的性能提升）

回答by Toby Hede

They are not really the same thing - with stored procedures, your database logic resides inside the database. Prepared statements basically avoid re-parsing queries if they are called multiple times - the performance benefit can vary greatly.

它们实际上并不是一回事 - 对于存储过程，您的数据库逻辑驻留在数据库中。如果多次调用，准备好的语句基本上可以避免重新解析查询 - 性能优势可能会有很大差异。

The choice to use one or the other is really dependent on your specific situation. I don't really use stored procs anymore as I like having all of my logic in one place.

选择使用其中一种实际上取决于您的具体情况。我不再真正使用存储过程，因为我喜欢将所有逻辑放在一个地方。

回答by user101124

Stored procedures make sense for professional-grade (IE enterprise-grade) applications where you:

存储过程适用于专业级（IE 企业级）应用程序，您可以：

1. Want to allow your database engineer to optimize queries for performance
2. Want to abstract complexity of queries to simple API's
3. WANT your logic distributed, because some of what happens in the database might be intellectual property that you don't want to expose to other parties
4. WANT your logic distributed, because that is the nature of distributed, n-tier computing
5. you might want the database engineer or DBA to modify schema without modifying application code (stored procs, by virtue of providing API's, provide a layer of abstraction)

1. 希望让您的数据库工程师优化查询以提高性能
2. 想要将查询的复杂性抽象为简单的 API
3. 希望你的逻辑是分布式的，因为数据库中发生的一些事情可能是你不想暴露给其他方的知识产权
4. 希望您的逻辑是分布式的，因为这是分布式 n 层计算的本质
5. 您可能希望数据库工程师或 DBA 在不修改应用程序代码的情况下修改模式（存储过程，通过提供 API，提供一个抽象层）

There are other reasons.

还有其他原因。

Prepared statements are better for work done within a session. But if you are taking the time to create a prepared statement, you have essentially done everything necessary to create a stored procedure. The difference is that the stored procedure is available across multiple sessions (subject to GRANTS in the database).

准备好的语句更适合在会话中完成的工作。但是，如果您正在花时间创建准备好的语句，那么您基本上已经完成了创建存储过程所需的一切工作。不同之处在于存储过程可跨多个会话使用（取决于数据库中的 GRANTS）。

What I can not figure out is that if you have the option for stored proc vs. prepared statement, why you would bother with prepared statements. Most of the SP vs PS discussions seem to focus on what the differences are between them, not on why to use one vs the other. That always appears to boil down to "depends on what you are trying to do." But I haven't seen a well organized description of: use a proc if you need VS use a statement if you need....

我想不通的是，如果您可以选择存储 proc 与准备好的语句，为什么要为准备好的语句烦恼。大多数 SP 与 PS 的讨论似乎都集中在它们之间的区别上，而不是为什么要使用一个和另一个。这似乎总是归结为“取决于你想要做什么”。但是我还没有看到组织良好的描述：如果需要，请使用 proc VS 如果需要，请使用语句....

回答by Barry Austin

Some advantages of stored procedures:

存储过程的一些优点：

• Portable between languages
• Arguably simplified interface and sometimes performance gains for complex queries and especially multi-query transactions (test!)
• By exposing an interface rather than tables, can be used to improve security and integrity

• 语言之间的可移植性
• 可以说是简化的界面，有时会提高复杂查询的性能，尤其是多查询事务（测试！）
• 通过公开接口而不是表，可用于提高安全性和完整性

Some disadvantages of stored procedures:

存储过程的一些缺点：

• Puts business logic into the database - complicates design, extra place to track for version control and troubleshooting
• Performance losses in some situations (test!)
• Less portable between databases

• 将业务逻辑放入数据库 - 使设计复杂化，需要额外的地方来跟踪版本控制和故障排除
• 某些情况下的性能损失（测试！）
• 数据库之间的可移植性较差

I don't think a single generalized answer exists for this question because there are pros and cons depending on the situation. If you follow principles like simplicity, DRY, testing, and avoiding premature optimization, you're likely to end up fine.

我认为这个问题不存在单一的概括答案，因为根据情况有利有弊。如果您遵循简单、DRY、测试和避免过早优化等原则，您可能会得到很好的结果。

回答by Jeremy Privett

May not be the case or worthwhile to mention here, but stored procedures also are "portable" in the case that they're language-agnostic. You can call the same stored procedures on your database from within, say, Java as you would with PHP. Because the procedures reside in the database, anything with access to the database can query them the same way.

在这里可能不是这种情况或值得一提，但存储过程在它们与语言无关的情况下也是“可移植的”。您可以像在 PHP 中那样从 Java 内部调用数据库上的相同存储过程。因为过程驻留在数据库中，所以可以访问数据库的任何人都可以以相同的方式查询它们。

回答by Ronald Conco

I will start by saying that I do not like the idea of stored procedures, I' rather go the prepared statements route. In this particular case I think you are also comparing apples with oranges...they both there to full fill different functions....

我首先要说我不喜欢存储过程的想法，我宁愿走准备好的语句路线。在这种特殊情况下，我认为您也在比较苹果和橙子……它们都在那里完成不同的功能……

I will only consider stored procedure if the application is 95% database driven only then does it make sense to have some of the logic in the db.

如果应用程序是 95% 的数据库驱动，我只会考虑存储过程，只有在数据库中有一些逻辑才有意义。

回答by Dimitry

The substantial advantage of stored procedures is that your data does not cross a layer (in this case it would be the PHP/MySQL layer) before logic can be applied to it. Some queries may require several select statements, which is slower done through PHP than within MySQL.

存储过程的重要优势在于，您的数据在应用逻辑之前不会跨层（在本例中为 PHP/MySQL 层）。某些查询可能需要多个 select 语句，这在 PHP 中比在 MySQL 中完成要慢。

Now, as tobyhedepoints out, it is good to have all logic in one place. But I have worked on projects where it was simply unrealistic to query the required data using PHP; it had to be done through a stored procedure.

现在，正如tobyhede指出的那样，将所有逻辑集中在一个地方是件好事。但是我曾从事过使用 PHP 查询所需数据根本不切实际的项目；它必须通过存储过程完成。

回答by Kozyarchuk

Not familiar with php, but in general stored procedures are already "compiled" so may produce marginally faster performance over an sql statement.

不熟悉 php，但通常存储过程已经“编译”，因此可能会比 sql 语句产生稍快的性能。

Though my preference usually to stick with SQL from code management/deployment and unit testing perspective.

尽管我通常更喜欢从代码管理/部署和单元测试的角度坚持使用 SQL。