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の挙動であったり,