stack stack - 5 months ago 13
MySQL Question

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

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.

Answer

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.

Comments