在Oracle中表示IPv4/IPv6地址 [英] Representing IPv4/IPv6 addresses in Oracle
问题描述
在Oracle中，什么是表示网络地址的合适数据类型或技术?这些地址可以是IPv4或IPv6?
In Oracle, what is the appropriate data type or technique for representing network addresses, which addresses may be IPv4 or IPv6?
背景:我正在转换一个记录网络活动的表，该表是使用PostgreSQL
Background: I'm converting a table recording network activity, built using the PostgreSQL inet data type to hold both v4 and v6 addresses in the same table.
但是，没有行同时包含v4和v6地址. (也就是说，记录来自机器的v4堆栈或机器的v6堆栈.)
No row contains both v4 and v6 addresses, however. (That is, a record is either from a machine's v4 stack, or a machine's v6 stack.)
推荐答案
在Oracle中，什么是合适的 数据类型或技术 代表网络地址， 地址可以是IPv4或IPv6
In Oracle, what is the appropriate data type or technique for representing network addresses, which addresses may be IPv4 or IPv6
有两种方法:
- 仅存储.
- 存储常规表示形式
仅用于存储. IPV4地址应为整数(32位足够).对于IP V6，128位将使用INTEGER(类似于Number(38)).当然，这就是存储.这种方法认为表示形式是应用程序的问题.
For storing only. An IPV4 address should be an integer (32bits are enough). For IP V6, 128 bits, INTEGER (which is similar to Number(38)) will do. Of course, that's storing. That approach takes the view that the representation is a matter for the application.
如果采用相反的策略来存储常规表示，则需要确保IP V4和IPV6地址仅具有一种常规(字符串)表示.它以ipV4闻名.至于IPV6，也有一种标准格式.
If one take the opposite strategy, of storing the conventional representation, one needs to make sure that IP V4 and IPV6 addresses have only one conventional (string) representation. It's well-known for ipV4. As for IPV6, there is also a standard format.
我更喜欢第一种策略.在最坏的情况下，您可以采用混合方法(虽然不使用酸)，并且将二进制和ascii表示形式与优先级"并排存储到二进制值中.
My preference goes to the first strategy. In the worst case, you can adopt an hybrid approach (non acid though) and store both the binary and the ascii representation side by side with "priority" to the binary value.
没有行同时包含v4和v6 地址.
No row contains both v4 and v6 addresses, however.
以IPV6格式表示的IPV4地址的标准表示为:::ffff:192.0.2.128.
The standard representation of a IPV4 address in IPV6 format is : ::ffff:192.0.2.128.
我不知道上下文，但是我会保留2列，一列用于IPV4，另一列用于不同的ipV6地址.
I don't know the context but I would however reserve 2 columns, one for IPV4 and the other for a distinct ipV6 address.
更新
经过@sleepyMonad的好评之后，我想指出，最好使用INTEGER数据类型代替 Number 数据类型，该类型将很高兴地容纳可能的最高值.用128位整数'ff ... ff'表示(需要 39 十进制数字). 38是十个从0到9的的最高幂，可以在128位上进行编码，但是仍然可以插入 2 ** 128-1 的最大无符号值(十进制340282366920938463463374374607431768211455).这是一个小测试来说明这种可能性.
Update
Following a good comment by @sleepyMonad's, I'd like to point out that instead of the Number data type it is preferable to use the INTEGER data type, which will happily accommodate the highest possible value that can be expressed with a 128 bits integer 'ff...ff' (which would need 39 decimal digits). 38 is the highest power of ten ranging from 0 to 9 that can be encoded on 128 bits but one can still insert the maximum unsigned value for 2**128 - 1 (decimal 340282366920938463463374607431768211455). Here is a small test to illustrate this possibility.
create table test (
id integer primary key,
ipv6_address_bin INTEGER );
-- Let's enter 2**128 - 1 in the nueric field
insert into test (id, ipv6_address_bin) values ( 1, to_number ( 'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF', 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') ) ;
-- retrieve it to make sure it's not "truncated".
select to_char ( ipv6_address_bin, 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' ) from test where id = 1 ;
-- yields 'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF'
select to_char ( ipv6_address_bin ) from test where id = 1 ;
-- yields 340282366920938463463374607431768211455
select LOG(2, ipv6_address_bin) from test where id = 1 ;
-- yields 128
select LOG(10, ipv6_address_bin) from test where id = 1 ;
-- yields > 38
这篇关于在Oracle中表示IPv4/IPv6地址的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
