| 删除数据库中无用的帐号:
在查询分析器里依次执行以下语句：
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字段的排序规则都改成数据库默认
|
