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
分享 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:
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.
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.
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.
分享
