Databases Reference
In-Depth Information
executing it as normal SQL. Preparing a statement also requires two extra round-
trips to the server (to use prepared statements properly, you should deallocate them
after use).
• You cannot currently use a prepared statement inside a stored function (but you
can use prepared statements inside stored procedures).
• You can accidentally “leak” a prepared statement by forgetting to deallocate it.
This can consume a lot of resources on the server. Also, because there is a single
global limit on the number of prepared statements, a mistake such as this can
interfere with other connections' use of prepared statements.
• Some operations, such as
BEGIN
, cannot be performed in prepared statements.
Probably the biggest limitation of prepared statements, however, is that it's so easy to
get confused about what they are and how they work. Sometimes it's very hard to
explain the difference between these three kinds of prepared statements:
Client-side emulated
The client driver accepts a string with placeholders, then substitutes the parameters
into the SQL and sends the resulting query to the server.
Server-side
The driver sends a string with placeholders to the server with a special binary pro-
tocol, receives back a statement identifier, then executes the statement over the
binary protocol by specifying the identifier and the parameters.
SQL interface
The client sends a string with placeholders to the server as a
PREPARE
SQL statement,
sets SQL variables to parameter values, and finally executes the statement with an
EXECUTE
SQL statement. All of this happens via the normal textual protocol.
User-Defined Functions
MySQL has supported
user-defined functions
(UDFs) since ancient times. Unlike stored
functions, which are written in SQL, you can write UDFs in any programming language
that supports C calling conventions.
UDFs must be compiled and then dynamically linked with the server, making them
platform-specific and giving you a lot of power. UDFs can be very fast and can access
a large range of functionality in the operating system and available libraries. SQL stored
functions are good for simple operations, such as calculating the great-circle distance
between two points on the globe, but if you want to send network packets, you need
a UDF. Also, while you can't currently build aggregate functions in SQL stored func-
tions, you can do this easily with a UDF.
With great power comes great responsibility. A mistake in your UDF can crash your
whole server, corrupt the server's memory and/or your data, and generally wreak all
the havoc that any misbehaving C code can potentially cause.
