首页 > 数据库 > 对QQ群关系数据库的数据挖掘

对QQ群关系数据库的数据挖掘

2013年12月11日 Terence 4,889 次阅读 发表评论 阅读评论

      首先声明一点,本人的目的并非是为了去窥探别人的隐私而去做这件事情,而是纯粹以一个数据挖掘的角度来看待它。QQ群关系数据库已经在上个月也就是11月18日被好事者公布在了网络上,立马遭到了网友的疯转,乌云-漏洞报告平台也在第一时间公布了这个漏洞消息。

      该QQ群数据库其实是2011年11月的时候被黑客从一个腾讯漏洞网站上脱裤下来的,里面包含了当时所有QQ号的基本个人信息与所加入群的对应关系以及群的一些基本信息,解压出来后数据库总大小超过了90G,用的是SQL Server 2000,以数据分片的形式存储,共有22个库。虽然其中并不包含密码信息,但可以从这里获得某个QQ号的昵称、年龄、性别和所加入的群的号码和群名称等信息。要知道许多人在加入某些重要群的时候所修改的昵称会是自己的真实姓名,所以无意中会使自己的QQ实名化,然后通过分析所加入的群名称,就基本上能判断该用户的读书工作经历乃至整个关系网了,因为毕竟我们当中的大多数已经越来越离不开QQ,生活中的点点滴滴都有可能有意无意的和QQ扯上了些关系。

      就像网上所说的,为什么11年的数据库到了现在才被公布出来,说明那些第一时间获得数据的黑客已经对数据进行了价值利用,直到利用完后才把它随便扔到了网络上,对于这个观点,我表示同意,可见这次的泄露事件影响有多么的可怕。

      撇开数据泄漏不说,我们把目光转回这个90个G的庞大数据库上,既然木已成舟,对于我们这些后知后觉的人来说,这么多的数据正好可以作为数据挖掘的练手好机会,虽然有悖道义,但只要本着求是的态度,只用于研究,所以在这里,我保证不会公布任何和个人信息有关的数据。

      其实对于这个QQ群数据库能挖掘的数据形式并不多,22个库归结下来就两种表:Group表和QunList表。Group表包含了QQ号的个人基本信息和对应加入的QQ群号,QunList表则包含了群的一些基本信息,这两张表都是以群号的递增顺序排列的,Group表共1100张,QunList表共110张,各自平均分散在11个数据库里,共22个数据库。其中Group表并没有被完全填充,Group1是空表,Group970以后的大部分表也是空的,然后QunList表中的群号比Group表中的要多,我猜测可能这两种表并不是在同一时间被脱裤的。以下是表结构:

Group表

QunList表

      随后的思路就比较简单了,我查询一个QQ号,然后从所有的Group表中进行查询遍历,再是把查询到的群号分别在对应的QunList表中查询,以获得群名称,以下是自己设计的查询结果表:

CREATE TABLE [dbo].[QQNumInfo] (
[ID] int NOT NULL IDENTITY(1,1) ,
[QQNum] int NOT NULL ,
[Nick] varchar(20) COLLATE Chinese_PRC_CI_AS NULL ,
[Age] int NULL ,
[Gender] int NULL ,
[QunNum] int NULL ,
[QunTitle] varchar(22) COLLATE Chinese_PRC_CI_AS NULL ,
[NotExist] char(1) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_QQInfo] PRIMARY KEY ([ID])
)
ON [PRIMARY]
GO
 
CREATE INDEX [INDEX_QQNum] ON [dbo].[QQNumInfo]
([QQNum] ASC)
ON [PRIMARY]
GO
 
CREATE INDEX [INDEX_QunNum] ON [dbo].[QQNumInfo]
([QunNum] ASC)
ON [PRIMARY]
GO

      整个查询逻辑采用存储过程语言t-sql来写,传入QQNum这个参数,首先查找结果表中是否存在记录,如果有则直接返回结果集,NotExist列是用来标记经过查找后返回为空集的QQ号,以便在下次查找的时候能立即返回为空;如果QQ号在结果表中不存在,则进入一系列的查询逻辑并最终将结果存入结果表中并返回结果集。以下是存储过程代码:

ALTER PROCEDURE [dbo].[LookupQQNum] @QQ INT
AS
BEGIN
	SET NOCOUNT ON --防止在jdbc里无法返回结果集
	CREATE TABLE #tmp (
		[ID] INT NOT NULL IDENTITY(1,1) ,
		[QQNum] INT NOT NULL ,
		[Nick] VARCHAR(20) COLLATE Chinese_PRC_CI_AS NULL ,
		[Age] INT NULL ,
		[Gender] INT NULL ,
		[QunNum] INT NULL ,
		[QunTitle] VARCHAR(22) COLLATE Chinese_PRC_CI_AS NULL ,
		[NotExist] CHAR(1) COLLATE Chinese_PRC_CI_AS NULL
	)
	INSERT INTO #tmp SELECT QQNum,Nick,Age,Gender,QunNum,QunTitle,NotExist FROM QQNumInfo WHERE QQNum=@QQ
	IF exists(SELECT QQNum FROM #tmp)
	BEGIN
		DECLARE @IsNotExist CHAR(1)
		SELECT TOP 1 @IsNotExist=NotExist FROM #tmp
		IF @IsNotExist IS null
			SELECT ID,QQNum,Nick,Age,Gender,QunNum,QunTitle FROM #tmp
		ELSE
			SELECT ID,QQNum,NotExist FROM #tmp
	END
	ELSE
	BEGIN
		DECLARE @table_num INT
		DECLARE @database_num INT
		SET @table_num=2
		WHILE @table_num<=970
		BEGIN
			SET @database_num=(@table_num-1)/100+1
			EXEC (
				'insert into #tmp(QQNum,Nick,Age,Gender,QunNum) select QQNum,Nick,Age,Gender,QunNum from GroupData'
				+ @database_num + '.dbo.Group' + @table_num + ' where QQNum=' + @QQ
			)
			SET @table_num=@table_num+1
		END
		INSERT INTO #tmp(QQNum,Nick,Age,Gender,QunNum) SELECT QQNum,Nick,Age,Gender,QunNum FROM GroupData11.dbo.Group1001 WHERE QQNum=@QQ
		INSERT INTO #tmp(QQNum,Nick,Age,Gender,QunNum) SELECT QQNum,Nick,Age,Gender,QunNum FROM GroupData11.dbo.Group1002 WHERE QQNum=@QQ
		INSERT INTO #tmp(QQNum,Nick,Age,Gender,QunNum) SELECT QQNum,Nick,Age,Gender,QunNum FROM GroupData11.dbo.Group1003 WHERE QQNum=@QQ
		IF not exists(SELECT QQNum FROM #tmp)
		BEGIN
			INSERT INTO #tmp(QQNum,NotExist) VALUES(@QQ,'1')
			INSERT INTO QQNumInfo(QQNum,NotExist) VALUES(@QQ,'1')
			SELECT ID,QQNum,NotExist FROM #tmp
		END
		ELSE
		BEGIN
			DECLARE cursor_QunNum CURSOR FOR SELECT QunNum FROM #tmp
			DECLARE @temp INT
			DECLARE @QunList_Num INT
			DECLARE @QunInfo_Num INT
			OPEN cursor_QunNum
			FETCH NEXT FROM cursor_QunNum INTO @temp
			WHILE @@FETCH_STATUS=0
			BEGIN
				SET @QunList_Num=(@temp-1)/1000000+1
				SET @QunInfo_Num=(@QunList_Num-1)/10+1
				EXEC (
					'update #tmp set QunTitle=(select Title from QunInfo' + @QunInfo_Num + '.dbo.QunList'
					+ @QunList_Num + ' where QunNum=' + @temp + ') where QunNum=' + @temp
				)
				FETCH NEXT FROM cursor_QunNum INTO @temp
			END
			CLOSE cursor_QunNum
			INSERT INTO QQNumInfo(QQNum,Nick,Age,Gender,QunNum,QunTitle) SELECT QQNum,Nick,Age,Gender,QunNum,QunTitle FROM #tmp
			SELECT ID,QQNum,Nick,Age,Gender,QunNum,QunTitle FROM #tmp
		END
	END
END

      存储过程代码量不多,就70行的样子,但毕竟面对的是90个G的海量数据,经过几次测试发现查询一个QQ号平均耗时29分钟,我的测试机配置是台式一代i3双核超线程CPU+2G内存+500G SATA2硬盘,采用的数据库是SQL Server 2008 R2 SP2。所以也只能作为测试用途,这么长的查询时间是谁也受不了的 :razz:

      最后上一张查询结果返回集合的截图吧,当然是经过打码的:





分类: 数据库 标签: , ,
  1. 点点
    2017年8月22日16:02 | #1
    Google Chrome 55.0.2883.87 Google Chrome 55.0.2883.87 Windows 7 x64 Edition Windows 7 x64 Edition

    亲爱的楼主你好,不知道楼主还能不能看到,资料可以分享给我一下吗,我想试着做一下数据库可视化,给天马发啦 :mrgreen:

    [回复]

    点点

    2017年8月22日16:04
    Google Chrome 55.0.2883.87 Google Chrome 55.0.2883.87 Windows 7 x64 Edition Windows 7 x64 Edition

    @点点, 564857289@qq.com邮箱忘写了 :roll:

    [回复]

  2. 黄花菜
    2017年3月26日17:10 | #2
    Google Chrome 51.0.2704.79 Google Chrome 51.0.2704.79 Windows Windows

    大神,能发我一份吗?谢谢啊:邮箱:120131615@qq.com

    [回复]

  3. 阳光
    2017年3月22日16:25 | #3
    uBrowser 5.7.15319.5 uBrowser 5.7.15319.5 Windows 8.1 x64 Edition Windows 8.1 x64 Edition

    :smile: 亲,这个数据包可以给我发一份吗?谢谢,QQ:439379

    [回复]

  4. 学习路上
    2017年3月7日15:53 | #4
    Google Chrome 45.0.2454.101 Google Chrome 45.0.2454.101 Windows 7 Windows 7

    我把你的这个程序完全复制到sql sever2008上了,怎么输入要查询的QQ号

    [回复]

  5. wang
    2017年2月15日16:12 | #5
    Google Chrome 45.0.2454.101 Google Chrome 45.0.2454.101 Windows XP Windows XP

    楼主可以发份到我邮箱或通过百度网盘发给我?wang3222681@qq.com/wang3222681

    [回复]

  6. marchlyp
    2017年1月23日22:13 | #6
    Sogou Explorer Sogou Explorer Windows 7 x64 Edition Windows 7 x64 Edition

    楼主你好,我是一名计算机专业硕士生,研究方向是数据挖掘。目前在做一个项目,需要爬取qq空间的数据。但是在爬空间数据时需要知道qq号码才能爬到数据。而且我还特别需要这份数据中的好友关系。我发现这份数据对我爬取qq空间数据和分析用户行为真的很重要。如果你还保留着这份数据的话,希望能够分享这份数据集。本人纯粹是出于研究使用,如果信不过我的话,可以把数据过滤一下,去掉昵称、群的名称等敏感信息。如果可以分享的话,本人万分感激!!!(我的邮箱:778176007@qq.com)。期待你能够与我联系!!

    [回复]

    Terence

    2017年1月24日10:26
    Google Chrome WP Google Chrome WP Windows 7 x64 Edition Windows 7 x64 Edition

    @marchlyp, 加你邮箱为账号的百度网盘好友了,你通过下,我发给你

    [回复]

    shineba

    2017年7月27日11:07
    Firefox 54.0 Firefox 54.0 Windows Windows

    楼主,我们是一家才成立的创业公司,做数据挖掘和大数据平台搭建的,先想做一些数据挖掘demo,希望楼主能赏下这份数据,万分感谢。百度网盘id:shinewardh

    [回复]

  7. 沙隆巴斯
    2014年12月13日15:15 | #7
    Sogou Explorer Sogou Explorer Windows 7 x64 Edition Windows 7 x64 Edition

    你这样挖掘, :mrgreen: :mrgreen: :mrgreen: 时间够长的

    [回复]

  8. 2014年1月31日18:40 | #8
    Google Chrome 33.0.1750.27 Google Chrome 33.0.1750.27 Windows 7 x64 Edition Windows 7 x64 Edition

    :smile: 这玩意已经被人拿来赚钱了~~

    [回复]

    Terence

    2014年2月1日01:20
    Safari Safari iPad iOS 6.1.2 iPad iOS 6.1.2

    太缺德

    [回复]