如何检测表中的冗余行？ [英] How can I detect redundant rows in the table?

问题描述

我正在关注解决方案＃1 -for-different-users-devices＃128471>此答案。过一会儿，表中会有一些冗余的行。假设这个表：

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...  |
+------+------------------+

当用户 1 删除其浏览器的Cookie时，数据库中仍然存在此行：

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...  |
+------+------------------+

今后，这些无用行的数量肯定会增加。那么我该如何管理呢？实际上，我可以通过删除冗余行来创建 EVENT 来清除该表（每天）。但这里是问题：我如何检测他们？如何指定一行无用？

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：并存储时间戳，然后通过 EVENT （或cron作业）删除所有旧时间戳行。但这不是一个完美的解决方案。我喜欢Cookie一直有效，直到用户退出（因此我不喜欢Cookie的任何限制（或过期时间）。

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).

推荐答案

当用户注销时，请明确删除表格中与其当前Cookie对应的行。

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

为了安全起见，您应该在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).

为了处理不再使用特定浏览器的用户，请为最后使用日期存储另一个字段（例如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.

这篇关于如何检测表中的冗余行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！