MySQL Prepare statement: Prepared statements

The MySQL preparation statement (Prepared statements), which uses enhanced binary client/server protocols to efficiently send data between the client and server, enables access to prepared statements through a programming library that supports such line protocols.

When a prepared statement is created, the client library sends a prototype of the actual query like the server, and then the server parses and processes the prototype, saves the partially optimized prototype, and returns a status handle (state Handle) client to the client that can execute the query repeatedly by defining the state handle.

Preparing a statement is much more efficient than executing a query multiple times, for the following reasons:

1, the server only needs to parse a query, which saves parsing and other overhead

2. Because the server caches part of the execution plan, it only needs to perform some tuning steps at once.

3. Sending parameters via binary is much faster than using ASCII code, for example, sending a date type parameter via binary requires only 3 bytes, but sending it via ASCII requires 10 bytes. The effect of the resolution is most pronounced for blobs and text types. Because they can be sent in chunks. Instead of sending it all. The binary protocol also saves memory for the client, while reducing network overhead and the cost of converting data from its own type to a non binary protocol. A binary protocol is not used to submit a query using the normal msyql_query () function only if a prepared statement can use a binary agreement.

4, the entire query will not be sent to the server. Only parameters are sent, reducing network traffic.

5, the MySQL direct bar parameter is saved in the server's buffer area, does not need to copy the data everywhere in the memory.

This column more highlights: http://www.bianceng.cn/database/MySQL/

6, the statement is also good for security, it does not need to be in the application of the value of the transfer and quotation marks. This is more convenient and reduces the likelihood of SQL being injected and other attacks (you never trust user input even if you are using a prepared statement).

Limitations of preparing statements:

1, prepare the statement for only one connection, so the other connection can not use the same handle, in the same reason, a first disconnect and reconnect the client will lose the handle (connection pool live continuous connection will mitigate this problem)

2, prepare the statement cannot use MYSQL5.0 previous version cache

3. Using prepared statements is not always efficient, and if you use only one preparation statement, it may take longer to prepare it than to perform a normal SQL statement, and preparing statements requires additional information interaction between the server and the client.

4. If you forget to destroy the prepared statement, it is possible to cause a resource leak, which consumes a considerable amount of server resources, as well as having a global limit on the number of stored statements, so an error can interfere with other connections that use prepared statements.