{"id":49,"date":"2007-03-02T18:02:10","date_gmt":"2007-03-02T18:02:10","guid":{"rendered":"http:\/\/thenoyes.com\/littlenoise\/?p=49"},"modified":"2007-07-09T13:28:55","modified_gmt":"2007-07-09T19:28:55","slug":"octets-from-inet_aton","status":"publish","type":"post","link":"https:\/\/thenoyes.com\/littlenoise\/?p=49","title":{"rendered":"Octets from INET_ATON"},"content":{"rendered":"

So you’ve follow Arjen’s suggestion<\/a> and are storing all your IP addresses in a nice integer format, having converted them with INET_ATON and selecting them with INET_NTOA.<\/p>\n

But now you want to use one of the octets, maybe to find out how many connections you get from 192.x.x.x. <\/p>\n

One way is to turn the integer back into a string, and use one of the substring functions to grab the parts you need. Let’s try that out for speed (I’ll use 192.168.1.1 in my examples, which translates to 3232235777):<\/p>\n

mysql> SELECT BENCHMARK(10000000, SUBSTRING_INDEX(INET_NTOA(3232235777), '.', 1) = '192');\r\n+-----------------------------------------------------------------------------+\r\n| BENCHMARK(10000000, SUBSTRING_INDEX(INET_NTOA(3232235777), '.', 1) = '192') |\r\n+-----------------------------------------------------------------------------+\r\n|                                                                           0 |\r\n+-----------------------------------------------------------------------------+\r\n1 row in set (6.28 sec)<\/pre>\n
Is there a better way? Of course; I’d hardly be writing this blog entry if there weren’t. According to the manual, the calculation for INET_ATON is
\n192x256^3 + 168x256^2 + 1x256^1 + 1x256^0<\/code>
\nNotice all those 256s. 256 is 2^8, so all we have to do is shift the bits to the right by the appropriate multiple of 8, and then throw away anything higher than the 8th bit. We’ll check real quick that gives us the right number:<\/p>\n
mysql> SELECT\r\n    ->   (3232235777 >> 24) & 255 AS oct1,\r\n    ->   (3232235777 >> 16) & 255 AS oct2,\r\n    ->   (3232235777 >> 8) & 255 AS oct3,\r\n    ->   (3232235777 >> 0) & 255 AS oct4;\r\n+------+------+------+------+\r\n| oct1 | oct2 | oct3 | oct4 |\r\n+------+------+------+------+\r\n|  192 |  168 |    1 |    1 |\r\n+------+------+------+------+<\/pre>\n
Looks like the right answer. Let’s see how it compares.<\/p>\n
mysql> SELECT BENCHMARK(10000000, (3232235777 >> 24) & 255 = 192);\r\n+-----------------------------------------------------+\r\n| BENCHMARK(10000000, (3232235777 >> 24) & 255 = 192) |\r\n+-----------------------------------------------------+\r\n|                                                   0 |\r\n+-----------------------------------------------------+\r\n1 row in set (1.61 sec)<\/pre>\n
Noticeably faster, and you get to use those bitwise operators that you’ve always wondered about but never had a reason to use.<\/p>\n","protected":false},"excerpt":{"rendered":"
So you’ve follow Arjen’s suggestion and are storing all your IP addresses in a nice integer format, having converted them with INET_ATON and selecting them with INET_NTOA. But now you want to use one of the octets, maybe to find out how many connections you get from 192.x.x.x. One way is to turn the integer […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[4],"tags":[],"class_list":["post-49","post","type-post","status-publish","format-standard","hentry","category-mysql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p2IBF1-N","jetpack_sharing_enabled":true,"jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/49","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=49"}],"version-history":[{"count":0,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/49\/revisions"}],"wp:attachment":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=49"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=49"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=49"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}