HANDLER 是一個很有趣的指令,可以讓我們一列列的瀏覽資料.
先來建立測試Table.

CREATE TABLE ithelp1007a(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
class CHAR(1) NOT NULL,
name CHAR(20) NOT NULL,
score TINYINT UNSIGNED NOT NULL
);

INSERT INTO ithelp1007a(class, name, score) VALUES
('A', 'Tom', 78),
('A', 'Mary', 25),
('A', 'John', 65),
('B', 'Hitomi', 95),
('B', 'Asami', 84),
('B', 'Keiko', 73);

首先打開 HANDLER
asami@[akina]>HANDLER ithelp1007a OPEN;
Query OK, 0 rows affected (0.00 sec)

讀取第一筆
asami@[akina]>HANDLER ithelp1007a READ FIRST;
+----+-------+------+-------+
| id | class | name | score |
+----+-------+------+-------+
|  1 | A     | Tom  |    78 |
+----+-------+------+-------+
1 row in set (0.00 sec)

讀取下一筆
asami@[akina]>HANDLER ithelp1007a READ NEXT;
+----+-------+------+-------+
| id | class | name | score |
+----+-------+------+-------+
|  2 | A     | Mary |    25 |
+----+-------+------+-------+
1 row in set (0.00 sec)

關閉HANDLER
asami@[akina]>HANDLER ithelp1007a CLOSE;
Query OK, 0 rows affected (0.00 sec)

以主鍵為補助方式瀏覽

HANDLER ithelp1007a OPEN AS H;

第一筆
asami@[akina]>HANDLER H READ `PRIMARY` FIRST;
+----+-------+------+-------+
| id | class | name | score |
+----+-------+------+-------+
|  1 | A     | Tom  |    78 |
+----+-------+------+-------+
1 row in set (0.00 sec)

最後一筆
asami@[akina]>HANDLER H READ `PRIMARY` LAST;
+----+-------+-------+-------+
| id | class | name  | score |
+----+-------+-------+-------+
|  6 | B     | Keiko |    73 |
+----+-------+-------+-------+
1 row in set (0.00 sec)

從最後一筆往前一筆
asami@[akina]>HANDLER H READ `PRIMARY` PREV;
+----+-------+-------+-------+
| id | class | name  | score |
+----+-------+-------+-------+
|  5 | B     | Asami |    84 |
+----+-------+-------+-------+
1 row in set (0.00 sec)

關閉HANDLER


若要以分數score為順序來瀏覽時,我們必須要建立
以score為key的INDEX.

asami@[akina]>CREATE INDEX SCORE ON ithelp1007a(score);
Query OK, 0 rows affected (0.29 sec)
Records: 0  Duplicates: 0  Warnings: 0

一樣OPEN HANDLER

asami@[akina]>HANDLER ithelp1007a OPEN AS `S`;
Query OK, 0 rows affected (0.00 sec)

asami@[akina]>HANDLER S READ SCORE FIRST;
+----+-------+------+-------+
| id | class | name | score |
+----+-------+------+-------+
|  2 | A     | Mary |    25 |
+----+-------+------+-------+
1 row in set (0.00 sec)

因為剛剛建立INDEX 時是升冪排列,所以會從分數最低的開始.

INDEX也可以用組合的KEY,例如class,score 的組合,
以及降冪的情況,可以自行視需要建立,依序存取.

HANDLER也可以加上 LIMIT, 來取出多筆.
或是使用條件,例如找出84分的.

asami@[akina]>HANDLER S READ SCORE=(84);
+----+-------+-------+-------+
| id | class | name  | score |
+----+-------+-------+-------+
|  5 | B     | Asami |    84 |
+----+-------+-------+-------+
1 row in set (0.00 sec)

在有些情況下,可以使用HANDLER 來展示資料.