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)
This comment has been removed by a blog administrator.
ReplyDeleteReally nice!
ReplyDeleteI have one question... For example when I have a table with lots of networks (and each having differrent netmask etc.)... How would a query look like when I want to find all networks containing a given IP ordered by netmask?
Example:
Networks:
10.0.0.0 / 8
10.0.2.0 / 24
10.10.0.0 / 16
And now I want to find the networks matching my IPs.
when searching for 10.10.5.100 I want to get 10.10.0.0 / 16
when searching for 10.0.2.100 I want to get 10.0.2.0 / 24
when searching for 10.0.5.100 I want to get 10.0.0.0 / 8
How would that work?
Kind regards
Martin
The every day payback rate won't be higher than 10% of day by day net deals, the day by day rate depends on the month to month charge cards deals volume and the measure of loan required. The payback time allotment is organized for a 6-9 months term, however it's not settled, and there won't be any punishments on the off chance that it takes longer. Cash Advances corona
ReplyDeleteCeaselessly steady and an exceptional sense of duty regarding the universe of bloggers.
ReplyDelete192 .168.0.1
We have sell some products of different custom boxes.it is very useful and very low price please visits this site thanks and please share this post with your friends. https://192-168-i-i.com
ReplyDeleteA lot of people having an incorrect image about the cash advance loans or sometimes refer it as bad credit payday loans. Plus d'information
ReplyDeleteI wish more authors of this type of content would take the time you did to research and write so well. I am very impressed with your vision and insight. bezoek website
ReplyDeleteExcellent .. Amazing .. I’ll bookmark your blog and take the feeds also…I’m happy to find so many useful info here in the post, we need work out more techniques in this regard, thanks for sharing. deze website
ReplyDeleteThe data you have posted is extremely valuable. The locales you have alluded was great. A debt of gratitude is in order for sharing... privacyinthenetwork
ReplyDeleteGrandstream PBX System- Grandstream Distributor Cameroon VDS the Grandstream distributor in Cameroon continues to bring innovative Grandstream Products to the IP communications / Telephony market with compelling values and features. Grandstream Networks is headquartered in Brookline, Massachusetts with offices in Yealink Office Phones
ReplyDeleteI think this is an informative post and it is very beneficial and knowledgeable. Therefore, I would like to thank you for the endeavors that you have made in writing this article. All the content is absolutely well-researched. Thanks... https://192-168-i-i.com
ReplyDeleteWow! Such an amazing and helpful post this is. I really really love it. It's so good and so awesome. I am just amazed. I hope that you continue to do your work like this in the future also t shirt met tekst
ReplyDeleteWe are really grateful for your blog post. You will find a lot of approaches after visiting your post. I was exactly searching for. Thanks for such post and please keep it up. Great work. shisha
ReplyDeletePretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I'll be subscribing to your feed and I hope you post again soon. Big thanks for the useful info. estilo de vida
ReplyDeleteOther computerized gadgets that have the ability to get to web do likewise utilize Internet Protocol delivers to speak with different gadgets just as PCs. It permits the area of billions of advanced administrations that are associated with the web to be pinpointed and separated from different gadgets for example in the event that somebody needs your postage information to send you a letter, a far off PC needs your Internet Protocol address to speak with your PC.linksys router login
ReplyDeleteFor instance, when the business accuses the laborer after a light obligation work isn't working out. This circumstance can bring about end of time misfortune remuneration benefits. Email Extractor Software
ReplyDeleteIf you are unable to Setup Linksys Wireless Router? Don't worry: our experts helps you find the solution with quick & simple ways. Get in touch with us. Dial our toll-free helpline numbers at USA/CA: +1-888-480-0288 and UK/London: +44-800-041-8324.
ReplyDeleteIf you are looking for a solution on how to add Disney Plus on Roku, Don’t worry: this article helps you find the best solution for adding channels on Roku.
ReplyDeleteAre you looking for a solution on how to resolve Linksys WiFi Extender Not Working? Don’t worry visit our website or get in touch with our experienced experts.
ReplyDeleteYou can access many blocked websites from your office or school using proxy websites. site web
ReplyDeletePretty nice post. I simply stumbled upon your weblog and wanted to mention that I’ve truly loved browsing your blog posts. After all I will be subscribing on your feed and I’m hoping you write once more soon! White House Market Link
ReplyDeleteThanks for the blog loaded with such a large amount of information. Stopping by your blog helped me to induce what i used to be yearning for. white house market
ReplyDeletethis product is good and thanks for a post for this kind off post .After you set up your NETGEAR WiFi range extender, you can log in to the extender to view and change the settings. If you have not installed and connected your WiFi range extender to your existing WiFi network, visit NETGEAR Support and download your model’s quick start guide before continuing. Verify that your model is tagged under the “This article applies to:” section.mywifiext setup
ReplyDeleteThe Wi-Fi booster security is quite guaranteed. It is usually not different from the same security level that typical Wi-Fi routers provide, such as WPA2, WPA and WEP among others.mywifiext setup
ReplyDeleteThey will be answerable for help of the database they planned on the off chance that one isn't. Other than this distinction the ranges of abilities are basically the same, https://onohosting.com/
ReplyDelete