blocks|key|1090116|text|您可以使用INET_ATON获取要比较的IP的数值。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1090117|SELECT+country+FROM+table
WHERE+INET_ATON('25.195.32.0')+BETWEEN+INET_ATON(from)+AND+INET_ATON(to)|code-block|syntax|javascript|1090118|inet-aton|1090119|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_inet-aton^0|5|9|0|0|0|9|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|X|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|Y|8|@]|D|@$9|Z|A|10|1|11]]|E|$]]|$1|M|3|-4|5|6|7|12|8|@]|D|@]|E|$]]]|N|$O|$5|P|Q|R|E|$S|T]]]]

You can use <code>INET_ATON</code> to get a numeric value of the IPs to compare.

<pre><code>SELECT country FROM table
WHERE INET_ATON('25.195.32.0') BETWEEN INET_ATON(from) AND INET_ATON(to)
</code></pre>

<a href="http://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_inet-aton" rel="nofollow">http://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_inet-aton</a>

blocks|key|4075137|text|您可以通过使用IN()或OR找到使用AND尝试条件|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4075138|WHERE+from+IN('your_ip')+OR+to+IN('your_ip')|code-block|syntax|javascript|4075139|entityMap^0|7|4|C|2|I|3|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|U|8|@]|D|@]|E|$]]]|L|$]]

You can find using <code>IN()</code> try condition according you use <code>AND</code> or <code>OR</code>

<pre><code>WHERE from IN('your_ip') OR to IN('your_ip')
</code></pre>

blocks|key|4075166|text|将IP转换为整数，然后比较整数是简单的。MySQL提供了这样做的inet_aton函数。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4075167|select+Country+from+table_name+where+inet_aton($ip)+between+inet_aton(`from`)+and+inet_aton(`ip`);|code-block|syntax|javascript|4075168|为了提高性能，应该将列from、to转换为整数。例如：添加到列from_n，to_n。然后您的SQL将如下所示：|4075169|select+Country+from+table_name+where+inet_aton($ip)+between+`from_n`+and+`to_n`|4075170|如果不使用MySQL，则应该首先将$ip转换为整数$ip_n+(使用类似于Python的socket.inet_aton)，然后将inet_aton($ip)替换为$ip_n。|4075171|entityMap^0|W|9|0|0|B|4|G|2|V|6|12|4|0|0|H|3|18|G|1T|E|2A|5|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|W|8|@$9|X|A|Y|B|C]|$9|Z|A|10|B|C]|$9|11|A|12|B|C]|$9|13|A|14|B|C]]|D|@]|E|$]]|$1|M|3|N|5|H|7|15|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|16|8|@$9|17|A|18|B|C]|$9|19|A|1A|B|C]|$9|1B|A|1C|B|C]|$9|1D|A|1E|B|C]]|D|@]|E|$]]|$1|Q|3|-4|5|6|7|1F|8|@]|D|@]|E|$]]]|R|$]]

Transform IP into integer, then compare integer is simple. MySQL offers the <code>inet_aton</code> function to do so.

<pre><code>select Country from table_name where inet_aton($ip) between inet_aton(`from`) and inet_aton(`ip`);
</code></pre>

For performance, you should transform column <code>from</code>, <code>to</code> to integer manully. eg. add to columns <code>from_n</code>, <code>to_n</code>. then your SQL will be like this:

<pre><code>select Country from table_name where inet_aton($ip) between `from_n` and `to_n`
</code></pre>

If you are not using MySQL, you should transform <code>$ip</code> into integer $ip_n first (using something like Python's <code>socket.inet_aton</code>), then replace <code>inet_aton($ip)</code> with <code>$ip_n</code>.

blocks|key|1089793|text|select+*+from+db.table|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1089794|然后在php中(假设您已经运行了任务和存储的结果)：|unstyled|1089795|$lookupIP;

for($i=0;$i<$db->getRowCount();$i%2B%2B)+{
++++$partsFROM+=+explode(".",+$FROM);
++++$partsTO+=+explode(".",+$TO);
++++$partsLOOKUP+=+explode(".",$lookupIP);
++++if(
++++++++$partsLOOKUP[0]+>=+$partsFROM[0]+&&+$partsLOOKUP[0]+<=+$partsTO[0]++
+++++&&+$partsLOOKUP[1]+>=+$partsFROM[1]+&&+$partsLOOKUP[1]+<=+$partsTO[1]++
+++++&&+etc..
++++)+return+$Country
}|1089796|不是特别有效率或优雅，但你有这个想法。|1089797|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|N|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$B|C]]|$1|I|3|J|5|F|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|F|7|Q|8|@]|9|@]|A|$]]]|L|$]]

<pre><code>select * from db.table
</code></pre>

Then in php (assuming you've run the quest and stored results):

<pre><code>$lookupIP;

for($i=0;$i&lt;$db-&gt;getRowCount();$i++) {
 $partsFROM = explode(".", $FROM);
 $partsTO = explode(".", $TO);
 $partsLOOKUP = explode(".",$lookupIP);
 if(
 $partsLOOKUP[0] &gt;= $partsFROM[0] &amp;&amp; $partsLOOKUP[0] &lt;= $partsTO[0] 
 &amp;&amp; $partsLOOKUP[1] &gt;= $partsFROM[1] &amp;&amp; $partsLOOKUP[1] &lt;= $partsTO[1] 
 &amp;&amp; etc..
 ) return $Country
}
</code></pre>

Not particularly efficient or elegant but you get the idea.

blocks|key|692990|text|正如我从问题中解释的那样，您可以使用一些掩码，获取基本ip地址，然后进行比较。要使用掩码并获得基本ip，您需要了解不同类(A、B、C、D)在寻址中使用的内容。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|692991|请参考此链接http://www.subnet-calculator.com/|offset|length|692992|entityMap|0|LINK|mutability|MUTABLE|url|http://www.subnet-calculator.com/^0|0|6|X|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

As i interpret from the question, you can use some mask and get the base ip address and then compare it. To use the mask and get base ip, you need to learn something about different classes (A, B, C, D) used from addressing.

Refer this link <a href="http://www.subnet-calculator.com/" rel="nofollow">http://www.subnet-calculator.com/</a>

blocks|key|693005|text|INET_ATON给出了IPV4地址上所需的解决方案。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|693006|除此之外，您还可以尝试在HEX版本的IP之间进行比较。|693007|SELECT++HEX(+input_ip_value+)+
BETWEEN+HEX(+from_ip_column+)+
++++AND+HEX(+to_ip_column+)|code-block|syntax|javascript|693008|示例：|BOLD|693009|mysql>+select+@i:=hex('25.195.32.0'),
++++->+@f:=hex('25.0.0.1'),
++++->+@t:=hex('25.255.255.255'),
++++->+@i+between+@f+and+@t+is_between;
%2B------------------------%2B---------------------%2B------------------------------%2B------------%2B
%7C+@i:=hex('25.195.32.0')+%7C+@f:=hex('25.0.0.1')+%7C+@t:=hex('25.255.255.255')++++%7C+is_between+%7C
%2B------------------------%2B---------------------%2B------------------------------%2B------------%2B
%7C+32352E3139352E33322E30+%7C+32352E302E302E31++++%7C+32352E3235352E3235352E323535+%7C++++++++++1+%7C
%2B------------------------%2B---------------------%2B------------------------------%2B------------%2B|693010|entityMap^0|0|9|C|4|0|C|3|0|0|0|2|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@$9|U|A|V|B|C]|$9|W|A|X|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|Y|8|@$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|H|3|I|5|J|7|11|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|12|8|@$9|13|A|14|B|O]]|D|@]|E|$]]|$1|P|3|Q|5|J|7|15|8|@]|D|@]|E|$K|L]]|$1|R|3|-4|5|6|7|16|8|@]|D|@]|E|$]]]|S|$]]

<code>INET_ATON</code> gives desired solution on <code>IPV4</code> addresses. 

Apart from that, you can also try <code>HEX</code> version of the IP to compare in between.

<pre><code>SELECT HEX( input_ip_value ) 
BETWEEN HEX( from_ip_column ) 
 AND HEX( to_ip_column )
</code></pre>

Example: 

<pre><code>mysql&gt; select @i:=hex('25.195.32.0'),
 -&gt; @f:=hex('25.0.0.1'),
 -&gt; @t:=hex('25.255.255.255'),
 -&gt; @i between @f and @t is_between;
+------------------------+---------------------+------------------------------+------------+
| @i:=hex('25.195.32.0') | @f:=hex('25.0.0.1') | @t:=hex('25.255.255.255') | is_between |
+------------------------+---------------------+------------------------------+------------+
| 32352E3139352E33322E30 | 32352E302E302E31 | 32352E3235352E3235352E323535 | 1 |
+------------------------+---------------------+------------------------------+------------+
</code></pre>

blocks|key|1090201|text|选择一个范围：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1090202|SELECT+Country+FROM+table_name+WHERE+ip_address+>=+from+AND+to+<=+ip_address|code-block|syntax|javascript|1090203|如果您可以使用脚本(例如php)，您可以选择整个表并为IP制作一个掩码。类似于：|1090204|我们可以知道属于192.168.129.3/18+ip的网络地址如下：
ip_en_binario+=+decbin+(ip2long+(“192.168.129.3”)；mascara_en_binario+=+decbin+(ip2long+("255.255.192.0"))；resultado_en_binario+$=$+mascara_en_binario+ip_en_binario；long2ip小姐($+resultado_en_binario)；
结果返回给我们这个脚本是192.168.128.0，它是IP所属的网络地址192.168.129.3/18。
|blockquote|1090205|1090206|然后，您可以使用"from“和"to”字段对您的网络进行“选择”。|1090207|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|K|7|T|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

Select a range:

<pre><code>SELECT Country FROM table_name WHERE ip_address &gt;= from AND to &lt;= ip_address
</code></pre>

If you can use a script (eg php) you could select the entire table and make a mask to the IP. Something like: 

<blockquote>
 We can know what belongs to the network address 192.168.129.3/18 ip as
 follows: 

<pre><code>ip_en_binario = decbin (ip2long ("192.168.129.3")); 
mascara_en_binario = decbin (ip2long ("255.255.192.0")); 
resultado_en_binario $ = $ &amp; $ mascara_en_binario ip_en_binario; 
miss long2ip (bindec ($ resultado_en_binario)); 
</code></pre>
 
 The result returned us this script is 192.168.128.0, which is the network address to which the IP belongs 192.168.129.3/18.
</blockquote>

Then, you can do a 'SELECT' of your network with "from" and "to" fields.

blocks|key|692863|text|使用此查询|type|unstyled|depth|inlineStyleRanges|entityRanges|data|692864|select+Country+from+table_name+where+from=to;|code-block|syntax|javascript|692865|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

use this query 

<pre><code>select Country from table_name where from=to;
</code></pre>

I am trying to solve a problem that could compare 2 columns in a table.
the table is as follows

<pre><code>------------------------------------------
| from | to | Country |
------------------------------------------
| 25.0.0.1 | 25.255.255.255 | denmark |
------------------------------------------
| 68.0.0.1 | 68.255.255.255 | USA |
</code></pre>

<hr>

My problem is i have a ip of <code>25.195.32.0</code> and i want to compare this to the <code>from</code> and <code>to</code> column and return the <code>country name</code>.

Ip address compare in database

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我试图解决一个问题，可以比较两列在一个表。表如下------------------------------------------|  from    |    to           |  Country  |------------------------------------------| 25.0.0.1 | 25.255.255.255  |  denmark  |--------

问数据库中Ip地址比较
EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问数据库中Ip地址比较EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问数据库中Ip地址比较
EN