MySQL
has many good features. Prepared statement is one of good feature which I like
most. When I was a developer I
was using this to complete many operations. After A long time I was refreshing my concepts
of development. Sharing so another can also use enjoy this features
Statement
Handling
MySQL support the prepared statement syntax. For the better part, a prepared statement is much like a
'normal', immediate statement. The main difference is seen in the way
the statement is processed by the server.
Immediate
Statements
When an immediate statement is issued, it is processed directly. Processing
comprises the following steps:
- Parsing: lexical and syntactic analysis of the
statement
- Planning: optimizer devises a strategy to realise the
required result or action, the execution plan or query plan
- Execution: retrieval/seeking, writing and reading of
data and, if applicable, the construction of a resultset
After these steps, the server responds to the request by sending the client a result
set (if applicable), or an acknowledgement that the statement was executed. Of
course, all these actions are performed in concert, and the client is not aware
of these different steps taking place. This becomes clear when typing the
following statement into the MySQL command line client tool:
mysql> select count(*) from information_schema.schemata;
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (0.10 sec)
Immediately (well, almost) the request to select the number of schemata is
responded to by returning the result set.
Prepared
Statements
A prepared statement is initiated by the PREPARE statement. A preparation for a
query equivalent to previous one could look like this:
mysql> prepare stmt from
-> 'select count(*) from
information_schema.schemata';
Query OK, 0 rows affected (0.00 sec)
Statement prepared
This time, we didn't get a result set.
The PREPARE statement instructs the server to parse the query, and possibly, to
devise the execution plan. PREPARE associates an identifier with the statement,
stmt, which acts as a handle to refer to the statement and the corresponding
execution plan.
Actual execution is postponed until called for by the EXECUTE statement, using
the handle to identify the prepared statement to execute.
mysql> execute stmt;
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (0.00 sec)
Which is the same result as we got when we issued the immediate statement.
This seems like an overly complex way to do what we could also do with just one
statement. It is - until we execute it again:
mysql> execute stmt;
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (0.00 sec)
....and again and again and again.
This actually touches upon the major purpose of the prepared statement concept:
when a statement is to be repeatedly executed, a prepared statement is
potentially more efficient. Because only the execution step needs to be
repeated, there is less time wasted on parsing and building the query plan each
time the result is required.
Increased efficiency is even more evident for statements that do not return a result
set (such as INSERT, UPDATE and DELETE) because the actions needed to construct
a result set are generally more time-consuming than parsing and creating an
execution plan.
Now, we need to elaborate just a little on this efficiency argument. In the
Reference manual, you will bump in to this phrase pretty quickly:
MySQL 5.0 provides support for server-side prepared statements. This...takes
advantage of the efficient client/server binary protocol...provided that you
use an appropriate client programming interface. Candidate interfaces include
the MySQL C API client library (for C programs), MySQL Connector/J (for Java
programs), and MySQL Connector/NET.
An alternative SQL interface to prepared statements is available. This
interface is not as efficient as using...a prepared statement API...
A little further on, the manual explains that the prepared statement syntax is
available from within the SQL language primarily for development purposes; NOT
to gain efficiency.
(I did some tests that suggest that the SQL prepared statement syntax is slower
than immediate statements, but I don't know if this has to do with the query
cache. I used INSERT statements BTW)
Using
Parameters
A very powerful feature of prepared statements is the possibility to bind
parameters to it. Parameters are specified by writing special ? placeholders
inside the sql statement that is to be prepared:
mysql> prepare stmt from
-> 'select count(*) from
information_schema.schemata where schema_name = ?'
;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
When EXECUTE-ing the statement, these placeholders must be bound to user variables
with the USING syntax:
mysql> set @schema := 'test';
Query OK, 0 rows affected (0.00 sec)
mysql> execute stmt using @schema;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
So, even though the statement has been prepared, we can still enjoy the
flexibility of controlling the query result.
One thing to keep in mind is that parameters are not implemented using simple
string substitution. For example, the placeholder in the previous example is
not quoted inside the statement. It merely provides a slot for a value, and the
binding process takes care of transferring that value to the slot inside the
statement. Quotes are merely syntactic methods to distinguish a string from the
surrounding code. Because the binding process is way beyond the level of
parsing, it does not make sense to use quotes.
You can't just put a placeholder wherever you like. Parameter placeholders can
appear only in those places inside the statement where you would normally
expect an expression. In particular, you cannot use parameter placeholders to
parameterize identifiers or entire statement structures. So, the following
attempt fails with a syntax error, because the parameter placeholder appears
where you would normally put an identifier:
mysql> prepare stmt from 'create table ? (id int unsigned)';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your
MySQL server version for the right syntax to use near '? (id int unsigned)' at
line 1
However, is is not impossible to paramterize identifiers using the prepared
statement syntax. I'll show that in a bit. It's just that you can't do it using
parameters, because parameters are just a special cases of expressions, like
column references, literals, etc.
Multiple
Parameters
We are not restricted to just one parameter, we can use several:
mysql> prepare stmt from
-> 'select count(*)
-> from
information_schema.schemata
-> where schema_name = ? or
schema_name = ?'
;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> execute stmt
-> using @schema1,@schema2
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
Parameter binding occurs in a positional manner.
Each ? placeholder must be matched bij exactly one user variable in the USING
clause, where the first placeholder matches the first user variable, the second
placeholder matches the second user variable and so on.
You really must match each placeholder by exactly one user variable, or else
you will encounter an error:
1210 (HY000): Incorrect arguments to EXECUTE
Dynamic
SQL
For no particular reason, PREPARE accepts either a string literal, or a user-defined variable to define the statement to prepare. It would've been just
as conceivable to accept just a statement, like so:
mysql> prepare stmt from
-> select count(*) from
information_schema.schemata;
But no, this does not work. This just results in a syntax error.
Anyway, we already saw how PREPARE accepts a statement in the form of a string
literal. It's pretty much the same for a global user variable:
mysql> set @sql_text := 'select count(*) from information_schema.schemata';
mysql> prepare stmt from
-> @sql_text
Query OK, 0 rows affected (0.00 sec)
Statement prepared
Because we can freely assign whatever value we want to @sql_text user variable,
we can use this as a device to employ dynamic SQL.
Now we know how to dynamically manipulate our identifiers too: we just
manipulate our string before assigning it to the user variable, like so:
mysql> set @table_name := 'mytable';
Query OK, 0 rows affected (0.02 sec)
mysql> set @sql_text:=concat('create table ',@table_name,'(id int unsigned)');
Query OK, 0 rows affected (0.00 sec)
Cleaning
up
There's one extra step in the process of using prepared statements that I did
not yet mention. That's cleaning up (I guess that tells you something about me,
right?). Once you've prepared a statement, the handle and the associated
objects on the server's side will remain to exist until the client's session is
over. This means that client will keep some of the server's resources occupied.
Therefore, it's good practice to clean up afterwards. When you're sure you're done
working with the statement, you should DEALLOCATE it:
mysql> deallocate prepare stmt;
This just tells the server to get rid of all the resources associated with the
statement handle, and to forget about the statement handle as well.
