(mysql.info) handler

Info Catalog

(mysql.info) do

(mysql.info) data-manipulation

(mysql.info) insert

 
 13.2.3 `HANDLER' Syntax
 -----------------------
 
      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 for `MyISAM' and `InnoDB' tables.
 
 The `HANDLER ... OPEN' statement opens a table, making it accessible
 via subsequent `HANDLER ...  READ' statements. This table object is not
 shared by other threads and is not closed until the thread calls
 `HANDLER ... CLOSE' or the thread 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.
 
 The first `HANDLER ... READ' syntax fetches a row where the index
 specified satisfies the given values and the `WHERE' 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 the `WHERE' condition.
 
 The third `HANDLER ... READ' syntax fetches a row from the table in
 natural row order that matches the `WHERE' condition. It is faster than
 `HANDLER TBL_NAME READ INDEX_NAME' 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 of `HANDLER ... READ' fetch a
 single row if one is available. To return a specific number of rows,
 include a `LIMIT' clause. It has the same syntax as for the `SELECT'
 statement. See  select.
 
 `HANDLER ... CLOSE' closes a table that was opened with `HANDLER ...
 OPEN'.
 
 `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 thread or other threads) and these
 modifications might be only partially visible to `HANDLER ... NEXT' or
 `HANDLER ...  PREV' scans.
 
 There are several reasons to use the `HANDLER' interface instead of
 normal `SELECT' statements:
 
    * `HANDLER' is faster than `SELECT':
 
         * 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 table does not have to be locked between two handler
           requests.
 
         * The handler interface does not have to provide a consistent
           look of the data (for example, dirty reads are allowed), so
           the storage engine can use optimizations that `SELECT' does
           not normally allow.
 
    * For applications that use a low-level `ISAM'-like interface,
      `HANDLER' makes it much easier to port them to MySQL.
 
    * `HANDLER' enables you to traverse a database in a manner that is
      difficult (or even impossible) to accomplish with `SELECT'. The
      `HANDLER' interface is a more natural way to look at data when
      working with applications that provide an interactive user
      interface to the database.
 

Info Catalog

(mysql.info) do

(mysql.info) data-manipulation

(mysql.info) insert