PreparedStatements的最佳实践;何时何时不去 [英] Best Practices with PreparedStatements; when to and when not to

问题描述

我最近在Web应用程序中再次开始使用预准备语句，我知道不鼓励为所有事务使用预准备语句。我不知道的是什么时候最好使用预备语句。

I recently have began using prepared statements again in a web application, and I know that it is discouraged to use prepared statements for all the transactions. What I do not know is when it is best to use prepared statements or not.

我已经阅读了何时使用而不使用它们，但没有一个例子真的告诉我们使用它们的最佳实践。

I have read of when to use and not use them, but none of the examples really tell best practice of using them.

我试图弄清楚我应该使用哪些数据库调用以及哪些数据库不应该使用。

I am trying to figure out which database calls I should be using them for and which ones I should not.

例如，MySQL网站在下一页的何时使用预准备语句中提到它准备好的语句 - MySQL

For Example the MySQL website mentions it in "When to use prepared statements" on the following page Prepared Statements-MySQL

推荐答案

一般大拇指决定是否参加PreparedStatement的规则是：

The general thumb rule in deciding whether to go for a PreparedStatement or not is:

使用准备好的陈述，除非
有足够的理由不。
准备好的语句在执行之前被编译为
因此可以提高
的性能，并且因为
数据库服务器负责
而增加了针对SQL注入的
安全性特殊字符的编码。

Use Prepared Statements, unless you have sufficient reason not to. Prepared Statements are compiled before execution therefore lending to better performance, and increased security against SQL injection as the database server takes care of the encoding of special characters.

根据您引用的文章，我认为准备语句的效果不如普通查询或存储过程是：

Going by the article that you have referenced, the list of reasons where I believe Prepared Statements are less useful than normal queries or stored procedures are:

• 一次性查询。如果您的应用程序对数据库进行单个查询，并且与其他查询相比，这种查询很少进行，则在这种情况下使用Prepared Statement可能没有意义。其基本原理是必须首先编译Prepared Statement，并缓存该语句的编译形式以供以后使用。对于不经常运行的查询，编译是一种开销。但是，最好使用预准备语句，以避免任何SQL注入问题。


• 数据密集型操作。有时准备语句不如存储过程有效，特别是当需要在同一事务中执行一系列操作时。当您的业务流程需要针对各种表执行多个选择，更新和删除时，存储过程通常比一堆一个接一个地执行的准备语句更好。由于为多个语句的执行进行了多次网络跳转，因此在调用存储过程时会大大减少这种性能损失。这种效果在查询批处理中更为明显，其中在短时间内创建并销毁了多个对象。这往往是数据库管理员和应用程序开发人员之间的一个有争议的问题，因为这是一个边缘案例; DBA将相信通过SP更好地执行操作批处理，而应用程序开发人员认为PreparedStatements可以处理它（通常更好地将所有逻辑放在一个层中）。它最终归结为关于使用SP是否有优势的应用程序。


• 支持本机数据库操作和类型。。这可能不适用于MySQL，但通常JDBC标准不支持数据库支持的所有操作，以及数据库支持的所有SQL /本机/自定义类型。这在Oracle数据库（可能还有IBM DB2？）中更为明显，程序员可以创建自己的类型，这需要编写自定义Java代码，因为JDBC标准不支持数据库中的用户定义类型。类似地，数据库中的其他操作不需要支持（如MySQL文档所述） - 使用Prepared Statement无法创建用户（执行CREATE USER），修改用户权限（执行GRANT操作）等。存储过程更适合此任务，因为它们可以直接或间接方式访问数据库的本机操作集。

• One-time queries. If your application makes a single query to the database, and this is done infrequently compared to the other queries, it might not make sense to use a Prepared Statement in this case. The rationale is that the Prepared Statement must first be compiled and the 'compiled' form of the statement is cached for later use. For queries that are run infrequently, the compilation is an overhead. But still, it is preferable to use prepared statements, to avoid any SQL injection issues.
• Data-intensive operations. Sometimes Prepared Statements are not as effective as stored procedures, especially when a sequence of operations need to be performed in the same transaction. When you have a business process that requires multiple selects, updates and deletes to be executed against a variety of tables, stored procedures are often better than a bunch of prepared statements executed one after the other. This performance penalty can turn serious as several network trips are made for the execution of multiple statements, which is considerably reduced when invoking a stored procedure. This effect is more pronounced in query batching where several objects are created and destroyed in a short duration of time. This often tends to be a contentious issue between database administrators and application developers, as this is an edge-case; DBAs will believe that the batching of operations is better performed via SPs, while application developers believe that PreparedStatements can handle it (its usually better to have all logic in one tier). It eventually boils down to the application on whether using SPs is an advantage or not.
• Support for native database operations and types.. This might not hold good for MySQL, but in general the JDBC standard does not support all the operations supported by a database, and all the SQL/native/custom types supported by the database. This is more pronounced in the Oracle database (and possibly IBM DB2?), where programmers can create their own types, which require custom Java code to be written as the JDBC standard does not support User-Defined Types in the database. Similarly, other operations in the database need to not supported (as the MySQL document states) - one cannot create users (execute CREATE USER), modify user privileges (perform GRANT operations) etc. using a Prepared Statement. Stored procedures are better suited to this task, as they would have access to the native operation set of the database, either in a direct or indirect manner.

这篇关于PreparedStatements的最佳实践;何时何时不去的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！