Storing IPv4 and IPv6 hosts and networks

Question

I have to store the IPv4/v6 address and networks(CIDR notation) in a MySQL database. Unfortunately, I am constrained to use Mysql.

So, I'm looking for the equivalent of the inet type of PostgreSql. :

The inet type holds an IPv4 or IPv6 host address, and optionally its subnet, all in one field. The subnet is represented by the number of network address bits present in the host address (the "netmask"). If the netmask is 32 and the address is IPv4, then the value does not indicate a subnet, only a single host. In IPv6, the address length is 128 bits, so 128 bits specify a unique host address.

The input format for this type is address/y where address is an IPv4 or IPv6 address and y is the number of bits in the netmask. If the /y portion is missing, the netmask is 32 for IPv4 and 128 for IPv6, so the value represents just a single host. On display, the /y portion is suppressed if the netmask specifies a single host.

I'm aware of the followings functions of Mysql 5.6 INET_ATON(), INET_NTOA(), INET6_ATON(), INET6_NTOA(). But I use Mysql 5.5... So, forget INET6_xxxx.

I'm also aware of the following question "Storing IP address" but it doesn't deals with network CIDR notation.

How you would implement the equivalent of inet type in Mysql 5.5?

• 3 columns : Network (2xBIGINT), CIDR (TINYINT) ?
• string ? (@Phil)

Answer 1

First: there is no equivalent of the inet type of PostgreSql in MySQL yet.

You should just use the string type to store all kind of IP addresses. if you need them all in a certain format, convert them before inserting them into the DB.

If you need to do calculations with them, as you mentioned INET_ATON() and INET_NTOA() are the needed functions to be used.

Answer 2

Building on rubo77... if the address block is stored in CIDR notation, then it might be desirable to determine the first and last ip address of the block.

assuming our CIDR block is '10.20.30.40/24'

mysql test> set @cidr:='10.20.30.40/24';
Query OK, 0 rows affected (0.00 sec)

mysql test> select @cidr;
+----------------+
| @cidr          |
+----------------+
| 10.20.30.40/24 |
+----------------+
1 row in set (0.00 sec)

99% of the time, the address in the cidr notation is the first address of the address block. Sometimes, people make mistakes (as I have intentionally done here), so we can find the first address just to be safe by...

select inet_ntoa( (@shifted_network_number :=
                   inet_aton(substring_index(@cidr,'/',1)) >> (@host_bits:=(32-substring_index(@cidr,'/',-1))))
                                                           << @host_bits) as starting_ip;
+-------------+
| starting_ip |
+-------------+
| 10.20.30.0  |
+-------------+
1 row in set (0.00 sec)

The ending address...

select inet_ntoa( ((@shifted_network_number :=
                 inet_aton(substring_index(@cidr,'/',1)) >> (@host_bits:=(32-substring_index(@cidr,'/',-1)))) + 1
                                                         << @host_bits) - 1) as starting_ip;
+--------------+
| starting_ip  |
+--------------+
| 10.20.30.255 |
+--------------+
1 row in set (0.00 sec)

The address range...

select concat( inet_ntoa( (@shifted_network_number :=
                       inet_aton(substring_index(@cidr,'/',1)) >> (@host_bits:=(32-substring_index(@cidr,'/',-1))))
                                                               << @host_bits)
              , ' - '
              , inet_ntoa( (((@shifted_network_number + 1) << @host_bits) - 1))) as IP_Range;
+---------------------------+
| IP_Range                  |
+---------------------------+
| 10.20.30.0 - 10.20.30.255 |
+---------------------------+
1 row in set (0.01 sec)