Shulou(Shulou.com)05/31 Report--

Editor to share with you how to use handler in MYSQL, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

HANDLER tbl_name OPEN [AS alias] HANDLER tbl_name READ index_name {= | > = | (...)

HANDLER is a relatively low-level statement. For example, it does not provide consistency. That is, HANDLER...OPEN cannot take a snapshot of a table or lock a table. Which means

When a HANDLER...OPEN statement is written, the table data can be changed (with this thread or another thread), and these changes appear only partially in the HANDLER...NEXT or HANDLER...PREV scan.

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

HANDLER is faster than SELECT:

O A specified storage engine hypervisor target sorts for HANDLER...OPEN. This target is reused for subsequent HANDLER statements of the table; there is no need to reinitialize each statement.

O less analysis is involved.

O there is no optimizer or query verification overhead.

O there is no need to lock the table between two hypervisor requests.

O the hypervisor interface does not need to provide consistent-looking data (for example, allowing disorganized reads), so optimization can be used by the storage engine, which is generally not allowed in SELECT.

Some applications use interfaces similar to ISAM to connect to MySQL. Using HANDLER makes it easier to connect to these applications.

HANDLER allows you to enter and leave the database in a special way. It is difficult (or impossible) to use this approach when using SELECT. Some applications can provide a delivery

The interactive user interface connects to the database. When used with these applications, it is more natural to use the HANDLER interface to view the data.

Example:

Mysql > desc test1

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | a | int (11) | YES | MUL | NULL |

| | b | varchar (45) | YES | | NULL |

+-+ +

Mysql > show index from test1

+- -+

| | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+- -+

| | test1 | 1 | ab | 1 | a | A | 4 | NULL | NULL | YES | BTREE |

+- -+

1 row in set (0.00 sec)

Mysql > handler test1 open

Query OK, 0 rows affected (0.00 sec)

Mysql > handler test1 read first

+-+ +

| | a | b | |

+-+ +

| | 1 | 2 |

+-+ +

1 row in set (0.00 sec)

Mysql > handler test1 read next

+-+ +

| | a | b | |

+-+ +

| | 1 | 2 |

+-+ +

1 row in set (0.00 sec)

Mysql > handler test1 read next

+-+ +

| | a | b | |

+-+ +

| | 1 | 2 |

+-+ +

1 row in set (0.00 sec)

Mysql > handler test1 read next

+-+ +

| | a | b | |

+-+ +

| | 3 | 4 |

| |

+-+ +

1 row in set (0.00 sec)

Mysql > handler test1 read next

+-+ +

| | a | b | |

+-+ +

| | 1 | 2 |

+-+ +

1 row in set (0.00 sec)

Mysql > handler test1 read next

+-+ +

| | a | b | |

+-+ +

| | 3 | 4 |

| |

+-+ +

1 row in set (0.00 sec)

Mysql > handler test1 read next

+-+ +

| | a | b | |

+-+ +

| | 3 | 4 |

| |

+-+ +

Mysql > handler test1 read next

Empty set (0.00 sec)

Mysql > handler test1 read next

Empty set (0.00 sec)

Mysql > handler test1 read next

Empty set (0.00 sec)

Mysql > handler test1 read next

Empty set (0.00 sec)

Mysql > handler test1 read ab= (1)

+-+ +

| | a | b | |

+-+ +

| | 1 | 2 |

+-+ +

1 row in set (0.00 sec)

Mysql > handler test1 read ab= (2)

Empty set (0.00 sec)

Mysql > handler test1 read ab= (3)

+-+ +

| | a | b | |

+-+ +

| | 3 | 4 |

| |

+-+ +

1 row in set (0.00 sec)

Mysql > handler test1 read ab= (3) limit 10

+-+ +

| | a | b | |

+-+ +

| | 3 | 4 |

| |

| | 3 | 4 |

| |

| | 3 | 4 |

| |

| | 3 | 4 |

| |

| | 3 | 4 |

| |

| | 3 | 4 |

| |

| | 3 | 4 |

| |

| | 3 | 4 |

| |

| | 3 | 4 |

| |

| | 3 | 4 |

| |

+-+ +

10 rows in set (0.00 sec)

Mysql > handler test1 read ab= (3) limit 20

+-+ +

| | a | b | |

+-+ +

| | 3 | 4 |

| |

| | 3 | 4 |

| |

| | 3 | 4 |

| |

| | 3 | 4 |

| |

| | 3 | 4 |

| |

| | 3 | 4 |

| |

| | 3 | 4 |

| |

| | 3 | 4 |

| |

| | 3 | 4 |

| |

| | 3 | 4 |

| |

+-+ +

10 rows in set (0.00 sec)

The above is all the content of the article "how to use handler in MYSQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.