MySQL リミットとオフセットの例

LIMITキーワードとは何ですか MySQL?

その LIMIT キーワードは、クエリ結果で返される行数を制限します。SELECT、UPDATE、DELETE ステートメントで使用できるため、クエリが読み取る行数と書き込みが影響を与える行数を制限できます。

LIMITキーワードの構文は以下のとおりです。

SELECT {fieldname(s) | *} FROM tableName(s) [WHERE condition] LIMIT N;

Pr_media

• 「SELECT {フィールド名 | *} FROM tableName(s)” は SELECTステートメント クエリで返したいフィールドが含まれています。
• 「[WHERE条件]」 オプションですが、指定すると結果セットのフィルタを指定します。 WHERE句 は LIMIT より前に適用されるため、フィルタリングが最初に行われ、残ったものに上限が適用されます。
• 「Nの制限」 がキーワードであり、 N は0から始まる任意の数値です。制限値に0を指定すると、レコードは返されません。例えば5を指定すると、5件のレコードが返されます。テーブル内のレコード数がNより少ない場合は、すべてのレコードが返され、エラーは発生しません。

構文は短いが、例を挙げる前に、その存在理由を述べておく価値がある。

なぜLIMITキーワードを使うべきなのか？

私たちが開発していると仮定しますping myflixdb上で動作するアプリケーションについて、システム設計者から、読み込み速度の低下を防ぐため、1ページに表示されるレコード数を20件に制限するよう求められています。このような要件を満たすシステムをどのように実装すればよいでしょうか？

LIMITキーワードはまさにこの状況に対応します。アプリケーションにすべてのメンバー行を取り込んでそのほとんどを破棄する代わりに、クエリは1ページあたり20件のレコードを返し、データベースが処理を行います。これによって3つの利点が得られます。

• より速い応答： ディスクから読み込まれるデータ量が少なくなり、ネットワークを通過するデータ量も少なくなる。
• メモリ使用量を削減する： アプリケーションにはテーブル全体ではなく、1ページ分の行が格納されます。
• セーファー氏は次のように書いています。 更新または DELETE このステートメントは、エラーが影響を与える行数を制限します。

MySQL LIMITクエリの例

以下の例は、myflixdbデータベースのmembersテーブルに対して実行されます。最初の例では、2行のみが返され、それ以上の情報は返されません。

SELECT * FROM members LIMIT 2;

上記の結果が示すように、返還されたメンバーはわずか2名でした。

データベースから10人のメンバーのリストを取得する

Myflixデータベースから登録済みの上位10名のリストを取得したいとします。以下のスクリプトは、それらのリストを要求するものです。

SELECT * FROM members LIMIT 10;

スクリプトを実行すると、以下の結果が得られます。

LIMIT句のNがテーブルのレコード数より大きいため、返されたメンバーは9人だけです。明示的に9行を要求すると、同じ結果セットが得られます。

SELECT * FROM members LIMIT 9;

💡ヒント: LIMIT は、サーバーが生成する順序に関係なく行を選択します。 ORDER BY 行の同一性が重要な場合は常にこの条項を適用してください。そうでない場合、「最初の10人」が同じ9人を2回意味するとは限らないためです。

行数を制限するのがこの機能の前半部分です。ウィンドウの開始位置を選択するのが後半部分です。

LIMITクエリでOFFSET値を使用する

その OFFSET value は、LIMIT キーワードと組み合わせて使用​​されることが最も多いです。これは、サーバーがデータの取得を開始する行を指定するもので、その行より前の行はスキップされます。

テーブルの中央から始まる限られた数のメンバーのみを表示したいとします。以下のスクリプトは2行目から開始し、結果を2件のレコードに限定します。

SELECT * FROM `members` LIMIT 1, 2;

実行すると MySQL ワークベンチ myflixdbに対して以下の結果を得ました。

ここで注意 オフセット= 1したがって、2行目が最初に返される行であり、 制限 = 2そのため、2件のレコードしか返ってきません。

2つの引数を持つ形式では、オフセットが最初に記述され、行数が次に記述されるため、誤って逆になってしまう可能性があります。 MySQL また、曖昧さを解消する明示的な形式も受け入れており、新しいコードではそちらを優先すべきである。

SELECT * FROM `members` LIMIT 2 OFFSET 1;

どちらのステートメントも同じ2行を返します。オフセットが理解できれば、すべてのリスト画面が依存するページネーションパターンがそこから直接導き出されます。

LIMITとOFFSETを使用してクエリ結果をページ分割する方法

ページネーションは、大量の検索結果を番号付きのページに分割する機能で、LIMITとOFFSETを組み合わせることで実現されます。ページリクエストは、1つの画面に表示されるレコード数であるページサイズと、ユーザーが要求するページ番号という2つの値によって制御されます。

オフセットは、単一の計算式を用いてそれらから導き出されます。

-- OFFSET = page_size * (page_number - 1)
SELECT membership_number, full_names
FROM members
ORDER BY membership_number ASC
LIMIT 20 OFFSET 0;   -- page 1

2ページ目は同じ制限を維持し、オフセットを1ページ分前方に移動させます。

SELECT membership_number, full_names
FROM members
ORDER BY membership_number ASC
LIMIT 20 OFFSET 20;  -- page 2

ページ分けされたリストを正確かつ迅速に作成するための3つのルール。

1. 常に並べ替え: ORDER BY句のないページネーションクエリでは、サーバーが呼び出し間で行の順序を変更できるため、同じレコードが2つの異なるページに表示され、別のレコードが完全に非表示になる可能性があります。
2. 一意の列でソートする: ソート列で同順位が発生した場合、同順位の行の順序は確定しません。主キーでソートするか、主キーをタイブレーカーとして追加することで、この問題は解消されます。
3. 詳細なページをご覧ください： オフセット100000力 MySQL 10万行を読み込んで破棄し、次の20行を返す。応答時間はページ番号に比例して長くなる。

非常に深いページネーションの場合、キーセットページネーションはオフセットを完全に回避します。スキップする行数を数える代わりに、クエリは前のページの最後のキーを記憶し、そのキー以降の行を要求します。

SELECT membership_number, full_names
FROM members
WHERE membership_number > 20      -- last id from the previous page
ORDER BY membership_number ASC
LIMIT 20;

この形式は、インデックスが前の行をたどるのではなく、開始キーに直接ジャンプするため、どの深さでも高速です。ただし、ページは順番にたどる必要があるため、ジャンプはping 500ページに直接アクセスすることはできなくなりました。

制限 MySQL vs TOP と FETCH FIRST

LIMIT句はすべてのSQL方言に含まれているわけではないため、クエリがデータベースエンジン間を移動する必要がある場合には、この点が重要になります。 MySQL, PostgreSQL, SQLite LIMIT キーワードを共有します。SQL Server は TOP を使用し、 Oracle 標準のFETCH FIRST句を使用します。以下の表は、これら3つの構文を比較したものです。

いずれの場合も動作は同じです。行数を制限し、必要に応じて最初に一定数の行をスキップします。違いはスペルだけです。複数のエンジンで実行する必要のあるクエリでは、行数制限句をコードベース全体に散在させるのではなく、分離しておくべきです。

よくあるご質問