Welcome to the Question2Answer Q&A. There's also a demo if you just want to try it out.
+2 votes
605 views
in Q2A Core by
why ipaddress is stored as varbinary(16) in qa_users table and other tables ?

can we store as plain ip address like - 76.180.83.10 ?

Any downside to saving plain format in mysql ?
Q2A version: Latest

1 Answer

+2 votes
by
selected by
 
Best answer

An IPv4 address consists of 4 octets (8 bits each) [1]. It's usually written as four numbers separated by dots, as in: 212.227.196.186 [2].

An IPv6 address consists of 16 octets, shown using hexadecimal representation (two digits per octet) and using a colon character (:) after each pair of octets for readability, like this FE80:0000:0000:0000:0123:4567:89AB:CDEF [1].

why ipaddress is stored as varbinary(16) in qa_users table and other tables ?

I think because it uses less storage for saving both IPv4 and IPv6 addresses [3][4]. Saving actual IP addresses (as sets of octets) requires 16 bytes (or 16 octets) per address; in contrast, saving their human readable representation would require 39 bytes (32 bytes for hexadecimal numbers plus 7 bytes for colons) per address, for taking into account the longest addresses.

can we store as plain ip address like - 76.180.83.10?

Yes, you can; but I don't recommend it (see below).

Any downside to saving plain format in mysql ?

Apart from using more storage than needed or dropping compatibility with IPv6 addresses, it won’t let you look up IP ranges [5], because the database will compare IP addresses like binary strings, instead of numbers (octet by octet).

For example, running the following SQL snippet won’t output the right set of addresses:

DROP TABLE IF EXISTS `my_users`;

CREATE TABLE `my_users` (
	`ip` VARBINARY(16)
);

INSERT INTO `my_users` VALUES
('76.180.83.7'),
('76.180.83.8'),
('76.180.83.9'),
('76.180.83.10'),
('76.180.83.11'),
('76.180.83.12');

SELECT *
FROM `my_users`
WHERE `ip` BETWEEN '76.180.83.7' AND '76.180.83.12';

DROP TABLE IF EXISTS `my_users`;

but this one will:

DROP TABLE IF EXISTS `my_users`;

CREATE TABLE `my_users` (
	`ip` VARBINARY(16)
);
 
INSERT INTO `my_users` VALUES
(UNHEX(HEX(INET_ATON('76.180.83.7')))),
(UNHEX(HEX(INET_ATON('76.180.83.8')))),
(UNHEX(HEX(INET_ATON('76.180.83.9')))),
(UNHEX(HEX(INET_ATON('76.180.83.10')))),
(UNHEX(HEX(INET_ATON('76.180.83.11')))),
(UNHEX(HEX(INET_ATON('76.180.83.12'))));

SET @ip_min = UNHEX(HEX(INET_ATON('76.180.83.7')));
SET @ip_max = UNHEX(HEX(INET_ATON('76.180.83.12')));

SELECT INET_NTOA(CONV(HEX(`ip`), 16, 10))
FROM `my_users`
WHERE `ip` BETWEEN @ip_min AND @ip_max;

DROP TABLE IF EXISTS `my_users`;

I hope this answer is helpful.

by
Hi Jair ! Thank you for your reply. I guess it makes sense to save on space if I already a have large database. However its extra step to convert from varbinary(16)  to human readable format in custom applications. Example - Displaying thousands of ips in datatables.
by
Jair !

Do you mind correcting my code on how to insert ip in varbinary(16)  format to mysql ?

How do i convert this ip format to varbinary(16) ?
Example -
//Get Ip address
$ip = $_SERVER['REMOTE_ADDR'];

INSERT INTO test_table (title, ip) VALUES ($,$)',$title, $ip);
by
+1
by
+1
Thank you for reference and appreciate your help :)
by
You're welcome!
by
by
+1
Hi John123! I do but I'm busy right now. I'll answer when I have a chance.
...