我的理解是在服务器上编译一个预准备语句,从而节省了重复解析,优化等的开销.显然,我应该总是更喜欢使用预处理语句来执行多次运行的查询.
这种方法有任何缺点吗？
我正在使用从C到mysql的odbc(libodbc).
解决方法:
Why use prepared statements?
There are numerous advantages to using
prepared statements in your
applications, both for security and
performance reasons.Prepared statements can help increase
security by separating SQL logic from
the data being supplied. This
separation of logic and data can help
prevent a very common type of
vulnerability called an SQL injection
attack. Normally when you are dealing
with an ad hoc query, you need to be
very careful when handling the data
that you received from the user. This
entails using functions that escape
all of the necessary trouble
characters, such as the single quote,
double quote, and backslash
characters. This is unnecessary when
dealing with prepared statements. The
separation of the data allows MySQL to
automatically take into account these
characters and they do not need to be
escaped using any special function.The increase in performance in
prepared statements can come from a
few different features. First is the
need to only parse the query a single
time. When you initially prepare the
statement, MySQL will parse the
statement to check the syntax and set
up the query to be run. Then if you
execute the query many times, it will
no longer have that overhead. This
pre-parsing can lead to a speed
increase if you need to run the same
query many times, such as when doing
many INSERT statements.(Note: While it will not happen with
MySQL 4.1, future versions will also
cache the execution plan for prepared
statements, eliminating another bit of
overhead you currently pay for each
query execution.)The second place where performance may
increase is through the use of the new
binary protocol that prepared
statements can use. The traditional
protocol in MySQL always converts
everything into strings before sending
them across the network. This means
that the client converts the data into
strings, which are often larger than
the original data, sends it over the
network (or other transport) to the
server, which finally decodes the
string into the correct datatype. The
binary protocol removes this
conversion overhead. All types are
sent in a native binary form, which
saves the conversion CPU usage, and
can also cut down on network usage.When should you use prepared statements? Prepared statements can
be useful for all of the above
reasons, however they should not (and
can not) be used for everything in
your application. First off, the type
of queries that they work on is
limited to DML (INSERT, REPLACE,
UPDATE, and DELETE), CREATE TABLE, and
SELECT queries. Support for additional
query types will be added in further
versions, to make the prepared
statements API more general.-> Sometimes prepared statements can actually be slower than regular
queries. The reason for this is that
there are two round-trips to the
server, which can slow down simple
queries that are only executed a
single time. In cases like that, one
has to decide if it is worth trading
off the performance impact of this
extra round-trip in order to gain the
security benefits of using prepared
statements.