MySQL in a Nutshell, 2nd Edition

Name

HANDLER

Synopsis

HANDLER table OPEN [AS handle]
      
HANDLER handle READ index { = | >= | <= | < } (value,...) 
   [WHERE condition] [LIMIT ...]
   
HANDLER handle READ index {FIRST|NEXT|PREV|LAST} 
   [WHERE condition] [LIMIT ...]
   
HANDLER handle READ {FIRST|NEXT} 
   [WHERE condition] [LIMIT ...]
    
HANDLER handle CLOSE

A handle provides direct access to a table, as opposed to working from a results set. Handles can be faster than SELECT statements when reading large numbers of rows from a table. MyISAM and InnoDB tables currently support handlers.

A handle is usable only by the session (connection thread) that established it. The table is still accessible by other sessions, though, and is not locked by this statement. Because of this, and because the method provides direct table access, the data in the table can change and even be incomplete as the handler performs successive reads.

Create a handler by issuing a HANDLER statement with the OPEN clause to establish a handle for the table, much like a file handle in a programming language such as Perl. The AS clause and handle name are optional. If an alias is not given, the table name is used as the handler name for subsequent HANDLER statements.

You can then use HANDLER statement formats with READ clauses to read data from a table. Finish by issuing HANDLER with a CLOSE clause.

Here are a couple of basic examples of the HANDLER statement:

HANDLER clients OPEN AS clients_handle;
HANDLER clients_handle READ FIRST;

The first line creates the table handle called clients_handle, based on the clients table. The next SQL statement retrieves the first row of data from the table. The result of this statement is the same as running a SELECT to retrieve all columns of the table and then picking off the first row in the results set. To continue retrieving results in the same way as a results set from a SELECT, issue the following:

HANDLER clients_handle READ NEXT;

Every time the statement is run with the NEXT keyword, the pointer is advanced and the next row in the table is displayed until the end of the table is reached. To retrieve more than one row, you can use the LIMIT clause like this:

HANDLER clients_handle READ NEXT LIMIT 3;

This statement displays the next three rows from the table.

The WHERE clause may be used with a HANDLER...READ statement in the same way as with the SELECT statement. Here is an example:

HANDLER clients_handle READ FIRST
WHERE state = 'MA' LIMIT 5;

This statement displays the first five rows in which the client is located in the state of Massachusetts. Note that no ORDER BY clause is available for HANDLER...READ statements. Therefore, the first five rows are based on the order in which they are stored in the table.

To extract data based on an index, use one of the READ clauses that specify indexes. Here is an example like the previous one, but with the addition of an index:

HANDLER clients_handle READ cid PREV
WHERE state = 'MA' LIMIT 2;

This example retrieves two rows matching the condition of the WHERE clause; the rows come from the previous batch of rows displayed thanks to the PREV keyword. Performance could benefit from the use of the cid index, if it was based on the state column. To retrieve the next set of rows using this syntax, replace PREV with NEXT.

The LAST keyword searches for and retrieves rows starting from the last row of the table. Here is another example using an index:

HANDLER clients_handle READ name = ('NeumeyerGera');

The name index is a combination of the name_last and the name_first column, but only the first four characters of the first name are used by the index. Given the sample database used for this book, this statement displays the row for the client Gerard Neumeyer. The values for each column may be separated with commas (e.g., 'Neumeyer', 'Gera'), or spliced together as shown. This feature, a condition for a multicolumn index, would be a difficult contortion with a SELECT statement.

Table of Contents for MySQL in a Nutshell, 2nd Edition

Name

Synopsis

Table of Contents for
MySQL in a Nutshell, 2nd Edition