问题描述
我在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).
但是,如果要查找,其中有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
这篇关于PostgreSQL IP地址:通过inet数据搜索正确的结果?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!