For MySQL it's a best practice to store IP addresses in a INT
column rather than in a VARCHAR. This allows for more compact
storage. This best practice is only for MySQL as PostgreSQL does
have data types like inet and cidr.
MySQL is equiped with the INET_ATON() and INET_NTOA() functions
since version 3.23.15. In MySQL 5.6.3 the INET6_ATON() and
INET6_NTOA() functions were addes to finaly add IPv6 address
support. To make it easier to work with IPv4 and IPv6 addresses
the IS_IPV4_COMPAT(), IS_IPV4_MAPPED(), IS_IPV4() and IS_IPV6()
functions were added. The difference between the new and old
functions is that the old functions use INT UNSIGNED to store IP
addresses and the new function uses VARBINARY(16) for IPv6
addresses and VARBINARY(4) for IPv4 addresses.
Here are some examples about how you could do nifty things with
IP addresses in MySQL.
Store hosts and networks in MySQL
CREATE TABLE `hosts` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`hostname` varchar(255) DEFAULT NULL,
`domainname` varchar(255) DEFAULT NULL,
`ip` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `ip` (`ip`)
)
CREATE TABLE `networks` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`network` int(10) unsigned NOT NULL,
`mask` int(10) unsigned NOT NULL,
`name` varchar(255) DEFAULT '',
PRIMARY KEY (`id`),
KEY `network` (`network`),
KEY `mask` (`mask`)
)
Insert and retrieve entries
mysql> INSERT INTO `hosts`(`hostname`,`domainname`,`ip`) VALUES('test6','example.com',INET_ATON('192.168.1.2'));
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM `hosts` WHERE `id`=LAST_INSERT_ID();
+----+----------+-------------+------------+
| id | hostname | domainname | ip |
+----+----------+-------------+------------+
| 6 | test6 | example.com | 3232235778 |
+----+----------+-------------+------------+
1 row in set (0.00 sec)
mysql> SELECT hostname,domainname,INET_NTOA(ip) ip FROM `hosts` WHERE `id`=LAST_INSERT_ID();
+----------+-------------+-------------+
| hostname | domainname | ip |
+----------+-------------+-------------+
| test6 | example.com | 192.168.1.2 |
+----------+-------------+-------------+
1 row in set (0.00 sec)
Convert a subnetmask to CIDR notation.
mysql> SELECT INET_ATON('255.255.255.0'),BIT_COUNT(INET_ATON('255.255.255.0'));
+----------------------------+---------------------------------------+
| INET_ATON('255.255.255.0') | BIT_COUNT(INET_ATON('255.255.255.0')) |
+----------------------------+---------------------------------------+
| 4294967040 | 24 |
+----------------------------+---------------------------------------+
1 row in set (0.00 sec)
mysql> CREATE FUNCTION mask_to_cidr (mask CHAR(15)) RETURNS INT(2) DETERMINISTIC RETURN BIT_COUNT(INET_ATON(mask));
Query OK, 0 rows affected (0.00 sec)
mysql> SET @mask='255.255.252.0';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @mask,mask_to_cidr(@mask);
+---------------+---------------------+
| @mask | mask_to_cidr(@mask) |
+---------------+---------------------+
| 255.255.252.0 | 22 |
+---------------+---------------------+
1 row in set (0.00 sec)
And convert a CIDR mask back to dot-decimal notation
mysql> CREATE FUNCTION cidr_to_mask (cidr INT(2)) RETURNS CHAR(15) DETERMINISTIC RETURN INET_NTOA(CONV(CONCAT(REPEAT(1,cidr),REPEAT(0,32-cidr)),2,10));
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT cidr_to_mask(22);
+------------------+
| cidr_to_mask(22) |
+------------------+
| 255.255.252.0 |
+------------------+
1 row in set (0.00 sec)
Use the & operator to find the network address using an IP and
mask.
mysql> SELECT INET_NTOA(INET_ATON('255.255.255.0') & INET_ATON('192.168.2.3'));
+------------------------------------------------------------------+
| INET_NTOA(INET_ATON('255.255.255.0') & INET_ATON('192.168.2.3')) |
+------------------------------------------------------------------+
| 192.168.2.0 |
+------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SET @netmask='255.255.255.0';
Query OK, 0 rows affected (0.00 sec)
mysql> SET @ipaddr = '192.168.2.6';Query OK, 0 rows affected (0.00 sec)
mysql> SELECT CONV(INET_ATON(@ipaddr),10,2) ip,CONV(INET_ATON(@netmask),10,2) mask,CONV(INET_ATON(@ipaddr) & INET_ATON(@netmask),10,2) network\G
*************************** 1. row ***************************
ip: 11000000101010000000001000000110
mask: 11111111111111111111111100000000
network: 11000000101010000000001000000000
1 row in set (0.00 sec)
This can be used to join a table with host ip's with a table of
networks. (Remember the netmasks table in Solaris and/or
NIS?)
mysql> SELECT CONCAT(h.hostname,'.',h.domainname) fqdn, INET_NTOA(h.ip) ip, INET_NTOA(n.mask & h.ip) network, INET_NTOA(n.mask) mask FROM hosts h INNER JOIN networks n ON (n.mask & h.ip) = n.network;
+-------------------+-------------+-------------+---------------+
| fqdn | ip | network | mask |
+-------------------+-------------+-------------+---------------+
| test1.example.com | 192.168.0.1 | 192.168.0.0 | 255.255.255.0 |
| test2.example.com | 192.168.0.2 | 192.168.0.0 | 255.255.255.0 |
| test3.example.com | 192.168.0.3 | 192.168.0.0 | 255.255.255.0 |
| test4.example.com | 10.0.0.1 | 10.0.0.0 | 255.0.0.0 |
| test5.example.com | 10.0.0.2 | 10.0.0.0 | 255.0.0.0 |
+-------------------+-------------+-------------+---------------+
5 rows in set (0.00 sec)
With the ~ operator the mask can be inverted and then be used to
find the broadcast address using the XOR operator |.
mysql> SELECT CONV(INET_ATON(@ipaddr),10,2) ip,CONV(INET_ATON(@netmask),10,2) mask,CONV(INET_ATON(@ipaddr) & INET_ATON(@netmask),10,2) network, CONV(CONV(SUBSTRING(CONV(~INET_ATON('255.255.255.0'),10,2),-32),2,10) | INET_ATON(@ipaddr),10,2) broadcast\G
*************************** 1. row ***************************
ip: 11000000101010000000001000000110
mask: 11111111111111111111111100000000
network: 11000000101010000000001000000000
broadcast: 11000000101010000000001011111111
1 row in set (0.00 sec)
You could use a view to make it easier for users to select the
rows they wanted without using functions. But that will result in
a full scan if you search using an IP. MySQL does not optimize
this by first converting the search value to an INT but converts
all rows to a dot-decimal notation and then compares it. MariaDB
has a nifty feature called persistent virtual columns which allow indexing. For the
example below this means that instead of scanning all four rows
it just uses the index to find the row we need.
mysql> CREATE TABLE `iptest`(`ip_n` int(11) UNSIGNED NOT NULL, `ip_a` CHAR(15) AS (INET_NTOA(`ip_n`)) PERSISTENT, PRIMARY KEY(`ip_n`), KEY `ip_a` (`ip_a`));
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO `iptest`(`ip_n`) VALUES (INET_ATON('192.168.1.1')),(INET_ATON('192.168.1.2')),(INET_ATON('192.168.1.3')),(INET_ATON('192.168.1.4'));
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM `iptest`;
+------------+-------------+
| ip_n | ip_a |
+------------+-------------+
| 3232235777 | 192.168.1.1 |
| 3232235778 | 192.168.1.2 |
| 3232235779 | 192.168.1.3 |
| 3232235780 | 192.168.1.4 |
+------------+-------------+
4 rows in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM `iptest` WHERE `ip_a`='192.168.1.2';
+----+-------------+--------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | iptest | ref | ip_a | ip_a | 16 | const | 1 | Using where |
+----+-------------+--------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)
mysql> CREATE VIEW v_iptest AS SELECT ip_n,INET_NTOA(ip_n) ip_a FROM `iptest`;
Query OK, 0 rows affected (0.00 sec)
mysql> EXPLAIN SELECT * FROM `v_iptest` WHERE `ip_a`='192.168.1.2';
+----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | iptest | index | NULL | PRIMARY | 4 | NULL | 4 | Using where; Using index |
+----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)