MySQL で過去レコードを削除するイベントを登録

MySQL で過去レコードを削除するイベントを登録する方法のメモ。

■テーブル作成・データ登録

drop table if exists test_event1;

create table test_event1 (
  id   varchar(16)  not null,
  t    timestamp,
  primary key(id),
  index (t)
);

insert into test_event1 (id, t) values ('01', '2024-04-18 23:43:00');
insert into test_event1 (id, t) values ('02', '2024-04-18 23:43:30');
insert into test_event1 (id, t) values ('03', '2024-04-18 23:44:00');
insert into test_event1 (id, t) values ('04', '2024-04-18 23:44:30');
insert into test_event1 (id, t) values ('05', '2024-04-18 23:45:00');
insert into test_event1 (id, t) values ('06', '2024-04-18 23:45:30');
insert into test_event1 (id, t) values ('07', '2024-04-18 23:46:00');
insert into test_event1 (id, t) values ('08', '2024-04-18 23:46:30');
insert into test_event1 (id, t) values ('09', '2024-04-18 23:47:00');
insert into test_event1 (id, t) values ('10', '2024-04-18 23:47:30');

■イベント登録

5分経過したレコードを削除するイベントを登録。

drop event if exists event1;

create event event1
on schedule
  every 1 minute
do
  delete from
    test_event1
  where
    t < date_sub(now(), interval 5 minute)
;

■時間が経過するとレコードが削除される

mysql> select now(), id, t from test_event1;
+---------------------+----+---------------------+
| now()               | id | t                   |
+---------------------+----+---------------------+
| 2024-04-18 23:47:08 | 01 | 2024-04-18 23:43:00 |
| 2024-04-18 23:47:08 | 02 | 2024-04-18 23:43:30 |
| 2024-04-18 23:47:08 | 03 | 2024-04-18 23:44:00 |
| 2024-04-18 23:47:08 | 04 | 2024-04-18 23:44:30 |
| 2024-04-18 23:47:08 | 05 | 2024-04-18 23:45:00 |
| 2024-04-18 23:47:08 | 06 | 2024-04-18 23:45:30 |
| 2024-04-18 23:47:08 | 07 | 2024-04-18 23:46:00 |
| 2024-04-18 23:47:08 | 08 | 2024-04-18 23:46:30 |
| 2024-04-18 23:47:08 | 09 | 2024-04-18 23:47:00 |
| 2024-04-18 23:47:08 | 10 | 2024-04-18 23:47:30 |
+---------------------+----+---------------------+

mysql> select now(), id, t from test_event1;
+---------------------+----+---------------------+
| now()               | id | t                   |
+---------------------+----+---------------------+
| 2024-04-18 23:48:45 | 03 | 2024-04-18 23:44:00 |
| 2024-04-18 23:48:45 | 04 | 2024-04-18 23:44:30 |
| 2024-04-18 23:48:45 | 05 | 2024-04-18 23:45:00 |
| 2024-04-18 23:48:45 | 06 | 2024-04-18 23:45:30 |
| 2024-04-18 23:48:45 | 07 | 2024-04-18 23:46:00 |
| 2024-04-18 23:48:45 | 08 | 2024-04-18 23:46:30 |
| 2024-04-18 23:48:45 | 09 | 2024-04-18 23:47:00 |
| 2024-04-18 23:48:45 | 10 | 2024-04-18 23:47:30 |
+---------------------+----+---------------------+

mysql> select now(), id, t from test_event1;
+---------------------+----+---------------------+
| now()               | id | t                   |
+---------------------+----+---------------------+
| 2024-04-18 23:49:39 | 05 | 2024-04-18 23:45:00 |
| 2024-04-18 23:49:39 | 06 | 2024-04-18 23:45:30 |
| 2024-04-18 23:49:39 | 07 | 2024-04-18 23:46:00 |
| 2024-04-18 23:49:39 | 08 | 2024-04-18 23:46:30 |
| 2024-04-18 23:49:39 | 09 | 2024-04-18 23:47:00 |
| 2024-04-18 23:49:39 | 10 | 2024-04-18 23:47:30 |
+---------------------+----+---------------------+