MySQL 8.0-13.2.4 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 for InnoDB and MyISAM tables.

HANDLER语句提供了对表存储引擎接口的直接访问。它适用于InnoDB和MyISAM表。

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 calls HANDLER ... CLOSE or the session terminates.

HANDLER……OPEN语句打开一个表，使它可以使用后续的HANDLER…READ语句。这个表对象不被其他会话共享，直到会话调用HANDLER…CLOSE或会话终止。

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 using mydb.mytable, further references must use mytable.

如果使用别名打开表，则使用其他HANDLER语句对打开表的进一步引用必须使用别名而不是表名。如果不使用别名，但使用由数据库名称限定的表名打开表，则进一步引用必须使用非限定表名。例如，对于使用mydb.mytable打开的表，进一步的引用必须使用Mytable。

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语法获取一个行，其中指定的索引满足给定值并且where条件满足。如果您有多列索引，请将索引列值指定为逗号分隔的列表。要么为索引中的所有列指定值，要么为索引列的最左边前缀指定值。假设索引my_idx包括三个列，分别是col_a、col_b和col_c。HANDLER语句可以为索引中的所有三列或最左边前缀中的列指定值。例如:

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接口来引用表的PRIMARY KEY，请使用带引号的标识符' 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.

第二个HANDLER …READ语法按照索引顺序从表中获取与WHERE条件匹配的行。

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.

第三个HANDLER …READ语法以符合WHERE条件的自然行顺序从表中获取一行。当需要全表扫描时，它比HANDLER tbl_name READ index_name快。自然行顺序是存储在MyISAM表数据文件中的行的顺序。这条语句也适用于InnoDB表，但是没有这样的概念，因为没有单独的数据文件。

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 Section 13.2.10, “SELECT Statement”.

如果没有LIMIT子句，所有形式的HANDLER…如果有一行可用，则读取一行。要返回特定的行数，请包含LIMIT子句。它具有与SELECT语句相同的语法。请参见13.2.10节“SELECT Statement”。

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

HANDLER ……CLOSE关闭一个用HANDLER ... OPEN打开的表。

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

使用HANDLER接口而不是普通的SELECT语句有几个原因:

• 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.

  • 指定的存储引擎处理程序对象被分配给处理程序…开放。该对象被该表的后续HANDLER语句重用;它不需要为每一个都重新初始化。

  • 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.

  • 处理程序接口不必提供数据的一致外观(例如，允许脏读)，因此存储引擎可以使用SELECT通常不允许的优化。

• HANDLER makes it easier to port to MySQL applications that use a low-level ISAM-like interface. (See Section 15.20, “InnoDB memcached Plugin” for an alternative way to adapt applications that use the key-value store paradigm.)

• HANDLER使它更容易移植到使用低级类似isam接口的MySQL应用程序。(参见第15.20节，“InnoDB memcached Plugin”，以适应使用键值存储范式的应用程序。)

• 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.

• HANDLER使您能够以一种使用SELECT难以(甚至不可能)完成的方式遍历数据库。在处理向数据库提供交互式用户界面的应用程序时，HANDLER接口是查看数据的一种更自然的方式。

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.

HANDLER是一个有点低级的语句。例如，它不提供一致性。也就是说，HANDLER…OPEN不获取表的快照，也不锁定表。这意味着在处理程序之后…打开语句，表数据可以被修改(由当前会话或其他会话)，这些修改可能只部分可见到HANDLER…NEXT或HANDLER…PREV扫描。

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.

• 任何会话都在处理程序的表上执行FLUSH TABLES或DDL语句。

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

• 打开处理程序的会话执行使用表的非handler语句。

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

表的TRUNCATE TABLE关闭用HANDLER OPEN打开的表的所有处理程序。

If a table is flushed with FLUSH TABLES tbl_name WITH READ LOCK was opened with HANDLER, the handler is implicitly flushed and loses its position.

如果用FLUSH TABLES tbl_name with READ LOCK用HANDLER打开了表，则该处理程序将隐式刷新并失去其位置。