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.
