mysql - Geoip 与 MySQL

Question

我有一个包含下表的 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 的所有 session 的时间戳、IP 编号和地理位置数据(国家、纬度、经度)。

我可以轻松地创建一个时间戳和所有 session 的 IP 号报告，其中 auth.success 等于 1:

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)？

Answer 1

当我使用您表中的一些条目测试以下查询时，它起作用了。我在名为 soq 的数据库中创建了表。我在您的创建语句中发现一处语法错误。您的 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);