代码之家 › 专栏 › 技术社区 › Iain Collins Ryan

MySQL子查询中的IP地址号

subquery mysql sql

Iain Collins Ryan · 技术社区 · 15 年前

我有一个涉及存储在MySQL(MySQL 5.0)中的IPV4地址的子查询问题。

IP地址存储在两个表中,都是网络号格式,例如MySQL的INET\u ATON()输出的格式。第一个表('events')包含许多与它们相关联的IP地址的行,第二个表('network\u providers')包含给定netblock的提供程序信息列表。

event_id (int)
event_name (varchar)
ip_address (unsigned int)

网络提供商

ip_start (unsigned int)
ip_end  (unsigned int)
provider_name (varchar)

为了解决我遇到的问题,我们的目标是创建一个导出:

event_id,event_name,ip_address,provider_name

SELECT provider_name FROM network_providers WHERE INET_ATON('192.168.0.1') >= network_providers.ip_start ORDER BY network_providers.ip_start DESC LIMIT 1

SELECT provider_name FROM network_providers WHERE 3232235521 >= network_providers.ip_start ORDER BY network_providers.ip_start DESC LIMIT 1

也就是说,它返回正确的 提供商名称 对于我查找的任何IP(当然我在查询中并没有真正使用192.168.0.1)。

但是,当以以下方式执行与子查询相同的查询时,它不会产生我期望的结果:

SELECT 
events.event_id,
events.event_name,
    (SELECT provider_name FROM network_providers 
    WHERE events.ip_address >= network_providers.ip_start 
    ORDER BY network_providers.ip_start DESC LIMIT 1) as provider
FROM events

相反,该参数的值不同(不正确) 已返回。超过90%(但奇怪的是不是所有)的值返回到

使用在子查询中,只需回显值即可确认它包含我期望的值,并且子查询可以解析它。更换 events.ip\u地址

我怀疑问题在于MySQL中的子查询有一些基本的和重要的东西我不知道。我以前在MySQL中使用过类似的IP地址,但是以前没有使用子查询来查找它们。

我真的很感激能举个例子来说明我是如何得到我想要的结果的,如果这里有人知道的话,一些关于为什么我所做的不起作用的启示,这样我就可以避免再次犯这个错误。

笔记:

我正在尝试的实际用法要复杂得多(包括连接两个或三个表)。这是一个简化的版本,以避免问题过于复杂化。

另外,我知道我没有在ip上使用中间层\u start&ip_end-这是有意的(DB可能已经过时,在这种情况下,DB中的所有者几乎总是在下一个指定的范围内,在这种情况下,“最佳猜测”是可以的),但是我很感谢任何与问题相关的改进建议。

2 回复 | 直到 15 年前

Ike Walker 15 年前

你应该看看这个帖子:

http://jcole.us/blog/archives/2007/11/24/on-efficiently-geo-referencing-ips-with-maxmind-geoip-and-mysql-gis/

它提供了一些很好的方法,可以在与您非常相似的查询中使用IPs。

您应该尝试的另一件事是使用存储函数而不是子查询。这将简化您的查询,如下所示:

SELECT 
event.id,
event.event_name,
GET_PROVIDER_NAME(event.ip_address) as provider
FROM events

Iain Collins Ryan 15 年前

似乎没有办法通过连接或子查询实现我想要的。

展开 艾克·沃克 的建议,我在MySQL中创建了一个存储函数,如下所示:

DELIMITER //
DROP FUNCTION IF EXISTS get_network_provider //
CREATE FUNCTION get_network_provider(ip_address_number INT) RETURNS VARCHAR(255)
BEGIN
DECLARE network_provider VARCHAR(255);
    SELECT provider_name INTO network_provider FROM network_providers
    WHERE ip_address_number >= network_providers.ip_start
    AND network_providers.provider_name != ""
    ORDER BY provider_name.ip_start DESC LIMIT 1;
RETURN network_provider;
END //

说明:

选中忽略空名称,并使用>=&ORDER BY for ip\u start而不是在ip\u start和ip\u end之间是我正在使用的两个组合的网络提供商数据库的一个特定的伪造方法,这两个数据库都需要以这种方式进行查询。

当调用函数的查询只需要返回几百个结果(尽管可能需要几秒钟)时,这种方法工作得很好。对于返回几千个结果的查询,可能需要2到3分钟。对于具有上万个(或更多)结果的查询,它的速度太慢,无法实际使用。

建议:

这样做的结果是,我需要接受数据结构不适合我的需要。我的一个朋友已经向我指出了这一点,但我当时并不想听到这一点(因为我真的很想使用特定的网络提供商数据库,因为表中的其他键对我很有用,例如地理位置)。

至少您需要重新格式化数据,以便它们可以可靠地与简单的BETWEEN语句一起使用(没有排序,也没有其他比较),这样您就可以将其与子查询(或联接)一起使用—尽管这可能是一个指标,表明任何乱七八糟的数据可能都不那么可靠。