Can I define a lifetime for each row of MySQL's table?

Question

I have a table like this:

// banned
+----+---------+---------------+
| id | user_id |   unix_time   |
+----+---------+---------------+
| 1  | 32534   | 1467066745524 |
| 2  | 43535   | 1467066745541 |
| 3  | 24352   | 1467066745618 |
| 4  | 88734   | 1467066746093 |
+----+---------+---------------+

Actually I need to define a expire time for each row when I insert it. Is that possible in MySQL? (I heard it is possible in Redis, well what about MySQL?)

So I want something like this:

// banned
+----+---------+---------------+
| id | user_id |   unix_time   |
+----+---------+---------------+
| 1  | 32534   | 1467066745524 | -- removing this row automatically in 10 min
| 2  | 43535   | 1467066745541 | -- removing this row automatically in 1 hour
| 3  | 24352   | 1467066745618 | -- removing this row automatically 2 day
| 4  | 88734   | 1467066746093 | -- removing this row automatically 8 hours min
+----+---------+---------------+

As you see, each row has a arbitrary lifetime.

if the expiration time is random per user then I think you'll have to insert that data yourself. Just set the expiration date to the current date + however many seconds/minutes/days/etc the user is allowed to exist. As for the deletion, I don't believe there is any way to force mysql to delete records. You'll have to create a job to delete records if the date is past the expiration. — barbiepylon, Jun 27 '16 at 22:42
Why does this expiration matter? For example, if you don't want a user logging into a system past their expiration date then check that date during authentication. — barbiepylon, Jun 27 '16 at 22:44
this link might help you http://stackoverflow.com/questions/11038675/remove-mysql-row-after-specified-time — Lorence Hernandez, Jun 27 '16 at 22:50

Bernd Buffen · Accepted Answer · 2016-06-28T00:47:36.577

you can create a EVENT that running every minute and delete old records like this:

enable scheduler

SET GLOBAL event_scheduler = ON;

create EVENT runs every minute

CREATE EVENT cleanup
ON SCHEDULE EVERY 1 MINUTE
DO 
 DELETE
  FROM yourTable
  WHERE unix_time < UNIX_TIMESTAMP();

Sample

MariaDB [yourschema]> SET GLOBAL event_scheduler = ON;
Query OK, 0 rows affected (0.00 sec)

MariaDB [yourschema]> SHOW GLOBAL VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set (0.00 sec)

MariaDB [yourschema]> SHOW PROCESSLIST;
+-----+-----------------+-----------------+------------+---------+------+-----------------------------+------------------+----------+
| Id  | User            | Host            | db         | Command | Time | State                       | Info             | Progress |
+-----+-----------------+-----------------+------------+---------+------+-----------------------------+------------------+----------+
|  28 | root            | localhost:53255 | yourSchema | Sleep   |   11 |                             | NULL             |    0.000 |
|  29 | root            | localhost:53256 | NULL       | Sleep   |   28 |                             | NULL             |    0.000 |
|  34 | event_scheduler | localhost       | NULL       | Daemon  | 6603 | Waiting for next activation | NULL             |    0.000 |
| 316 | root            | localhost       | yourschema | Query   |    0 | init                        | SHOW PROCESSLIST |    0.000 |
+-----+-----------------+-----------------+------------+---------+------+-----------------------------+------------------+----------+
4 rows in set (0.00 sec)

MariaDB [yourschema]>
MariaDB [yourschema]> SHOW events;
Empty set (0.01 sec)

MariaDB [yourschema]> DROP EVENT IF EXISTS cleanup;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [yourschema]> CREATE EVENT cleanup
    -> ON SCHEDULE EVERY 5 SECOND  ON COMPLETION PRESERVE ENABLE
    -> DO
    ->  DELETE
    ->   FROM schedulerTable
    ->   WHERE expire_unix_time < UNIX_TIMESTAMP() LIMIT 10;
Query OK, 0 rows affected (0.00 sec)

MariaDB [yourschema]> INSERT INTO schedulerTable VALUES
    -> (NULL,100,UNIX_TIMESTAMP(now() + INTERVAL 30 SECOND)),
    -> (NULL,101,UNIX_TIMESTAMP(now() + INTERVAL 40 SECOND)),
    -> (NULL,111,UNIX_TIMESTAMP(now() + INTERVAL  1 MINUTE));
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [yourschema]> SELECT * FROM schedulerTable;SELECT SLEEP(20);
+-------------+---------+------------------+
| id_rubrique | id_stat | expire_unix_time |
+-------------+---------+------------------+
|           1 |     100 |       1467074632 |
|           2 |     101 |       1467074642 |
|           3 |     111 |       1467074662 |
+-------------+---------+------------------+
3 rows in set (0.00 sec)

+-----------+
| SLEEP(20) |
+-----------+
|         0 |
+-----------+
1 row in set (20.00 sec)

MariaDB [yourschema]> SELECT * FROM schedulerTable;SELECT SLEEP(20);
+-------------+---------+------------------+
| id_rubrique | id_stat | expire_unix_time |
+-------------+---------+------------------+
|           2 |     101 |       1467074642 |
|           3 |     111 |       1467074662 |
+-------------+---------+------------------+
2 rows in set (0.00 sec)

+-----------+
| SLEEP(20) |
+-----------+
|         0 |
+-----------+
1 row in set (20.01 sec)

MariaDB [yourschema]> SELECT * FROM schedulerTable;SELECT SLEEP(20);
+-------------+---------+------------------+
| id_rubrique | id_stat | expire_unix_time |
+-------------+---------+------------------+
|           3 |     111 |       1467074662 |
+-------------+---------+------------------+
1 row in set (0.00 sec)


+-----------+
| SLEEP(20) |
+-----------+
|         0 |
+-----------+
1 row in set (20.01 sec)

MariaDB [yourschema]> SELECT * FROM schedulerTable;
Empty set (0.00 sec)

MariaDB [yourschema]>

This condition `WHERE unix_time < UNIX_TIMESTAMP()` always is *true*. So that EVENT removes all rows every time. — stack, Jun 28 '16 at 00:13
if you want to delete the rows to different times you must put the expire timestamp in the row of your table. i will add a sample i a few minutes — Bernd Buffen, Jun 28 '16 at 00:16
Although this works, (1) This has a granularity that is very high and (2) it incurs overhead of running an event to do clean-up when that might not be necessary. Running such frequent events is really a waste of database resources. — Gordon Linoff, Jun 28 '16 at 02:54
@Gordon Linoff - yes correct -the better solution is to do it on both ways. (1) SELECT * FROM .. WHERE expire_unix_time > UNIX_TIMESTAMP(); to prevent to read invalid items and (2) with a EVENT every n minutes for the Garbage Collection. — Bernd Buffen, Jun 28 '16 at 05:27

Gordon Linoff · Answer 2 · 2016-06-27T23:29:02.333

3

You could create a view, something like this:

create view v_table as
    select (case when unix_time < UNIX_TIMESTAMP() then id end) as id,
           (case when unix_time < UNIX_TIMESTAMP() then user_id end) as user_id
    from banned;

Or:

create view v_table as
    select b.* 
    from banned b
    where unixtime < UNIX_TIME();

This returns no information about a user after the timestamp. You can then schedule an event to periodically delete rows with expired information.

edited Jun 27 '16 at 23:29

answered Jun 27 '16 at 22:57

Gordon Linoff

1,242,037
58
646
786

Always all rows of `banned` table will be selected in your query. because your condition is always *true* `unix_time < UNIX_TIMESTAMP()` for all rows. I should add a new column named `life_time` which is containing the row's lifetime. Then here is the condition: `unix_time > UNIX_TIMESTAMP() - life_time`. Thank you anyway .. upvote – stack Jun 27 '16 at 23:15
@stack . . . I suggested an alternative. – Gordon Linoff Jun 27 '16 at 23:29
Actually I always scare to use `view`s. That's because every time I send a query to a view, first the logic of that view will be executed and then my query will be run. So I think using a `view` incurs overhead *(and it is really a waste of database resources)*. But cleaning a table by an `event` *(per day)* needs much less process. *(cleaning = removing expired information)* – stack Jun 28 '16 at 20:16
@stack . . . That isn't really the way views work. Especially in this case, a view should be quite efficient. – Gordon Linoff Jun 29 '16 at 02:10

Can I define a lifetime for each row of MySQL's table?

2 Answers2