ipv4与十进制整数的转换

原创于 2024-04-24 17:23:34 发布 · 351 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#前端 #linux #javascript

文章介绍了如何在SQL中使用CAST和CONCAT_WS函数进行IPv4地址（如184.153.243.168）与十进制整数（如3097097128）之间的转换，分别展示了从IPv4到十进制和从十进制到IPv4的转换示例。

样例数据：184.153.243.168 和 3097097128

1、ipv4转十进制整数

select 
CAST(
    split('184.153.243.168', '\\.') [0] * 256 * 256 * 256 + 
    split('184.153.243.168', '\\.') [1] * 256 * 256 + 
    split('184.153.243.168', '\\.') [2] * 256 + 
    split('184.153.243.168', '\\.') [3] 
AS bigint)

2、十进制整数转ipv4

select 
concat_ws('.'
    ,conv(substr(hex(cast('3097097128' as bigint)),1,2),16,10)
    ,conv(substr(hex(cast('3097097128' as bigint)),3,2),16,10)
    ,conv(substr(hex(cast('3097097128' as bigint)),5,2),16,10)
    ,conv(substr(hex(cast('3097097128' as bigint)),7,2),16,10)
);