■はじめに
テーブルに登録しているデータで「TIMESTAMP」を使用することがあります。
この「TIMESTAMP」のデータを使用して、1日前のデータは削除するなど、一定期間で削除する方法を記載します。
もちろん手動でSQLを流してもいいですが、手間なので、今回は自動で削除する方法を記載します。
■手順
まずは、以下で、24時間前以前のデータを取得することができます。
SELECT * FROM [DB名].[テーブル名] WHERE TIMESTAMPDIFF(HOUR, [TIMESTAMPのカラム名], CURRENT_TIMESTAMP()) > 24
上記をちょっと解説すると、「TIMESTAMPDIFF」というSQLの関数を利用して時間の差分を出しています。
第一引数に「HOUR」とすることで、時間の差分になります。
※ちなみに、「MINUTE」なら分、「SECOND」なら秒です。
では、上記を元に、今度は削除のSQLを以下に記載します。
DELETE FROM [DB名].[テーブル名] WHERE TIMESTAMPDIFF(HOUR, [TIMESTAMPのカラム名], CURRENT_TIMESTAMP()) > 24
これで、削除のSQLは完成です。
ただ、今回は自動で削除したいので、cronを使用します。
以下のコマンドでcronの編集モードにします。
crontab -e
編集モードになったら、以下を記載します。
0 0 * * * mysql --user=[ユーザー名] --password=[パスワード] --database=[DB名] --execute="DELETE FROM [DB名].[テーブル名] WHERE TIMESTAMPDIFF(HOUR, [TIMESTAMPのカラム名], CURRENT_TIMESTAMP()) > 24" > /var/log/mysql-[DB名]-[カラム名]-delete.log 2>&1
上記は、毎日0時にDELETE文を実行するcronです。
実行されたら、以下にログが出力されるので、確認してみてください。
/var/log/mysql-[DB名]-[カラム名]-delete.log
