如何检测表中的冗余行？

Question

I'm following solution #1 of this answer. After a while, there will be some redundant rows in the table. Suppose this table:

+------+------------------+
| user |     cookie       |
+------+------------------+
| 1    | ojer0f934mf2...  |
| 2    | ko4398f43043...  |
| 2    | 34fjkg3j438t...  |
| 3    | 0243hfd348i4...  |
+------+------------------+

when user 1 removes his browser's cookies, still this row exists in the database:

| 1    | ojer0f934mf2...  |

And when he opens my website, he needs to log in again. So here is the table:

+------+------------------+
| user |     cookie       |
+------+------------------+
| 1    | ojer0f934mf2...  | -- now this row is useless anymore
| 2    | ko4398f43043...  |
| 2    | 34fjkg3j438t...  |
| 3    | 0243hfd348i4...  |
| 1    | 0243hfd348i4...  |
+------+------------------+

Surely in future, the number of such useless rows will increase. Well how can I manage it? Actually I can create an EVENT to clean that table up (per day) by removing redundant rows. But here is the problem: How can I detect them? How can I specify a row is useless?

---

EDIT: I can add a new column and store the timestamp, and then remove all old-timestamp rows by an EVENT (or a cron jobs). But that isn't a perfect solution. I like a cookie be valid until user signs out (so I don't like any limitation (or expire time) for cookies).

Now I'm looking for a solution to determine redundant rows (not old rows).

Answer 1

When a user logs out, explicitly delete the row in the table that corresponds to their current cookie.

For security you should be storing a random value within the cookie, at a secure hash of the cookie within the database tables (e.g. SHA-2).

For dealing with users that no longer use a particular browser, store another field for Last Used Date. Update this date as soon as you detect the cookie for the row being used to access your application.

Then you will want a scheduled task to run every so often that cleans up sessions that have not been recently used.