MySQLの DELETE には LIMIT OFFSET が使えない！？代わりの方法

最近 MySQL ですごく困ったのがコレ

なぜか DELETE 句に対して OFFSET が使えないこと

これって何でダメなのか不思議ですよね、できてもよさそうなのに

でもルールでそうなってるなら仕方ありません。

ということで、 DELETE句に OFFSET を使う方法とコード例をまとめみました。

このページの目次

なんで？ DELETE 句で OFFSET が使えなくて困った話...
サブクエリを使えば OFFSET もどきができる！
- でも IN を使ってるから少し効率が悪いかも...
サブクエリは色々な場面で応用できる
ここまでのまとめ

なんで？ DELETE 句で OFFSET が使えなくて困った話...

まずこの問題（というか仕様）を発見した経緯について

レコード削除には DELETE を使うと思うんですが、
そのとき最初の数件だけ飛ばして削除したい、みたいな場面があったんです。

その時にこの問題に気付いてしまいました。

例えば次みたいなテーブルとデータを用意したとします。

CREATE TABLE tweets (
  id          INT KEY AUTO_INCREMENT,
  user_name   VARCHAR(15),
  tweet_id    VARCHAR(280),
  tweeted_on  DATETIME
);

INSERT INTO tweets (user_name, tweet_id, tweeted_on) 
  VALUES
  ( 'hoge', '128256', '2019-06-01 19:03:10' ),
  ( 'fuga', '512', '2019-01-17 12:04:19' ), 
  ( 'piyo', '2048', '2019-02-24 20:11:54' ),
  ( 'fuga', '81632', '2019-05-18 10:22:01' ), 
  ( 'piyo', '24', '2018-04-28 08:35:28' );
  
SELECT * FROM tweets;

CREATE TABLE tweets (

id INT KEY AUTO_INCREMENT,

user_name VARCHAR(15),

tweet_id VARCHAR(280),

tweeted_on DATETIME

);

INSERT INTO tweets (user_name, tweet_id, tweeted_on)

VALUES

( 'hoge', '128256', '2019-06-01 19:03:10' ),

( 'fuga', '512', '2019-01-17 12:04:19' ),

( 'piyo', '2048', '2019-02-24 20:11:54' ),

( 'fuga', '81632', '2019-05-18 10:22:01' ),

( 'piyo', '24', '2018-04-28 08:35:28' );

SELECT * FROM tweets;

ツイート情報＆ツイート日時を記録する的なテーブルです。

実際にできあがったデータを表示してみると、こんな感じ

tweetsテーブルの中身
id	user_name	tweet_id	tweeted_on
1	hoge	128256	2019-06-01 19:03:10
2	fuga	512	2019-01-17 12:04:19
3	piyo	2048	2019-02-24 20:11:54
4	fuga	81632	2019-05-18 10:22:01
5	piyo	24	2018-04-28 08:35:28

実際は別のデータだけど、こういうテーブルがあったわけです。

このテーブルでやりたかったことは、
カラム tweeted_on が最新のものを数件飛ばし、古いのだけ削除するみたいな処理

そこで次みたいなSQLを書いて実行してみました。

DELETE FROM tweets
WHERE
ORDER BY tweeted_on DESC
LIMIT 100 OFFSET 3;

DELETE FROM tweets

WHERE

ORDER BY tweeted_on DESC

LIMIT 100 OFFSET 3;

普通に考えれば、これでも通るような気がしますよね？

でも残念、次みたいなエラーが出て怒られてしまいました。

ERROR 1064 (42000) at line 21: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY tweeted_on DESC
LIMIT 100 OFFSET 3' at line 3

1 2	ERROR 1064 (42000) at line 21: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY tweeted_on DESC LIMIT 100 OFFSET 3' at line 3

なんでこうなるのか、良く分からないです。

多分 OFFSET が使えないような事情があるのかも

サブクエリを使えば OFFSET もどきができる！

この OFFSET が使えない問題

解決策は意外と簡単で、サブクエリを使えばいいだけでした。

具体的に書くなら、

サブクエリで OFFSET を使って絞り込み
そのIDを WHERE に渡してSELECTする

こういう感じ

さっきのケースでいうなら、次みたいなSQLを発行すれば解決です。

DELETE FROM tweets
WHERE id IN (
  SELECT id FROM (
    SELECT id FROM tweets
    ORDER BY tweeted_on DESC 
    LIMIT 1000 OFFSET 3
  ) AS dummy
);

SELECT * FROM tweets;