MySQL 最新5件を除いて削除

Question

PHP の PDO で 最新5件を除いて 全てのデータを削除したいです。

1. 全件数取得
2. 古いものから(全件数-5)件を削除

上記のような 考えは浮かんだのですが、
もう少しスマートな方法があれば教えてください。
(1回のSQL実行で削除できる方法とか無いでしょうか？)

ちなみに Date に 作成日時が入っています。

Answer 1

nabe3さんが投稿されてる
ユーザ変数を使う方法ですが

OracleACEのAketiJyuuzouさんとyoku0825さんと
日本オラクルの木村明治さんによると
MySQLのユーザ変数は評価順序が未定義です。
http://qiita.com/AketiJyuuzou/items/cced9b70cc714b382d98

なので、期待したレコードがDeleteされることが保証されません。

Answer 2

MySQL一撃で行けますよ。
Delete文のWhere句にはサブクエリが使えます。

dateカラムをorder by句で降順(新しい順)に並べて、
limit 5を末尾に付けたselect文を発行してあげましょう。

そうしてやることで消しては行けない5レコードのID一覧が拾得出来るので、
not in等を使って他のレコードだけ削除しましょう。

Comments

[KiyoshiMotoki]

MySQLでは、IN句の中にLIMIT句を含めることはできないはずです。
https://dev.mysql.com/doc/refman/5.6/ja/subquery-restrictions.html
https://dev.mysql.com/doc/refman/5.7/en/subquery-restrictions.html

-----
mysql> SELECT * FROM t1
　　-> WHERE s1 IN (SELECT s2 FROM t2 ORDER BY s1 LIMIT 1);
ERROR 1235 (42000): This version of MySQL doesn't yet support
　'LIMIT & IN/ALL/ANY/SOME subquery'
-----

もう少し具体的に、方法を教えていただけますか？

見当違いの指摘でしたら、申し訳ありません。

Answer 3

これなら、IN内にLIMIT使えます

DELETE FROM table_name
WHERE
  column_name NOT IN (
    SELECT * FROM (
      SELECT column_name
      FROM table_name
      ORDER BY column_name
      LIMIT 5) v
  )

Answer 4

SQLFiddleが落ちてるので
動作確認してませんが

delete from t1
 where (select count(*)+1
        from t1 b
        where b.ColA > t1.ColA) >= 6;

Answer 5

LIMITなしで書いてみました

WITH句は各日付より新しい日付を結合して、
各データの日付より新しい日が5件以上の日付を取得

後はEXISTSで該当する日付のデータを削除しています

WITH OLD_DATE AS(
	SELECT
		  STD.DATE
	FROM TBL STD
	JOIN TBL NEW
	ON STD.DATE < NEW.DATE
	GROUP BY
		  STD.DATE
	HAVING COUNT(*) >= 5
)
DELETE TBL
WHERE EXISTS(
	SELECT 1
	FROM OLD_DATE
	WHERE OLD_DATE.DATE = TBL.DATE
)

Comments

[kutsulog]

ps13zierさんがせっかくヒントだけにしたものを
丸っとSQLにしちゃってますね(^-^;

Answer 6

ユーザー定義変数を使い、重複しない行番号もどきを付けて処理する案はどうでしょうか？

CREATE TABLE `aaa_log` (
	`id` INT(11) NOT NULL,
	`date` DATE NOT NULL,
	PRIMARY KEY (`id`)
);

SET @rownum=0;

DELETE FROM aaa_log
WHERE id IN (
	SELECT t1.id
	FROM
	(
		SELECT @rownum:=@rownum+1 as ROW_NUM, id, date
		FROM aaa_log
		ORDER BY date, id
	) t1
	WHERE ROW_NUM <= 5
);

DELETEを実行前に内側のSELECTの結果が想定通りか確認してみる事をお勧めします。

参考）MySQL 5.6 リファレンスマニュアル / 言語構造 / ユーザー定義変数

Comments

[退会済みユーザー]

期待通りの結果を得られるかもしれませんが、それが確約されないSQLで、
仕事では使いものになりませんので、マイナス投票します。

http://download.nust.na/pub6/mysql/doc/refman/5.1/ja/user-variables.html

>基本的なルールは、ステートメントの一部でユーザ変数値を割り当てないこと
>および同一ステートメント内の他部分で同じ変数を使用しないことです。
>期待通りの結果を得られるかもしれませんが、これは確約されていません。

Answer 7

やっぱり普通は ORDER BY と LIMIT を使いますよね。ちょっと変わった方法でのヒントを挙げておきます。

レコード件数がそれほど多くない場合に、例えば、以下のようにキーでグループ化し、同じテーブルの日付同士で比較して件数を計上すれば同一テーブル上での順位付けが実現できます。

SELECT Count(*) as no, A.key, A.date
  FROM table A, table B
 WHERE (A.date < B.date) OR (A.date = B.date AND A.key < B.key)
 GROUP BY A.key, A.date
 ORDER BY no

※ OR を使っている箇所は同一順位の場合の対処として入れていますが、ここは要求仕様に従って調整が必要かもしれませんね

この結果は、日時に対する順位が列挙されている表そのものであるため、HAVING を使って特定の順位以降に絞ったレコード群が削除対象そのものであることが解ると思います。なので、こういった SELECT 結果を使って DELETE する書き方にまとめてしまえば、SQL だけで処理できます。

以上、ご参考になれば幸いです。

Answer 8

SQLワンセンテンスでは無いですが、1つのトランザクションで

START TRANSACTION;

alter table trg rename to tmp;
create table trg(id integer, Date date);
insert into trg select * from tmp order by Date desc limit 5;
drop table tmp;
select * from trg;

/* COMMIT; */

1.元テーブルの名前変更して
2.新規で元テーブルと同じテーブルを作って
3.[1]から5件とって[2]に入力
4.[1]のテーブルを削除
5.[3]を確認
6.ここまで問題なければコミット

事前にバックアップを取って事故に備えましょう。
制約の内容次第では動かないかもしれません。
テーブルクリエイトのSQLを紛失してたらできません。

Comments

[hirohiro]

miyabi-sunさんのやり方のほうがいいですね。

[KiyoshiMotoki]

> テーブルクリエイトのSQLを紛失してたらできません。

create table trg like tmp;

とすれば、クリエイト文を指定する必要なく実現できますね。

[KiyoshiMotoki]

念のため付け加えさせていただきますが、MySQLではDDLにトランザクションは効きません。
https://dev.mysql.com/doc/refman/5.6/ja/implicit-commit.html
https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html

重箱の隅をつつくようで恐縮ですが。。

[hirohiro]

> MySQLではDDLにトランザクションは効きません。
フォローありがとうございます。となると私の書いたコードにおいてトランザクションを挟む意味は殆ど(全く？）無いですね。

[KiyoshiMotoki]

hirohiro様

> となると私の書いたコードにおいてトランザクションを挟む意味は殆ど(全く？）無いですね。

このままだと、おっしゃるとおりですね。

しかし、ちょっとアレンジすれば、同じアイデアで
トランザクションを効かせることができるようになります。
例えば、以下のような感じです。

DROP TABLE IF EXISTS tmp; # 念のため実行する。
CREATE TABLE tmp LIKE trg;

START TRANSACTION;

INSERT INTO tmp SELECT * FROM trg ORDER BY Date DESC LIMIT 5;
DELETE FROM trg;
INSERT INTO trg SELECT * FROM tmp;

COMMIT;

DROP TABLE tmp;

個人的には、複雑なワンクエリより
こちらのようなシンプルな複数クエリのほうが好みです。

Answer 9

プライマリキーはありますか？

Comments

[KiyoshiMotoki]

回答ではありません。
"情報の追加・修正の依頼をする"欄に書くべき内容です。

[退会済みユーザー]

すみません、昨日寝ぼけて書き込んでしまいました。