Managing & Using MySQL, 2nd Edition

Introducing Bind Variables and Optimizations

As we indicated in Section 9.1.1, creating a statement in DBI is a two-step process: first you create a query, then you create a statement handle from the query. One reason for this division is a complication in SQL called bind variables , which are a kind of placeholder. Instead of specifying every field and value literally in a query, such as:

SELECT author FROM Titles WHERE ISBN = '156592567X'

you can create a generalized query with a question mark as a bind variable:

SELECT author FROM Titles WHERE ISBN = ?

and plug in 156592567X or any other ISBN you want before executing the statement.

Databases operate much more efficiently if you create a general statement containing bind variables and issue repeated queries or updates with different values for the bind variables. This is because the database compiles the SQL statement into an internal format when you create the statement and reuses the internal version on each query or update. This compilation is shown as the second and third of the three phases, parsing and optimization, in Figure 5-1. Perl DBI performs compilation in the prepare( ) statement. If you use bind variables, you must plug in the values between the prepare( ) and the execute( ) statements.

We’ll show a trivial example using a bind variable in this section. More sophisticated examples appear later in the chapter.

The new example alters the previous example to use a WHERE clause in the SELECT statement so that we can ...