donglankui1263
2018-01-28 17:29
浏览 109
已采纳

为什么模拟预处理语句不与数据库服务器通信?

I am learning to PHP PDO and confused about the emulated prepared statements. What are emulated prepared statements and native prepared statements.

The reference link says that:

Emulated prepared statements does not communicate with the database server so PDO::prepare() does not check the statement.

Reference Link: Resource

图片转代码服务由CSDN问答提供 功能建议

我正在学习PHP PDO并对模拟的预处理语句感到困惑。 什么是模拟准备语句和本机预准备语句。

参考链接说明:

模拟的预准备语句不与 数据库 服务器,因此PDO :: prepare()不检查语句。

参考链接:资源

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • dqluw20882 2018-01-28 19:36
    已采纳

    The statement "does not communicate with the server" may not be clear. It would be more clear to say "does not communicate with the server when you call prepare()."

    Normally during a prepare & execute sequence, the client sends the SQL as a string to the server when you call prepare(). The server parses the SQL syntax, and returns an error if the SQL syntax has mistakes, or references tables that don't exist, or other errors. If the query is valid, it saves the query on the server. If you had parameter placeholders like ? in your SQL string, these are noted by the server.

    Subsequently, you call execute(), and at that time pass any values to replace the placeholders. This might not involve string-replacement, because the query is no longer stored as a string after it has been parsed and analyzed. Each element of the query is stored as an object, including constant values and parameter placeholders.

    With emulated prepares, PDO skips sending the SQL string to the server during prepare(). It just saves the SQL string on the client-side, in memory in PDO code. This also skips checking the SQL query for errors, because the PDO client does not include a SQL parser or any knowledge of what tables exist, etc.

    When you execute() a query that was saved as an emulated-prepared query, PDO does string-replacement on the saved SQL string, then it submits the final SQL string to the server. This is the first time the server has seen that query, so it parses the syntax and optimizes the query at that time.

    There are at least three possible reasons for emulated prepares:

    1. To allow PDO to support prepare()/execute() for some brands of SQL database that don't natively support that usage, or don't support query parameters.

      But I can't think of any commonly-used brand of SQL database that lacks support for query parameters.

    2. To allow PDO to support both positional parameters (?) and named parameters (:user_id) for any brand of SQL database. Natively, MySQL supports only positional parameters. Oracle supports only named parameters. SQLite supports both styles.

      But PDO did find a way to do a mapping from one type of parameter to the other before prepare(), so you use named parameters with MySQL, even while using non-emulated prepares. PDO can rewrite the query string before sending it via prepare(), converting named parameters to positional parameters, and remembering which position corresponds to which name, so when you call execute() it is sure to send the values in the right order.

    3. PDO 0.1 alpha dates back to 2004. At that time, some people might have been worried that network overhead was too high if you had to communicate with the database server twice for every query. So using emulated prepared statements might have been created for the sake of reducing network trips.

      But if your app is that sensitive to performance, you should run the app and the database servers close to each other on a fast network, 1Gbps or 10Gbps. Any modern data center that is serious about performance should be using these fast networks internally.

    打赏 评论
  • drag2458 2018-01-28 18:07

    PDO operates in two different modes depending on how you configure your connection. You can alter how it operates with the setAttribute method with the PDO::ATTR_EMULATE_PREPARES option if your database driver supports that feature.

    Since PDO has to work with a multitude of databases, not just common cases like MySQL, it needs to cover cases where prepared statements are not supported and it must emulate them.

    When they're emulated it has no way of knowing if the query is valid, it just hopes it'll work out once the final query has been composed and delivered to the server. In other words, although the query behaves as if it was prepared, where the escaping is done properly, it's not actually performed with a prepared statement.

    If they're native you can get a prepared statement handle which will (usually) verify that the query has, at the least, valid syntax.

    If you're curious about how this works at the lowest level, a good example is the MySQL binary protocol documentation where there's a section on how, mechanically speaking, the prepared statements system is implemented.

    打赏 评论

相关推荐 更多相似问题