MySQL :: MySQL 8.4 Reference Manual :: 15.2.5 HANDLER Statement (original) (raw)

15.2.5 HANDLER Statement

HANDLER tbl_name OPEN [ [AS] alias]

HANDLER tbl_name READ index_name { = | <= | >= | < | > } (value1,value2,...)
    [ WHERE where_condition ] [LIMIT ... ]
HANDLER tbl_name READ index_name { FIRST | NEXT | PREV | LAST }
    [ WHERE where_condition ] [LIMIT ... ]
HANDLER tbl_name READ { FIRST | NEXT }
    [ WHERE where_condition ] [LIMIT ... ]

HANDLER tbl_name CLOSE

The HANDLER statement provides direct access to table storage engine interfaces. It is available forInnoDB and MyISAM tables.

The HANDLER ... OPEN statement opens a table, making it accessible using subsequent HANDLER ... 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 other HANDLER 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 first HANDLER ... 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 index my_idx includes three columns named col_a,col_b, and col_c, in that order. The HANDLER 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 the HANDLER interface to refer to a table's PRIMARY KEY, use the quoted identifier`PRIMARY`:

HANDLER tbl_name READ `PRIMARY` ...

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

The third HANDLER ... READ syntax fetches a row from the table in natural row order that matches theWHERE condition. It is faster thanHANDLER _`tblname`_ READ_`indexname`_ when a full table scan is desired. Natural row order is the order in which rows are stored in a MyISAM table data file. This statement works for InnoDB tables as well, but there is no such concept because there is no separate data file.

Without a LIMIT 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 with HANDLER ... OPEN.

There are several reasons to use the HANDLER interface instead of normal SELECT statements:

• HANDLER is faster thanSELECT:
  • A designated storage engine handler object is allocated for the HANDLER ... OPEN. The object is reused for subsequent HANDLER 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 that SELECT does not normally permit.
• HANDLER makes it easier to port to MySQL applications that use a low-level ISAM-like interface.
• HANDLER enables you to traverse a database in a manner that is difficult (or even impossible) to accomplish with SELECT. 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 does not take a snapshot of the table, and does not lock the table. This means that after a HANDLER ... OPEN statement is issued, table data can be modified (by the current session or other sessions) and these modifications might be only partially visible to HANDLER ... NEXT or HANDLER ... 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 executes FLUSH 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 TABLES tbl_name WITH READ LOCK was opened with HANDLER, the handler is implicitly flushed and loses its position.