How to store IP (internet protocol) address in MySQL?

Internet Protocol address is a special kind of data. This data does not correspond directly to available MySQL built-in data types. I have seen in many servers, the IP(Internet Protocol) address is stored in CHAR(12), VARCHAR(15), TEXT and so on. 

The dotted-quad notation is just a way of writing for better readability, it is not the original form of raw data. The IP address is a UNSIGNED INTEGERS they are not strings. 

So the question is how we can store the IP addresses with dot in integer columns? The MySQL provides the built-it function to do this operation. The functions are given below:







For IPv4 addresses:

INET_ATON()
mysql> select inet_aton('127.0.0.1');

+------------------------+
| inet_aton('127.0.0.1') |
+------------------------+
|             2130706433 | 
+------------------------+
1 row in set (0.11 sec)
INET_NTOA()
SELECT inet_ntoa('2130706433');

+-------------------------+
| inet_ntoa('2130706433') |
+-------------------------+
| 127.0.0.1               | 
+-------------------------+
1 row in set (0.02 sec)







For IPv6 addresses:

The MySQL provides the following built-in functions to store and retrieve the IPv6 addresses. Here, IPv6 addresses are not stored as integers, since numeric-format IPv6 addresses require more bytes than the UNSIGNED BIGINT. So the below functions returns the VARBINARY(16) data types. Let us see with an example.

mysql> select hex(inet6_aton('127.0.0.1'));
+---------------------------------+
| hex(inet6_aton('127.0.0.1')) |
+---------------------------------+
| 7F000001                             |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select hex(inet6_aton('2001:0db8:85a3:0000:0000:8a2e:0370:7334'));
+---------------------------------------------------------------------------+
| hex(inet6_aton('2001:0db8:85a3:0000:0000:8a2e:0370:7334')) |
+---------------------------------------------------------------------------+
| 20010DB885A3000000008A2E03707334                                 |
+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select inet6_ntoa(unhex('20010DB885A3000000008A2E03707334'));
+----------------------------------------------------------------------------+
| inet6_ntoa(unhex('20010DB885A3000000008A2E03707334')) |
+----------------------------------------------------------------------------+
| 2001:db8:85a3::8a2e:370:7334                                                    |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)


Note:


Assume that, you are writing a query to find out the users, who are connected from the internet protocol address 127.0.0.1. So the following query can be derived.

SELECT name FROM user WHERE inet_ntoa(ipaddress) = '127.0.0.1';

Be aware that, this query will not use the index created on the ipaddress column, since, we are modifying the index column during the execution, also, it will convert the integer into IP adress row by row. So the work around will be:

SET @ip = inet_aton('127.0.0.1');
SELECT name FROM user WHERE ipaddress = @ip;
(or)


SELECT name FROM user WHERE ipaddress = inet_aton('127.0.0.1');



Now if you check the query execution plan, you will see that, the query using index on ipaddress column. I hope this post may help you. Please share your views on comment section and if you have other better methods, please share it on comment section.


11 comments:

  1. Hi,nice post... 192.168.1.254 login is a default access point that is factory set on some of the popular router models. Mainstream router manufacturers like Linksys, D-link, Belkin etc use 192.168.1.254 as their default router admin pages. For that matter, the whole 192.168.1.x series is IANA reserved for logging in home networks.

    ReplyDelete
  2. At Advanced LED Lights, our authentic 3W and 10 W CREE LED lights deliver highly focused beams of light in the precise wavelengths plants need for optimal photosynthesis. In fact, research suggests high-quality LEDs produce more photons (light particles) than standard HID lights. We spend 30 percent more than the standard price in order to purchase the highest quality LEDs possible. Our investment means our LED lights will produce plant growth and garden yields than will surpass your highest expectations.

    ReplyDelete
  3. I have perused your online journal it is exceptionally useful for me. I need to express profound gratitude to you. I have bookmark your site for future redesigns.  Visiter le site

    ReplyDelete
  4. Thanks for taking the time to discuss that, I feel strongly about this and so really like getting to know more on this kind of field. Do you mind updating your blog post with additional insight? It should be really useful for all of us. 60-Minute Empire

    ReplyDelete
  5. Regular visits listed here are the easiest method to appreciate your energy, which is why why I am going to the website everyday, searching for new, interesting info. Many, thank you Meer hierover leest je hier

    ReplyDelete
  6. Idiotic and additionally trusting people at the same time can be an obstacle, on aged and also fully grown folk assembled also will follow a complication. Age and also embryonic concern together. bezoek website

    ReplyDelete
  7. I truly awed after read this in light of some quality work and educational contemplations . I just wanna express profound gratitude for the essayist and want you to enjoy all that life has to offer for coming!. privacy in the network

    ReplyDelete
  8. I found so many interesting stuff in your blog especially its discussion. Really its great article. Keep it up. https://privacyonline.com.br

    ReplyDelete
  9. I cannot wait to dig deep and kickoff utilizing resources that I received from you. Your exuberance is refreshing. https://192-168-i-i.com/

    ReplyDelete
  10. Grandstream 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 Grandstream Office Phones

    ReplyDelete