如何从MySQL表中选择数据并删除其他所有内容？

Question

我有一个简单的MySQL表格，像这样:

CREATE TABLE `logins` (
  `user` int(10) NOT NULL,
  `time` int(10) NOT NULL,
  `ip` varchar(20) NOT NULL,
  PRIMARY KEY (`user`,`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

使用CRON，我每24小时根据以下方式清理此表，只保留每个用户IP的唯一登录，即如果某个用户从IP X登录5次，则仅保留他使用该IP登陆的最新时间，并删除其他4个登陆记录。

这是我的查询语句：

DELETE FROM `logins` WHERE (`user`, `time`) NOT IN(
   SELECT `user`, `maxtime`
   FROM(
      SELECT `user`, MAX(`time`) as `maxtime` FROM `logins` GROUP BY `user`, `ip`
   ) as `a`
)

这种查询-子查询的方式有点慢。完成需要大约3秒钟。

然而，内部部分非常快。如果我省略SELECT部分，像这样：

SELECT `user`, MAX(`time`) as `maxtime` FROM `logins` GROUP BY `user`, `ip`

这只需要不到0.005秒。

所以我在想：如果我相反地做呢？ 不是“删除除X以外的所有内容”，而是“选择X，删除所有内容，重新插入X”？

这种操作可行吗？

类似于以下内容：

1) SELECT `user`, MAX(`time`) as `maxtime` FROM `logins` GROUP BY `user`, `ip`
/* store this somewhere temporarily */

2) TRUNCATE TABLE `logins`

3) reinsert data from step 1 to `logins`

- Frantisek

Answer 1

使用另一个（临时或永久）表格来插入您想要保留的数据。清空原表。然后再将数据插入回去...

INSERT INTO LoginsTemp 
SELECT * FROM Logins WHERE ...; 

TRUNCATE Logins; 

INSERT INTO Logins 
SELECT * FROM LoginsTemp; 

- Z .

---

请您能否提供一个例子？ - Frantisek

您展示的这三个查询语句一起运行时，是否会在整个时间段内锁定表格？例如，如果第一个查询运行后，在清空表格之前有任何用户登录系统，会发生什么？我会失去他们的数据吗？ - Frantisek

你可以创建一个事务来防止并发问题。 - Z .

为了避免第二次重新加载，您可以进行原子重命名：将表Logins重命名为LoginsOld，将LoginsTemp重命名为Logs。 - atxdba

Answer 2

我会使用这个来删除每个用户和IP组合中除最新记录之外的所有记录：

DELETE l1.*
FROM
  logins l1 INNER JOIN logins l2
  ON l1.user=l2.user
     AND l1.ip=l2.ip
     AND l1.time<l2.time

请查看 fiddle 这里。

- fthiella

---

我认为这不会产生期望的结果。我想要每个用户每个IP保留所有最近的登录记录，而不仅仅是每个用户。如果您从50个IP登录，则需要为您的用户提供50个条目。 - Frantisek