はじめに
Googleの検索結果などで「○○件中××件」という表記が見られますが、このように複数ページに結果を表示してページを遷移させることを「ページング」と呼びます。
今回はページング処理のベースとなる、データをDBから取り出す際のSQLを少し考えてみました。
使用した環境
MySQL
- OS
- CentOS7.7（1908）
- RDBMS
- MySQL8.0.19
Oracle
- Oracle社が提供しているOracle Live SQLでOracle19cを利用しました。
作成したSQL
- 【MySQL】SQLで基本統計量を求めるで作成した「八ヶ岳の山々のデータ」を使用して、「標高の低い順に10件ずつデータを取得する」というSQLを作りました。
MySQLの場合
- 以下のSQLでは、「山データを標高の昇順に並べて、先頭（OFFSET=0）から10件取得する」という処理が行われます。
- ページを遷移させたい時は、OFFSET値だけ変更します。
- 2ページ目を表示させる場合、
LIMIT 10 OFFSET 10となります。 - ページに表示させたい件数を調節する時は、LIMIT値を変更します。
- 5件ずつ表示させる場合、
LIMIT 5 OFFSET 0となります。
MySQLの場合
SELECT
*
FROM
mountain
ORDER BY
altitude ASC
LIMIT 10
OFFSET 0;
実行結果
+----+--------------+----------+--------------+
| id | name | altitude | note |
+----+--------------+----------+--------------+
| 23 | 八子ヶ峰 | 1833 | 北八ヶ岳 |
| 21 | 双子山 | 2224 | 北八ヶ岳 |
| 16 | 丸山 | 2330 | 北八ヶ岳 |
| 20 | 大岳 | 2381 | 北八ヶ岳 |
| 17 | 茶臼山 | 2384 | 北八ヶ岳 |
| 2 | 西岳 | 2398 | 南八ヶ岳 |
| 18 | 縞枯山 | 2403 | 北八ヶ岳 |
| 19 | 北横岳 | 2480 | 北八ヶ岳 |
| 15 | 中山 | 2496 | 北八ヶ岳 |
| 1 | 編笠山 | 2524 | 南八ヶ岳 |
+----+--------------+----------+--------------+
Oracleの場合
Oracle12c以降の場合
- Oracle12c以降であれば、
OFFSET n ROWSとFETCH FIRST n ROWS ONLYを組み合わせて、「山データを標高の昇順に並べて、先頭（OFFSET=0）から10件取得する」という処理を実現できます。 - @nakaie さん、コメントありがとうございました。
- MySQLの
OFFSETとLIMITの組み合わせよりもわずかにSQLが長くなりますが、OFFSET n ROWSとFETCH FIRST n ROWS ONLYの組み合わせの方が、SQLを見た時に処理内容を素直に理解しやすいと感じました。
Oracle12c以降の場合
SELECT
*
FROM
mountain
ORDER BY
altitude ASC
OFFSET 0 ROWS
FETCH FIRST 10 ROWS ONLY;
実行結果
+----+----------+------+----------+
| ID | 山名 | 標高 | 備考 |
+----+----------+------+----------+
| 23 | 八子ヶ峰 | 1833 | 北八ヶ岳 |
| 21 | 双子山 | 2224 | 北八ヶ岳 |
| 16 | 丸山 | 2330 | 北八ヶ岳 |
| 20 | 大岳 | 2381 | 北八ヶ岳 |
| 17 | 茶臼山 | 2384 | 北八ヶ岳 |
| 2 | 西岳 | 2398 | 南八ヶ岳 |
| 18 | 縞枯山 | 2403 | 北八ヶ岳 |
| 19 | 北横岳 | 2480 | 北八ヶ岳 |
| 15 | 中山 | 2496 | 北八ヶ岳 |
| 1 | 編笠山 | 2524 | 南八ヶ岳 |
+----+----------+------+----------+
Oracle11g以前の場合
- 以下のSQLでは、「インラインビューで標高の昇順に並べた時のROW_NUMBERを取得して、標高の昇順に付けられた通し番号の範囲を指定して先頭（通し番号=1）から10件取得する」という処理が行われます。
- MySQLやOracle12c以降と比べて、かなりSQLが長く複雑になってしまいます...
Oracle11g以前の場合
SELECT
ID,
山名,
標高,
備考
FROM
(
SELECT
id AS ID,
name AS 山名,
altitude AS 標高,
note AS 備考,
ROW_NUMBER() OVER(ORDER BY altitude ASC) AS 通し番号
FROM
mountain
)
WHERE
通し番号 >= 1
AND 通し番号 <= 10
ORDER BY
標高 ASC;
備忘録
- Oracle12c以降ではMySQLの
AUTO INCREMENTに相当するGENERATED ALWAYS AS IDENTITYおよびGENERATED BY DEFAULT AS IDENTITYが登場しました。 - Oracle11gまでは、
AUTO INCREMENTに近いものとしてSEQUENCEや列の最大値＋1を使うケースが多かったようです。 - 今回は自動採番した後に
id列の値を書き換える必要が無かったため、GENERATED ALWAYS AS IDENTITYを使いました。
Oracle用のCREATE文
CREATE TABLE mountain (
id NUMBER(2) GENERATED ALWAYS AS IDENTITY,
name VARCHAR2(50) NOT NULL,
altitude NUMBER(4) NOT NULL,
note VARCHAR2(200),
CONSTRAINT PK_id PRIMARY KEY (id)
);