SQL删除所有行的时间戳比(现在 - x天)要早,除了保留最近的n条记录
如果我有下表,我需要删除所有比(现在 - x天)更早的记录,但不是在最近的n个记录中。SQL删除所有行的时间戳比(现在 - x天)要早,除了保留最近的n条记录
具体的例子:用户不能在90天内和重复使用的密码可能不会再使用过去的10个密码。如果我每90天更换一次密码,我仍然无法重复使用密码进行10次更改。
CREATE TABLE PASSWORD_HISTORY (
ID BIGINT NOT NULL AUTO_INCREMENT,
USER_NAME VARCHAR(255) NOT NULL,
PASSWORD VARCHAR(255) NOT NULL,
SALT VARCHAR(255),
CREATED_TIMESTAMP BIGINT NOT NULL,
UPDATED_TIMESTAMP BIGINT NOT NULL,
TENANT_ID INTEGER DEFAULT -1234,
PRIMARY KEY (ID)
)ENGINE INNODB;
-- drop table password_history;
create table password_history
(
id bigint null auto_increment primary key,
user_name varchar(255) not null,
created_timestamp bigint not null
);
-- delete from password_history where id>0; -- safe mode sometimes barfs
insert password_history (user_name,created_timestamp) values ('fred',100);
insert password_history (user_name,created_timestamp) values ('fred',200);
insert password_history (user_name,created_timestamp) values ('fred',300);
insert password_history (user_name,created_timestamp) values ('fred',400);
insert password_history (user_name,created_timestamp) values ('fred',401);
insert password_history (user_name,created_timestamp) values ('fred',402);
insert password_history (user_name,created_timestamp) values ('fred',403);
insert password_history (user_name,created_timestamp) values ('fred',404);
insert password_history (user_name,created_timestamp) values ('fred',405);
insert password_history (user_name,created_timestamp) values ('fred',406);
insert password_history (user_name,created_timestamp) values ('fred',407);
insert password_history (user_name,created_timestamp) values ('fred',500);
insert password_history (user_name,created_timestamp) values ('fred',555);
insert password_history (user_name,created_timestamp) values ('fred',unix_timestamp(now()));
insert password_history (user_name,created_timestamp) values ('stan',unix_timestamp(now()));
alter table password_history add deleteMe int;
select * from password_history;
-- variables n and d
-- n=10, users last 10 records
-- d=90, last 90 days
-- rows where password created more than 90 days ago (replace 90 below as desired)
select * from password_history
where unix_timestamp(now()) - created_timestamp>(60*60*24*90)
order by id desc
-- update password_history set deleteMe=1 where id>0; -- safe mode sometimes barfs
-- update password_history set deleteMe=null where id>0; -- safe mode sometimes barfs
update password_history
join
(
select ph.id,ph.user_name,ph.created_timestamp
from password_history ph
join
(
select ph.id,ph.user_name,ph.created_timestamp
from password_history ph
join
(
select user_name,max(id),max(created_timestamp),count(*) as theCount
from password_history xx
group by user_name
having theCount>10
) inR2
on ph.user_name=inR2.user_name
order by ph.user_name,ph.created_timestamp desc
limit 10
) inR1
on ph.id=inR1.id
) bigThing
on password_history.id=bigThing.id
set deleteMe='1'
where password_history.id>0 -- this gets rid of the safe mode barfing
update password_history
join
(
select user_name from password_history p2 where p2.deleteMe=1
) phMany
on password_history.user_name=phMany.user_name
set deleteMe=2
where password_history.deleteMe is null
-- select * from password_history order by user_name,created_timestamp desc;
-- look at the ones with deleteMe=2
我给你留下90天的方面。如果您同意结果,那么执行delete where deleteMe = 2 ...和alter table drop the column – Drew
DELETE
FROM PASSWORD_HISTORY
Where PASSWORD_HISTORY.ID Not IN
(
SELECT ID FROM(SELECT pass1.[USER_NAME], Count(*) num, pass1.ID
FROM Password_History pass1 JOIN Password_History pass2
ON pass1.[USER_NAME] = pass2.[USER_NAME] AND pass1.ID <= pass2.ID
Group BY pass1.[USER_NAME], pass1.ID
Having Count(*) <= 10
) a
)
AND CREATED_TIMESTAMP <= Convert(int, DATEADD(day, -90, GetDate()))
这如果这些行属于一个用户名超过10个记录和那些行有超过90天前Created_TimeStamp只会删除具有最低ID的行。当然,你应该用SELECT *
来代替DELETE
来确保它给你你想要的东西。此外,我会运行一个Begin Transaction;
,并准备好Rollback Transaction;
,如果它在删除时变酸。
如果我理解正确,您想要删除最近记录超过90天并且其密码不在最近90天内的记录。我不知道为什么你真的需要删除这些。当你查询数据时,你可以施加这些规则,而不是数据本身。然后,如果你喜欢,你可以轻松地将规则更改为120天。
在任何情况下,如果仅为每位用户列举密码,这将会简单得多。但他们不是,MySQL不支持row_number()
。这表明使用变量来枚举结果。由于MySQL的规则,这需要子查询和delete
中的join
。剩下的只是基本条件逻辑:
delete ph
from password_history ph join
(select ph2.*,
(@rn := if(@u = user_name, @rn + 1,
if(@u := user_name, 1, 1)
)
) as seqnum
from password_history ph2 cross join
(select @rn := 0, @u := '') params
order by user_name, created_timestamp desc
) ph2
on ph2.id = ph.id
where ph2.seqnum > 10 and
ph.created_timestamp <= date_sub(curdate(), interval 90 day)
u能解释为什么你有一个UPDATED_TIMESTAMP列 – Drew
你贴PASSWORD_HISTORY表,我猜你是用来存储不同的密码用户有一段时间。你问如何从这个表中删除行,如果它们大于X天,并且不在特定用户的最近10行中? – DiscipleMichael
我不需要更新时间戳...包括习惯的武力;我怀疑我会在代码完成之前放弃。 –