PostgreSQL IP地址：通过inet数据搜索正确的结果？ [英] PostgreSQL IP address: Search the right result by inet data?

问题描述

我在PostgreSQL上遇到问题，询问给定IP地址（ inet 类型）是否在数据库中搜索正确的结果。我将逐步提供我的工作。

I have a problem on PostgreSQL when asking if a given IP address (inet type) search the right result in the database. I will provide step by step what I'm doing.

CREATE TABLE public.test (
   ip inet,
   a character varying
);
INSERT INTO public.test (ip, a) VALUES
    ('1111:0000:0101:000A:0002:0003:0004:0005', 'admin1'),
    ('1111:0001:0101:000A:0002:0003:0004:0005', 'admin2'),
    ('1111:0011:0101:000A:0002:0003:0004:0005', 'admin3'),
    ('1111:0111:0101:000A:0002:0003:0004:0005', 'admin4'),
    ('1111:1111:0101:000A:0002:0003:0004:0005', 'admin5');

然后我要在 ip 中搜索'1111：0'，应该可以找到结果

Then I want search ip by '1111:0', which should find the result

1111:0000:0101:000A:0002:0003:0004:0005
1111:0001:0101:000A:0002:0003:0004:0005
1111:0011:0101:000A:0002:0003:0004:0005
1111:0111:0101:000A:0002:0003:0004:0005

推荐答案

如果要搜索 inet 地址的某些文本表示形式（例如f.ex. Like ），您会遇到困难；因为 inet 输出是规范的。例：

If you want to search for some text representation of inet addresses (with f.ex. LIKE), you'll have a hard time; because inet output is canonized. F.ex.:

input                                   | output 
1111:0000:0000:0000:0000:0000:0000:0005 | 1111::5/128
1111:0001:0101:000A:0002:0003:0004:0005 | 1111:1:101:a:2:3:4:5/128

因此，前导零不见了并且只有零的最大块被 :: 替换（在IPv6中完全有效）。

So, leading zeros are gone & and the largest block of only zeros are replaced with :: (which is completely valid in IPv6).

但是，如果要查找 inet 地址，其中有20个前导位，例如 1111：0 ，您可以使用子网。

But, if you want to find inet addresses, which have 20 leading bits like 1111:0, you can use subnets.

对于 1111 ：0 ，您实际上是在寻找 1111 :: // 20 （带有包含运算符：>> ）：

For 1111:0, you are really looking for the child (networks / ) hosts of 1111::/20 (with the contains operator: >>):

select addr,
       inet '1111::/20' >> addr  "is within '1111::/20'"
from   (values (inet '1111:0000:0000:0000:0000:0000:0000:0005'),
               (inet '1111:0001:0101:000A:0002:0003:0004:0005'),
               (inet '1111:0011:0101:000A:0002:0003:0004:0005'),
               (inet '1111:0111:0101:000A:0002:0003:0004:0005'),
               (inet '1111:1111:0101:000A:0002:0003:0004:0005'),
               (inet '1111:F111:0101:0000:0000:0000:0000:0005')) v(addr)

将产生：

addr                        | is within '1111::/20'
1111::5/128                 | t
1111:1:101:a:2:3:4:5/128    | t
1111:11:101:a:2:3:4:5/128   | t
1111:111:101:a:2:3:4:5/128  | t
1111:1111:101:a:2:3:4:5/128 | f
1111:f111:101::5/128        | f

http://rextester.com/ZFFFK28291

这篇关于PostgreSQL IP地址：通过inet数据搜索正确的结果？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！