MySQL 5: Prepared statement syntax and Dynamic SQL

Every now and then, people are puzzled by the precise status and extent of MySQL support for dynamic SQL.

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:

1. Parsing: lexical and syntactic analysis of the statement


2. Planning: optimizer devises a strategy to realise the required result or action, the execution plan or query plan


3. 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 resultset (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 resultset.

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 resultset.
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, wich 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 statment 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 a 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 resultset (such as INSERT, UPDATE and DELETE) because the actions needed to construct a resultset are generally more timeconsuming 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 statments is the possibility to bind parameters to it. Parameters are specified by writing special ? placholders 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.

Some links

Although it does not seem that the prepared statement syntax was designed for it (word is there will be true dynamic sql support in MySQL 5.1 in the form of the EXECUTE IMMEDIATE syntax), it sure does the job. If you want to read some articles on actually using this feature for a real purpose, check out these links:

"The Wizard revisited"

By Beat Vontobel

"The power of dynamic queries"

by Giuseppe Maxia

"Scheduling Stored Procedure Execution"

by yours truly