将PostgreSQL中的IP地址转换为整数? [英] Convert IP address in PostgreSQL to integer?

问题描述

是否存在能够完成此操作的查询?

Is there a query that would be able to accomplish this?

例如，给定条目"216.55.82.34" ..我想将字符串除以."，然后应用等式:

For example given an entry '216.55.82.34' ..I would want to split the string by the '.'s, and apply the equation:

IP编号= 16777216 * w + 65536 * x + 256 * y + z 其中IP地址= w.x.y.z

IP Number = 16777216*w + 65536*x + 256*y + z where IP Address = w.x.y.z

仅通过查询就能做到吗?

Would this be possible from just a Query?

推荐答案

您可以使用split_part().例如:

CREATE FUNCTION ip2int(text) RETURNS bigint AS $$ 
SELECT split_part($1,'.',1)::bigint*16777216 + split_part($1,'.',2)::bigint*65536 +
 split_part($1,'.',3)::bigint*256 + split_part($1,'.',4)::bigint;
$$ LANGUAGE SQL  IMMUTABLE RETURNS NULL ON NULL INPUT;


SELECT ip2int('200.233.1.2');
>> 3370713346

或者，如果不想定义函数，只需:

Or, if don't want to define a function, simply :

SELECT split_part(ip,'.',1)::bigint*16777216 + split_part(ip,'.',2)::bigint*65536 +
 split_part(ip,'.',3)::bigint*256 + split_part(ip,'.',4)::bigint;

后一种方法的缺点是，如果该值是通过某种计算而不是仅仅是表字段给出的，则计算效率可能很低，或者编写起来很麻烦.

The drawback of the later is that, if the value is given by some computation instead of being just a table field, it can be inefficient to compute, or ugly to write.

这篇关于将PostgreSQL中的IP地址转换为整数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！