mysql - 删除序列中的重复记录

Question

我创建了一个“登录”表，用于在其中存储用户登录详细信息，如下所示(带有示例数据)。

CREATE TABLE Login ( id int(11) NOT NULL AUTO_INCREMENT, userid int(11) DEFAULT NULL, logintime timestamp NULL DEFAULT CURRENT_TIMESTAMP, status tinyint(4) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

INSERT INTO Login (id, userid, logintime, status)
values (1,  1,  '2014-12-19 16:27:46',  -1),
       (2,  1,  '2014-12-19 16:31:18',  -1),
       (3,  1,  '2014-12-19 16:31:27',  -1),
       (4,  1,  '2014-12-19 16:31:29',  -1),
       (5,  1,  '2014-12-19 16:31:41',  1),
       (6,  2,  '2014-12-19 16:32:01',  -1),
       (7,  2,  '2014-12-19 16:32:03',  -1),
       (8,  1,  '2014-12-19 16:32:06',  -1),
       (9,  1,  '2014-12-19 16:32:37',  -1),
       (10, 2,  '2014-12-19 16:32:58',  1),
       (11, 3,  '2014-12-19 16:33:05',  1),
       (12, 4,  '2014-12-19 16:33:10',  -1),
       (13, 4,  '2014-12-19 16:33:11',  -1),
       (14, 4,  '2014-12-19 16:33:11',  -1),
       (15, 5,  '2014-12-19 16:33:16',  -1),
       (16, 3,  '2014-12-19 16:33:19',  1),
       (17, 3,  '2014-12-19 16:34:39',  1);

在此示例中，字段 status=1 显示登录成功，status= -1 显示登录失败。

在这里，我想删除重复的失败登录(例如 status= -1)记录，除了按顺序排列的第一条记录。
在我的例子中，意味着应删除登录 ID 为 2、3、4 的用户 1，因为它在序列中位于登录 ID 1 之后。那么用户 1 的登录 ID 5 已成功登录(状态 = 1)，因此应保留它，在用户 1 的登录 ID 9 之后，也应该删除，因为它是重复记录。

我想为每个用户执行此操作，并且该表至少有 1400 万(非常大)，并且我想使用 DELETE 查询来执行此操作。他们有任何询问要这样做吗？

Answer 1

首先，我建议您不要将其视为删除。相反，将所需的数据复制到另一个表，截断该表，然后重新插入。

我建议使用变量来获得你想要的。以下标识了您想要的记录:

select l.*
from (select l.*,
             (@rn := if(@id = id and status = -1, @rn + 1,
                        if(@id := id, 1, 1)
                       )
             ) as rn
      from login l cross join
           (select @rn := 0, @id := 0) vars
      order by id
     ) l
where seqnum = 1 or status = 1;

注意:测试此查询以确保它确实满足您的要求。

要使用这个:

create table tmp_login as
    select l.*
    from (select l.*,
                 (@rn := if(@id = id and status = -1, @rn + 1,
                            if(@id := id, 1, 1)
                           )
                 ) as rn
          from login l cross join
               (select @rn := 0, @id := 0) vars
          order by id
         ) l
    where seqnum = 1 or status = 1;

truncate table login;

insert into login(id, userid, logintime, status)
    select id, userid, logintime, status
    from tmp_login;