文章/答案/技术大牛

发布

问使用MySQL的Geoip
EN

Stack Overflow用户

提问于 2017-07-09 00:04:43

回答 1查看 997关注 0票数 1

我有一个包含以下表的MySQL数据库：

CREATE TABLE IF NOT EXISTS `auth` (
  `id` int(11) NOT NULL auto_increment,
  `session` char(32) NOT NULL,
  `success` tinyint(1) NOT NULL,
  `username` varchar(100) NOT NULL,
  `password` varchar(100) NOT NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY  (`id`)
);

CREATE TABLE IF NOT EXISTS `sessions` (
  `session` char(32) NOT NULL,
  `starttime` datetime NOT NULL,
  `endtime` datetime default NULL,
  `ip` varchar(15) NOT NULL default '',
  PRIMARY KEY  (`id`)
);

CREATE TABLE `geoip_blocks` (
  `gbl_block_start` int(10) unsigned NOT NULL,
  `gbl_block_end` int(10) unsigned NOT NULL,
  `gbl_glc_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`gbl_block_start`,`gbl_block_end`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `geoip_locations` (
  `glc_id` int(10) unsigned NOT NULL,
  `glc_country` char(2) NOT NULL,
  `glc_region` varchar(2) NOT NULL,
  `glc_city` varchar(64) NOT NULL,
  `glc_zip` varchar(16) NOT NULL,
  `glc_latitude` decimal(7,4) NOT NULL,
  `glc_longitude` decimal(7,4) NOT NULL,
  PRIMARY KEY (`glc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

其中geoip_blocks和geoip_locations是从MaxMind的免费遗留地理位置数据库导入的。

我想创建一个报告，其中包含auth.success等于1的所有会话的时间戳、IP编号和地理位置数据(国家、纬度、经度)。

我可以很容易地创建auth.success等于1的所有会话的时间戳和IP编号的报告：

SELECT
  UNIX_TIMESTAMP(auth.timestamp) as time_sec,
  sessions.ip as ip
FROM auth
  INNER JOIN sessions ON auth.session = sessions.session
WHERE auth.success = 1

给定单个IP编号ip，我可以获取它的地理位置数据：

SELECT
  geoip_locations.glc_country as country,
  geoip_locations.glc_latitude as lat,
  geoip_locations.glc_longitude as lon
FROM geoip_blocks INNER JOIN geoip_locations
  ON geoip_locations.glc_id = geoip_blocks.gbl_glc_id
WHERE gbl_block_start <= INET_ATON(ip)
ORDER BY gbl_block_start DESC
LIMIT 1

但是，我应该使用哪种查询来获得我需要的报告类型(对于上面第一个查询将生成的所有IP)？

mysql

geoip

回答 1

Stack Overflow用户

发布于 2017-08-11 05:31:48

当我使用您的表中的一些条目对以下查询进行测试时，它可以正常工作。我在名为soq的数据库中创建了这些表。我在create语句中发现了一个语法错误。您的sessions表主键应该是session而不是id。我将尝试编辑您的问题以解决此问题。

SELECT UNIX_TIMESTAMP(auth.timestamp) as time_sec,
  auth.timestamp,
  sessions.ip as ip,
  geoip_locations.glc_country as country,
  geoip_locations.glc_latitude as lat,
  geoip_locations.glc_longitude as lon
FROM soq.geoip_blocks JOIN soq.geoip_locations ON (geoip_locations.glc_id = geoip_blocks.gbl_glc_id)
  LEFT JOIN (soq.auth JOIN soq.sessions ON auth.session = sessions.session) -- nested join
  ON (gbl_block_start <= INET_ATON(ip) AND gbl_block_end >= INET_ATON(ip))
  WHERE success = 1
ORDER BY gbl_block_start DESC;

以下是查询结果示例：

# time_sec, timestamp, ip, country, lat, lon
'1502385000', '2017-08-10 13:10:00', '5.82.243.128', 'SA', '25.0000', '45.0000'
'1502401200', '2017-08-10 17:40:00', '1.40.42.8', 'AU', '-27.0000', '133.0000'
'1502393400', '2017-08-10 15:30:00', '1.40.42.255', 'AU', '-27.0000', '133.0000'

下面是我使用的一些测试数据：

-- AU loc_id = 17   19409408 = 1.40.42.0        19409663 = 1.40.42.255
-- SA loc_id - 186  89322240 = 5.82.243.0       89325567 = 5.82.255.255
INSERT INTO soq.sessions (`session`, `starttime`, `endtime`, `ip`) VALUES('userSession1',NOW(), ADDTIME(NOW(),'01:02:00'),'1.40.42.255');
INSERT INTO soq.sessions (`session`, `starttime`, `endtime`, `ip`) VALUES('userSession2',ADDTIME(NOW(),'02:03:00'), ADDTIME(NOW(),'03:04:00'),'1.40.42.8');
INSERT INTO soq.sessions (`session`, `starttime`, `endtime`, `ip`) VALUES('userSession3','2017-08-10 13:00:00', '2017-08-10 16:37:00','5.82.243.128');

INSERT INTO soq.auth (`session`, `success`, `username`, `password`, `timestamp`) VALUES('userSession1',1,'user1','pwd1','2017-08-10 15:30:00');
INSERT INTO soq.auth (`session`, `success`, `username`, `password`, `timestamp`) VALUES('userSession2',1,'user2','pwd2','2017-08-10 17:40:00');
INSERT INTO soq.auth (`session`, `success`, `username`, `password`, `timestamp`) VALUES('userSession3',1,'user3','pwd3','2017-08-10 13:10:00');
INSERT INTO soq.auth (`session`, `success`, `username`, `password`, `timestamp`) VALUES('userSession3',0,'user3','pwd3','2017-08-10 13:00:00');

-- 19409408 19409663    17
-- 89322240 89325567    186
INSERT INTO soq.geoip_blocks VALUES(19409408,19409663,17);
INSERT INTO soq.geoip_blocks VALUES(89322240,89325567,186);

-- 17   AU              -27 133
-- 186  SA              25  45
INSERT INTO soq.geoip_locations VALUES (17,'AU','','','',-27,133);
INSERT INTO soq.geoip_locations VALUES (186,'SA','','','',25,45);

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/44988119

复制

相似问题

问使用MySQL的Geoip
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用MySQL的GeoipEN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用MySQL的Geoip
EN