【MySQL】条件にあてはまるレコードをn個だけ残すように同じ条件にあてはまる他のレコード全てを削除する方法

　題の通りです。稀に特定の条件に合致するレコードの数を制限するようなクエリを発行する必要があります。例えば、最新のn件のレコードだけを保持して残りは削除したい、といったような具合です。MySQLでこの処理をするSQLを紹介します。

　まずはSQLで適切に対象を取得する必要があります。なんとなくでDELETEすると事故になりやすいですし、論理削除の場合はUPDATEをする必要があります。対象の取得は例えば次のSQLでできます。

SELECT *
FROM accounts
-- 残したいレコードのID以外を持つレコード全てを取得するようにWHERE NOT IN を使う
WHERE account_id NOT IN (
    -- LIMITを使ったサブクエリをさらにサブクエリで包まないと次のエラーが出る
    -- [42000][1235] This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
    SELECT *
    FROM (
        -- サブクエリで残したいレコードだけ取得する
        SELECT account_id
        FROM accounts
        ORDER BY created_at DESC
        LIMIT 5
    ) AS recent_accounts
)

　このクエリは、accountsテーブルから最新の5件を除いたすべてのレコードを選択します。例のように残したいものだけ得られるサブクエリとそのサブクエリの結果のIDを利用して絞り込みすることによって削除すべきものとそうでないものを識別できるようにします、

　参照クエリが期待通りに動作すれば後は楽です。次のようにSELECT * を DELETE に置き換えるだけです。

DELETE
FROM accounts
WHERE account_id NOT IN (
    SELECT *
    FROM (
        SELECT account_id
        FROM accounts
        ORDER BY created_at DESC
        LIMIT 5
    ) AS recent_accounts
);

　もしLaravelで同じ目的を達成するのであれば次のようにもできます。

$idsToKeep = Account::query()
                ->orderByDesc('created_at')
                ->limit(5)
                ->pluck('id');

Account::query()
  ->whereNotIn('account_id', $idsToKeep)
  ->delete();

　サブクエリをやめて変数に展開し、それを元に削除クエリをつくる形です。残す量が大した量でないのであればメモリにも優しいですし、他の処理で使っている絞り込み機能なども利用できるのでこちらの方が便利です。