MySQL道普請便り
第141回 LIMIT句の利用について
MySQLで取得する行数を制限するのに,
今回は,
LIMIT句の使い方
まずはおさらいとして,
mysql> SELECT * FROM t1 LIMIT 1; +-------+ | id | +-------+ | 1 | +-------+
OFFSET句を組み合わせることで,
mysql> SELECT id FROM t1 ORDER BY id LIMIT 3 OFFSET 10000; +-------+ | id | +-------+ | 10001 | | 10002 | | 10003 | +-------+
LIMIT句に2つの引数を渡すことで,LIMIT [OFFSET値], [制限する行数]
の形式で書くことになります。
- ※)
- OFFSETを明示的に書く場合は
LIMIT n OFFSET m
と書きますが,省略して書く場合は逆に LIMIT m, n
と書くので注意してください。
mysql> SELECT id FROM dummy ORDER BY id LIMIT 10000, 3; +-------+ | id | +-------+ | 10001 | | 10002 | | 10003 | +-------+
ORDER BYとLIMITとINDEX
ORDER BYとLIMITをもつSQLに関して,
この挙動は,
LIMIT句のよくある勘違い
たまに,LIMIT n OFFSET m
を使ったクエリは実際 n + m 分の件数を取得した後にm件を破棄しているため,
もし複数回に分けて実施する場合はLIMIT OFFSETを使ってクエリを分けるより,
- ※)
- ただし,
このアプローチはトランザクションで保護していなかったり, トランザクション分離レベルがREAD-COMMITTED, またはREAD-UNCOMMITTEDの場合では, 複数回実行している間にレコードの追加, 削除によって漏れてしまうデータがある事についても十分に考慮が必要です。
件数はアプリケーションで制限するか, SQLで制限するか
指定の件数を表示する際に,
筆者としては,
どうしてもアプリケーションで条件を使って件数を制御しなければいけないこともあるので,
MySQLはDELETE構文にLIMITが利用できる
MySQLはDELETE構文に対してLIMIT句が利用できます。これにより,
実際に動作を確認してみましょう。
mysql> SELECT * FROM t1; +------+-------+ | id | point | +------+-------+ | 1 | 5 | | 2 | 5 | | 3 | 5 | | 4 | 5 | | 5 | 5 | +------+-------+ 5 rows in set (0.01 sec) mysql> DELETE FROM t1 WHERE point = 5 ORDER BY id LIMIT 3; Query OK, 3 rows affected (0.01 sec) mysql> SELECT * FROM t1; +------+-------+ | id | point | +------+-------+ | 4 | 5 | | 5 | 5 | +------+-------+ 2 rows in set (0.00 sec)
point = 5のデータに対して,
上記のクエリはORDER BY idなしでもシンタックスエラーにはならないのですが,
この機能を用いることで,
WINDOW関数を利用してLIMIT句を再現してみる
さて,
MySQLは8.
mysql> SELECT * FROM ( -> SELECT row_number() over (order by id asc) as row_num, -> name -> FROM dummy -> ) tmp -> WHERE tmp.row_num > 500 AND row_num <= 600; 〈省略〉 | 597 | UyWaJGm1h9 | | 598 | JK7m8XZtSk | | 599 | JZeuNUJKMf | | 600 | K3Ab5Z5I2Z | +---------+---------------+ 100 rows in set (0.08 sec) mysql> SELECT id, name FROM dummy ORDER BY id LIMIT 100 OFFSET 500; 〈省略〉 | 597 | UyWaJGm1h9 | | 598 | JK7m8XZtSk | | 599 | JZeuNUJKMf | | 600 | K3Ab5Z5I2Z | +-----+---------------+ 100 rows in set (0.01 sec)
同じように件数を制限して取得することができました。ただし,
まとめ
今回はLIMIT句による結果セットの件数制御に関して説明しました。
LIMIT OFFSETの挙動であったり,
バックナンバー
MySQL道普請便り
- 第141回 LIMIT句の利用について
- 第140回 オンラインスキーママイグレーションツール gh-ostを使ってみよう[その3]
- 第139回 オンラインスキーママイグレーションツール gh-ostを使ってみよう[その2]
- 第138回 オンラインスキーママイグレーションツール gh-ostを使ってみよう[その1]
- 第137回 MySQLTunerを使ってチューニングの足がかりを見つけてみる
- 第136回 CHECK制約を利用してみよう
- 第135回 MySQL 8.0で追加されたoptimizer_switchのフラグについて
- 第134回 DDLと暗黙的なコミットについて
- 第133回 Partial Revokesによる権限の一部剥奪について
- 第132回 Internal Temporary Table(内部テンポラリテーブル)について[その2]
関連記事
- 第47回 MySQL for Excelのご紹介,PostgreSQL 12ベータ版登場
- 第44回 MySQL Clusterのご紹介,PostgreSQL関連記事紹介
- 第43回 MySQL 8.0.15緊急リリースとLINEでの導入事例公開,PostgreSQL 11.2,Pgpool-II 4.0.3リリース
- 第40回 「MySQL Innovation Day 2018 秋」「PostgreSQL Conference Japan 2018」開催
- 第38回 LOCONDO.jpやLINEでのMySQL最新導入事例,PostgreSQL 11ベータ4リリース,Cassandra(イベント情報のみ)