Databases Reference
In-Depth Information
DEALLOCATE PREPARE stmt;
END LOOP;
CLOSE c;
END//
DELIMITER ;
You can use this stored procedure as follows:
mysql>
CALL optimize_tables('sakila');
Another way to write the loop in the procedure is as follows:
REPEAT
FETCH c INTO t;
IF NOT done THEN
SET @stmt_text := CONCAT("OPTIMIZE TABLE ", db_name, ".", t);
PREPARE stmt FROM @stmt_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
UNTIL done END REPEAT;
There is an important difference between the two loop constructs:
REPEAT
checks the
loop condition twice for each loop. This probably won't cause a big performance prob-
lem in this example because we're merely checking an integer's value, but with more
complex checks it could be costly.
Concatenating strings to refer to tables and databases is a good use for the SQL interface
to prepared statements, because it lets you write statements that won't work with pa-
rameters. You can't parameterize database and table names because they are identifiers.
Another scenario is dynamically setting a
LIMIT
clause, which you can't specify with a
parameter either.
The SQL interface is useful for testing a prepared statement by hand, but it's otherwise
not all that useful outside of stored procedures. Because the interface is through SQL,
it doesn't use the binary protocol, and it doesn't really reduce network traffic because
you have to issue extra queries to set the variables when there are parameters. You can
benefit from using this interface in special cases, such as when preparing an enormous
string of SQL that you'll execute many times without parameters.
Limitations of Prepared Statements
Prepared statements have a few limitations and caveats:
• Prepared statements are local to a connection, so another connection cannot use
the same handle. For the same reason, a client that disconnects and reconnects
loses the statements. (Connection pooling or persistent connections can alleviate
this problem.)
• Prepared statements cannot use the query cache in MySQL versions prior to 5.1.
• It's not always more efficient to use prepared statements. If you use a prepared
statement only once, you might spend more time preparing it than you would just
