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

I have a simple MySQL table like this:

我有一个简单的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;

With CRON, every 24 hours, I'm performing a cleanup on this table in a way that I keep only unique logins for every users's IP, i.e. if a certain user logins 5 times from IP X, I keep only the latest time he logged in with that IP and delete the remaining 4.

CRON,每24小时,我执行清理表,我只保留独特的登录为每一个用户的IP,即如果某个用户登录5次从IP X,我只保留最新的一次他登录与IP和删除其余4。

This is my QUERY:

这是我的查询:

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

This query-subquery way is kind of slow. It takes around 3 seconds to complete.

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

However, the inner part is extremely quick. If I leave out only the SELECT part, like this:

然而，内在的部分是极快的。如果我只漏掉选择的部分，像这样:

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

It takes less than 0.005 seconds.

不到0.005秒。

So I was wondering: what if I did the opposite? Not 'delete all but X', but instead, 'select X, delete all, re-insert X'?

所以我在想:如果我反其道而行之呢?不是“删除除X之外的所有内容”，而是“选择X、删除所有内容、重新插入X”?

Is this possible?

这是可能的吗?

Something like this:

是这样的:

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`

2 个解决方案

#1

I would use this to delete all record except the most recent for every combination of user and ip:

我会用这个删除所有记录，除了最近的用户和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

Please see fiddle here.

请在这里看到小提琴。

#2

Use another (temporary or not) table to insert the data you want to keep. Truncate. Then insert it back...

使用另一个表(临时的或非临时的)插入希望保存的数据。截断。然后把它放回到…

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

TRUNCATE Logins; 

INSERT INTO Logins 
SELECT * FROM LoginsTemp;

#1