How to convert binary to integer in mysql?

Question

To put my question into perspective:

I have a PHP app, which stores IP's of users in a MySQL table.

The column type is VARBINARY(16), and the app uses PHP's inet_pton to form a binary string.

That is the string has 4 bytes for a typical IP4 address.

How to retrieve these IPs from the table, displaying them in a human readable form?

My current solution is:

select INET_NTOA(CONV(HEX(ip),16,10)) from operation_ip;

Is there a more direct way to do that?

In particular is CONV(HEX(x),16,10) the easiest way to change 4 bytes into an integer (actually I believe it is not even an integer, but a string which looks like integer).

(I use VARBINARY(16), as PHP's inet_pton can return 16-bytes for IPv6 addresses. AFAIK MySQL's INET_NTOA does not support IPv6, but at the moment this is not my biggest concern).

if you want a more direct form, don't convert the ip address to binary in order to store it on the database... that or sore both the address's string and the binary value in the database. — SQL.injection
– SQL.injection, Commented Jul 4, 2013 at 7:41

RandomSeed · Accepted Answer · 2013-07-04 08:04:49Z

0

MySQL has nice built-in functions to handle this case:

INSERT INTO t(ip) VALUE (INET_ATON('1.2.3.4')); -- stores integer 16909060
SELECT INET_NTOA(ip) FROM t; -- returns string '1.2.3.4'

Or the PHP way:

$sql = 'INSERT INTO t(ip) VALUE (' . ip2long('1.2.3.4') . '))'; 
// ip2long('1.2.3.4') = 16909060
// reverse conversion: long2ip(16909060) returns '1.2.3.4'

answered Jul 4, 2013 at 8:04

RandomSeed

29.9k6 gold badges54 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

qbolec Over a year ago

If I understand correctly, your solution is to use a 4-byte integer as the type of the column. Unfortunatelly IPv6 requires 16-bytes, and MySQL does not have a support for such long integers, that's why I use VARBINARY(16). On the other hand, in some queries I am 100% sure that the result contains only rows with IPv4 adresses, and in such cases I'd like to convert VARBINARY(16) to (4-byte) INT quickly.

RandomSeed Over a year ago

I disregarded the problem with IPv6 since you wrote "IPv6 (...) is not my biggest concern". As of v5.6.3, MySQL features exactly what you need: INET6_ATON() and INET6_NTOA(). Until then, your approach is probably the best way to go. I woundn't store IPv4 and IPv6 in the same column, though, but in distinct columns.

Collectives™ on Stack Overflow

How to convert binary to integer in mysql?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related