Documentation Home
MySQL 9.4 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 41.2Mb
PDF (A4) - 41.3Mb
Man Pages (TGZ) - 262.8Kb
Man Pages (Zip) - 368.8Kb
Info (Gzip) - 4.1Mb
Info (Zip) - 4.1Mb


15.2.5 HANDLER Statement

HANDLERtbl_name OPEN [ [AS]alias]HANDLERtbl_name READindex_name { = | <= | >= | < | > } (value1,value2,...)    [ WHEREwhere_condition ] [LIMIT ... ]HANDLERtbl_name READindex_name { FIRST | NEXT | PREV | LAST }    [ WHEREwhere_condition ] [LIMIT ... ]HANDLERtbl_name READ { FIRST | NEXT }    [ WHEREwhere_condition ] [LIMIT ... ]HANDLERtbl_name CLOSE

TheHANDLER statement provides direct access to table storage engine interfaces. It is available forInnoDB andMyISAM tables.

TheHANDLER ... OPEN statement opens a table, making it accessible using subsequentHANDLER ... READ statements. This table object is not shared by other sessions and is not closed until the session callsHANDLER ... CLOSE or the session terminates.

If you open the table using an alias, further references to the open table with otherHANDLER statements must use the alias rather than the table name. If you do not use an alias, but open the table using a table name qualified by the database name, further references must use the unqualified table name. For example, for a table opened usingmydb.mytable, further references must usemytable.

The firstHANDLER ... READ syntax fetches a row where the index specified satisfies the given values and theWHERE condition is met. If you have a multiple-column index, specify the index column values as a comma-separated list. Either specify values for all the columns in the index, or specify values for a leftmost prefix of the index columns. Suppose that an indexmy_idx includes three columns namedcol_a,col_b, andcol_c, in that order. TheHANDLER statement can specify values for all three columns in the index, or for the columns in a leftmost prefix. For example:

HANDLER ... READ my_idx = (col_a_val,col_b_val,col_c_val) ...HANDLER ... READ my_idx = (col_a_val,col_b_val) ...HANDLER ... READ my_idx = (col_a_val) ...

To employ theHANDLER interface to refer to a table'sPRIMARY KEY, use the quoted identifier`PRIMARY`:

HANDLERtbl_name READ `PRIMARY` ...

The secondHANDLER ... READ syntax fetches a row from the table in index order that matches theWHERE condition.

The thirdHANDLER ... READ syntax fetches a row from the table in natural row order that matches theWHERE condition. It is faster thanHANDLERtbl_name READindex_name when a full table scan is desired. Natural row order is the order in which rows are stored in aMyISAM table data file. This statement works forInnoDB tables as well, but there is no such concept because there is no separate data file.

Without aLIMIT clause, all forms ofHANDLER ... READ fetch a single row if one is available. To return a specific number of rows, include aLIMIT clause. It has the same syntax as for theSELECT statement. SeeSection 15.2.13, “SELECT Statement”.

HANDLER ... CLOSE closes a table that was opened withHANDLER ... OPEN.

There are several reasons to use theHANDLER interface instead of normalSELECT statements:

  • HANDLER is faster thanSELECT:

    • A designated storage engine handler object is allocated for theHANDLER ... OPEN. The object is reused for subsequentHANDLER statements for that table; it need not be reinitialized for each one.

    • There is less parsing involved.

    • There is no optimizer or query-checking overhead.

    • The handler interface does not have to provide a consistent look of the data (for example,dirty reads are permitted), so the storage engine can use optimizations thatSELECT does not normally permit.

  • HANDLER makes it easier to port to MySQL applications that use a low-levelISAM-like interface.

  • HANDLER enables you to traverse a database in a manner that is difficult (or even impossible) to accomplish withSELECT. TheHANDLER interface is a more natural way to look at data when working with applications that provide an interactive user interface to the database.

HANDLER is a somewhat low-level statement. For example, it does not provide consistency. That is,HANDLER ... OPEN doesnot take a snapshot of the table, and doesnot lock the table. This means that after aHANDLER ... OPEN statement is issued, table data can be modified (by the current session or other sessions) and these modifications might be only partially visible toHANDLER ... NEXT orHANDLER ... PREV scans.

An open handler can be closed and marked for reopen, in which case the handler loses its position in the table. This occurs when both of the following circumstances are true:

  • Any session executesFLUSH TABLES or DDL statements on the handler's table.

  • The session in which the handler is open executes non-HANDLER statements that use tables.

TRUNCATE TABLE for a table closes all handlers for the table that were opened withHANDLER OPEN.

If a table is flushed withFLUSH TABLEStbl_name WITH READ LOCK was opened withHANDLER, the handler is implicitly flushed and loses its position.