MySQL :: MySQL 8.4 参考手册 :: 15.2.5 HANDLER 语句

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 15 条 SQL 语句 / 15.2 数据操作语句 / 15.2.5 HANDLER 语句

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 100%

15.2.5 HANDLER 语句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

HANDLER 语句提供对表存储引擎接口的直接访问。该语句可用于 InnoDB 和 MyISAM 表。The HANDLER statement provides direct access to table storage engine interfaces. It is available for InnoDB and MyISAM tables.

HANDLER ... OPEN 语句打开表，使其通过后续的 HANDLER ... READ 语句访问。该表对象不被其他会话共享，且在会话调用 HANDLER ... CLOSE 或会话终止时才关闭。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 语句引用开表时，必须使用别名而非表名。如果你不使用别名，但用数据库名称为限定的表名打开表，后续引用必须使用不限定的表名。例如，对于使用 mydb.mytable，后续引用必须使用 mytable。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 ... READ 语法获取一行，其中指定的索引满足给定值，且满足WHERE条件。如果你有多列索引，将索引列值指定为逗号分隔列表。要么为索引中所有列指定值，要么为索引列的最左前缀指定值。假设索引my_idx包含三列，分别依序为col_a、col_b和col_c。HANDLER 语句可以指定索引中所有三列的值，或最左前缀列的值。例如：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) ...

要使用 HANDLER 接口来引用表的 PRIMARY KEY可以使用引号标识符 `PRIMARY`：To employ the HANDLER interface to refer to a table's PRIMARY KEY, use the quoted identifier `PRIMARY`:

HANDLER tbl_name READ `PRIMARY` ...

第二个 HANDLER ... READ 语法从表中按索引顺序获取一行，该行与 WHERE。The second HANDLER ... READ syntax fetches a row from the table in index order that matches the WHERE condition.

第三种HANDLER ... READ语法以自然行顺序从表中获取与WHERE条件匹配的行。它比HANDLER tbl_name READ index_name中存储的顺序MyISAM表数据文件。该语句同样适用于InnoDB表，但没有单独的数据文件，因此没有这个概念。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.

没有 LIMIT 子句，所有形式的 HANDLER ... READ 取出单行（如有的话）。要返回特定行数，包含 LIMIT 子句。它的语法与 SELECT 语句相同。参见第 15.2.13 节，“SELECT Statement”。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 15.2.13, “SELECT Statement”.

HANDLER ... CLOSE 关闭一个以 HANDLER ... OPEN打开的表。HANDLER ... CLOSE closes a table that was opened with HANDLER ... OPEN.

该接口而非普通HANDLER语句SELECT有几个原因There are several reasons to use the HANDLER interface instead of normal SELECT statements:

HANDLER 比 SELECTHANDLER is faster than SELECT:
- 为 HANDLER ... OPEN。该对象被用于该表的后续 HANDLER 语句;不需要为每个语句重新初始化。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.
- 处理程序接口不必提供数据的一致外观（例如，脏读取被允许），因此存储引擎可以使用优化 SELECT 通常不允许。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 使得移植到使用低级 ISAM类接口的 MySQL 应用程序变得更容易。HANDLER makes it easier to port to MySQL applications that use a low-level ISAM-like interface.
HANDLER 使您能够以一种使用 SELECT 难以（甚至不可能）完成的方式遍历数据库。当处理为数据库提供交互式用户界面的应用程序时，HANDLER 接口是一种更自然的数据查看方式。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 是一种相对底层的语句。例如，它不提供一致性。也就是说，HANDLER ... OPEN 不会对表进行快照，也不会锁定表。这意味着在执行 HANDLER ... OPEN 语句之后，表数据可能会被修改（由当前会话或其他会话），这些修改可能仅部分可见于 HANDLER ... NEXT 或 HANDLER ... PREV 扫描。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 会丢失其在表中的位置。当以下两种情况同时成立时，就会发生这种情况：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:

任何会话对 handler 所对应的表执行 FLUSH TABLES 或 DDL 语句。Any session executes FLUSH TABLES or DDL statements on the handler's table.
在打开 handler 的会话中执行使用表的非 HANDLER 语句。The session in which the handler is open executes non-HANDLER statements that use tables.

TRUNCATE TABLE for a table 会关闭所有用 HANDLER OPEN。TRUNCATE TABLE for a table closes all handlers for the table that were opened with HANDLER OPEN.

如果使用 FLUSH TABLES tbl_name WITH READ LOCK 刷新了表，而该表以 HANDLER 打开，则 handler 会被隐式刷新并丧失其位置。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.

首页上一级下一节