删除数据库中无用的帐号

删除数据库中无用的帐号:

在查询分析器里依次执行以下语句:
1、delete from memb_info
where memb___id not in (select DISTINCT accountid from character where clevel>=100) ;这句是删除帐号
2、delete from warehouse
where accountid not in (select DISTINCT accountid from character where clevel>=100) ;这句是删除仓库
3、delete from character where accountid not in (select memb___id from memb_info) ;这句是删除人物

以上语句执行顺序不能错,如果出现以下错误
服务器: 消息 446,级别 16,状态 9,行 1
无法解决 equal to *作的排序规则冲突。
请点设计warehouse,character,memb_info表中accountid,accountid,memb___id字段将排序规则改成数据库默认。
关于删除数据库中无用的帐号(增加未上线检测)
很多朋友私服都开了有一段时间了,里面有许多无用的帐号其实只要有一点sql语法基础很容易就可以把多余的帐号删除的,以下是删除没有100级人物的帐号的方法,用之前请记得备份:
在查询分析器里依次执行以下语句:
1、delete from memb_info
where memb___id not in (select DISTINCT accountid from character where clevel>=100) ;这句是删除帐号
2、delete from warehouse
where accountid not in (select DISTINCT accountid from character where clevel>=100) ;这句是删除仓库
3、delete from character where accountid not in (select memb___id from memb_info) ;这句是删除人物

以上语句执行顺序不能错,如果出现以下错误
服务器: 消息 446,级别 16,状态 9,行 1
无法解决 equal to *作的排序规则冲突。
请点设计warehouse,character,memb_info表中accountid,accountid,memb___id字段将排序规则改成数据库默认。

以下是删除30天未上线的语句
1、delete from memb_info
where memb___id in (select memb___id from memb_stat where getdate()-isnull(connecttm,'1900/01/01')>30 and getdate()-isnull(disconnecttm,'1900/01/01')>30)
;这是删除帐号
2、delete from warehouse
where accountid in (select memb___id from memb_stat where getdate()-isnull(connecttm,'1900/01/01')>30 and getdate()-isnull(disconnecttm,'1900/01/01')>30
) ;这句是删除仓库
3、delete from character where accountid in (select memb___id from memb_stat where getdate()-isnull(connecttm,'1900/01/01')>30 and getdate()-isnull(disconnecttm,'1900/01/01')>30 ) ;这句是删除人物
4、delete from memb_info where memb___id in (select memb___id from memb_stat) ;这句是删除申请帐号后从未上过线的帐号
关于第4点,涉及到一个最近申请的帐号如果没上过线也会被删除的问题
解决方法:如果你的注册系统将注册日期写进了memb_info表中你可以在第4句后面加上以下语句: and getdate()-记录注册日期的字段>30

如果怕出问题可以把delete先用select * 替换查询后看看记录是不是你想要的

1.删除无角色帐号:
delete from accountcharacter
where (gameidc is null)

2.删除等级为1,从未登陆过的角色:
delete from character
where (clevel = '1') and (money = '0')

3.删除空仓库('500000'是注册送的钱,我送了50万):
delete from warehouse
where (items is null) and (money = '500000')

以上测试通过!

关于删除数据库中无用的帐号(更新为存储过程)
很多朋友私服都开了有一段时间了,里面有许多无用的帐号其实只要有一点sql语法基础很容易就可以把多余的帐号删除的,以下是删除没有100级人物的帐号的方法,用之前请记得备份:
CREATE PROCEDURE del_acc
@level smallint
AS
begin
select memb___id into #tmptable from memb_info
where memb___id not in (select DISTINCT accountid from character where clevel>=@level)
delete from memb_info
where memb___id in (select memb___id from #tmptable)
delete from accountcharacter
where id in (select memb___id from #tmptable)
delete from guild where g_name in (select name from character where accountid in (select * from

#tmptable))
delete from guildmember where g_name in (select name from character where accountid in (select * from

#tmptable))
delete from vi_curr_info where memb___id in (select * from #tmptable)
delete from warehouse
where accountid in (select memb___id from #tmptable)
delete from character where accountid in (select memb___id from #tmptable)
drop table #tmptable
end
GO
CREATE PROCEDURE del_date_acc
@level smallint
AS
begin
select memb___id into #tmptable from memb_stat where getdate()-isnull(connecttm,'1900/01/01')>@level and getdate()-isnull(disconnecttm,'1900/01/01')>@level
delete from memb_info
where memb___id in (select memb___id from #tmptable)
delete from accountcharacter
where id in (select memb___id from #tmptable)
delete from guild where g_name in (select name from character where accountid in (select * from

#tmptable))
delete from guildmember where g_name in (select name from character where accountid in (select * from

#tmptable))
delete from vi_curr_info where memb___id in (select * from #tmptable)
delete from warehouse
where accountid in (select memb___id from #tmptable)
delete from character where accountid in (select memb___id from #tmptable)
drop table #tmptable
end
GO
执行
exec del_acc 100 // 100是等级
exec del_date_acc 10 //10是10天未上线,
参数可以自己改
如果怕有错,可以将里面的delete全改成select * 自己看结果再决定删不删
如出现排序规则冲突
将AccountCharacter,Character,Guild,GuildMember,MEMB_INFO,VI_CURR_INFO,warehouse中Id,AccountID,G_Name,name,memb___id,memb___id,AccountID字段的排序规则都改成数据库默认


 

请大家切记本站:奇迹私服 HTTP://www.218mu.com

 

◆ 更多的技术资料正在整理中,本决将努力的不断更新! ◆
站长QQ: 67254354[ 530免费私服发布网 版权所有 ]
Copyright @ 2007-2008 www.218mu.com 奇迹私服 All rights reserved.

免责声明:218mu.Com 新开奇迹私服 登载之所有信息及资源均出于传递更多信息之目的,并不意味着本站赞同其观点或证实其描述.本站所有信息均为免费发布,并非赢利及商业目的.