MariaDB講座 / ORDER BY・LIMITで並べ替えとページ分割をする

MariaDB講座

ORDER BYは結果の順序、LIMITは返す件数と開始位置を指定します。同じ並べ替え値があるときは一意な補助キーを加え、データが変わらない条件で順位やページ境界を決められるようにします。

順序と件数を両方指定する

高額順の上位三件には価格降順と三件の制限が必要です。LIMIT 3だけでは、どの三件かを指定できません。ASCは昇順、DESCは降順で、各式に個別に適用されます。ORDER BY price DESC, product_id ASCではIDまで降順にはなりません。

WHEREは対象の絞り込み、ORDER BYは出力の順序で、保存行を物理的に並べ直す操作ではありません。数値のように見える文字列も、その型と比較規則で並びます。

同額を含むデータを用意する

SQLファイルの実行方法でcourse_userとして学習サーバーへ接続し、SELECT VERSION(), DATABASE();とSHOW TABLES;で対象を確認します。通常例はUTF-8の.sqlファイルで実行できます。同名の実習表がある場合は定義と行を調べ、必要なデータを保存してから判断します。既存のアプリケーション表を置き換えません。

USE shopcourse;
CREATE TABLE products_17_lab (
  product_id INT NOT NULL AUTO_INCREMENT,
  product_name VARCHAR(100) NOT NULL,
  category VARCHAR(30) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  stock INT NOT NULL DEFAULT 0,
  note VARCHAR(100) NULL DEFAULT NULL,
  PRIMARY KEY (product_id),
  CONSTRAINT ck_p17_price CHECK (price >= 0),
  CONSTRAINT ck_p17_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO products_17_lab
  (product_id, product_name, category, price, stock, note)
VALUES (1, '노트', '문구', 3500.00, 20, NULL),
  (2, '볼펜', '문구', 1500.00, 0, ''),
  (3, '머그컵', '생활', 8000.00, 12, '선물 포장'),
  (4, '스탠드', '생활', 25000.00, 5, NULL),
  (5, '파일', '문구', 1500.00, 50, ''),
  (6, '케이블', '전자', 8000.00, 0, '호환 확인');

SQLの文字列はUTF-8実習の条件をそろえるため、原文の韓国語サンプルを維持しています。ID1～6はノート、ペン、マグカップ、スタンドライト、ファイル、ケーブルです。分類は文具・生活用品・電子用品で、結果はIDと数値を基準に照合できます。ファイルはUTF-8、接続もutf8mb4を使ってください。

ID3と6は8000、2と5は1500です。product_idは一意なので、同額の最終的な順番を決めるキーになります。

同額の順序を明示する

価格降順、同額ならID昇順です。

SELECT product_id, product_name, price FROM products_17_lab
ORDER BY price DESC, product_id ASC LIMIT 3;

ID4・3・6の順になります。IDの方向だけをDESCに変えると、同額の6が3より先になります。比較後はASCへ戻します。今は同額がなくても将来発生するため、偶然の表示順に依存しません。

OFFSETとページ境界を理解する

二件ずつの一・二ページです。

SELECT product_id, price FROM products_17_lab
ORDER BY price DESC, product_id ASC LIMIT 2 OFFSET 0;
SELECT product_id, price FROM products_17_lab
ORDER BY price DESC, product_id ASC LIMIT 2 OFFSET 2;

一ページは4・3、二ページは6・1です。OFFSETはページ数ではなく飛ばす行数で、三ページは4から始まります。LIMIT 2, 2という書式もありますが、件数と位置を明記すると読みやすくなります。

一意なキーを追加しても、別リクエストの間に追加・削除・価格変更が起きれば境界は動きます。また、大きなOFFSETでは返す件数が少なくても先行行を読む負担があります。結果を保つページ分割の改善で確認します。

直前の値を使う方式とNULLの位置

直前が価格8000・ID3なら、低価格か、同額で大きなIDを選びます。

SELECT product_id, price FROM products_17_lab
WHERE price < 8000 OR (price = 8000 AND product_id > 3)
ORDER BY price DESC, product_id ASC LIMIT 2;

6・1となり、二ページの例と一致します。これがキーセット方式で、カーソル条件を両方のソート方向と合わせます。任意ページ番号へ直接移動する方式とは要件が違い、同時更新中に一覧を完全固定するなら追加の整合性方針が必要です。

SELECT product_id, note FROM products_17_lab
ORDER BY note IS NULL ASC, note ASC, product_id ASC;

通常昇順でNULLは低い値として扱われますが、この真偽式は非NULLを先にします。文字の細かな順は照合順序に依存し、最後にIDで同順位を解消しています。

想定外の順序を調べる

LIMITを一時的に外し、主キーとソート式をすべて表示します。方向、型、別名と補助キーを確認し、インデックスの見かけの順番をORDER BYの代わりにしません。利用者が選べる列名と方向は許可一覧へ対応させます。値のプレースホルダーは任意の識別子を一般に置き換えられません。ページ件数も範囲内の整数に制限します。

やり直すときは、選択中のDB・実際の表定義・保存行を確認し、必要なものをバックアップします。削除してよいこの記事の実習表だけに次を実行します。DROP TABLEは定義と行を削除し、通常のROLLBACKでは戻せないため、トランザクションの実験とは分けます。