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.


81 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
  11. Incredible Article it its truly instructive and inventive update us as often as possible with new upgrades. its was truly important. much obliged. check my ip address

    ReplyDelete
  12. Thank you for taking the time to publish this information very useful!
    digital marketing company

    ReplyDelete
  13. Thank you for sharing a bunch of this quality contents, I have bookmarked your blog. Please also explore advice from my site. I will be back for more quality contents. https://192-168-i-i.com

    ReplyDelete
  14. As the classic film Spinal Tap once posited, what is the point of pushing the volume up to 10 when you can take it to 11? Increasing the threshold is never something you expect to be possible, but wireless internet services have in many ways made that possible these days. Unlimited wireless internet

    ReplyDelete
  15. Hello I am so delighted I located your blog, I really located you by mistake, while I was watching on google for something else, Anyways I am here now and could just like to say thank for a tremendous post and a all round entertaining website. Please do keep up the great work. Groovepages vs Clickfunnels

    ReplyDelete
  16. seaport hack Excellent trick this great friend, this was what I was looking for a long time and finally something that works. I recommend them all if it is real is not a lie. Thanks friend. Keep it up

    ReplyDelete
  17. In addition to having your keywords included in your META Tags, it's important that the post also begins and ends with your keyword phrase - in the opening sentence, and the closing sentence.
    SEO bureau NL

    ReplyDelete
  18. After reading your article I was amazed. I know that you explain it very well. And I hope that other readers will also experience how I feel after reading your article. power lead system review

    ReplyDelete
  19. The point when somebody on the Internet, they might want to download from download facebook video

    ReplyDelete
  20. I gotta favorite this website it seems very helpful . purchase instagram likes paypal

    ReplyDelete
  21. home wifi packages Du Home Internet Packages with exciting Offers Staring from AED 270/- High Speed Internet upto 800 MBPS. Du Internet Packages with Free Landline & TV Channels

    ReplyDelete
  22. Super-Duper site! I am Loving it!! Will come back again, Im taking your feed also, Thanks. online marketing

    ReplyDelete
  23. Discover beneath, and get the "Points of interest" recognized! If you don't mind share!best smm panel

    ReplyDelete
  24. I really love the way you discuss this kind of topic..”\"*. web design company los angeles

    ReplyDelete
  25. I?¦ve read several excellent stuff here. Certainly worth bookmarking for revisiting. I surprise how a lot attempt you set to make this type of great informative website. web designer los angeles

    ReplyDelete
  26. Ultimately, a problem that I'm passionate about. I have looked for data of this caliber for the previous various hours. Your site is greatly appreciated. website design agency

    ReplyDelete
  27. Almost all businesses nowadays have some relationship with the Internet whether jealous computers it's a simple business listing or an online store. What implications would be put upon businesses across the world with the loss of the Internet?

    ReplyDelete
  28. First discharged in May 23, 1995, MySQL is an open source database programming.Change Site URL Using MySQL

    ReplyDelete
  29. Get access to global databases with emails and contact details for over 500 business niches, marketing products and SEO and lead generation software to take your online business to new heights. Instant download. Flexible membership plans and low pric Technology

    ReplyDelete
  30. Generate your own targeted business sales leads with laser precision on complete auto pilot with revolutionary CBT Web Scraper and Email Extractor Software. Scrape and extract business data from Google Maps, Google SERPs, Yellow Pages, Yelp, Facebook Business Pages, Twitter, LinkedIn and custom website lists. Download the only lead generation tool you will ever need right now. Technology

    ReplyDelete
  31. I wanted to thank you for this great read!! I definitely enjoying every little bit of it I have you bookmarked to check out new stuff you post. Limitless Referrals

    ReplyDelete
  32. I've been related with both those that succeeded and those that fizzled. cloudways review

    ReplyDelete
  33. Pretty 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. regression testing

    ReplyDelete
  34. Damien has an eternal quest for gaining knowledge and is very interested in places and people, both modern and historical. Natnit Blog

    ReplyDelete
  35. Data between network systems is sent from one port to another at the end of a communication. In a network system, when you want to redirect that data to another computer system, one uses port forwarding. https://192-168-0-1ip.org/

    ReplyDelete
  36. They are exceptionally convenient for checking the outcomes daily late however in the event that you can hardly wait that long there is a large group of different approaches to discover the outcomes; numerous accessible while you are progressing. data hk

    ReplyDelete
  37. Thanks so much for this information. I have to let you know I concur on several of the points you make here and others may require some further review, but I can see your viewpoint. backlinks

    ReplyDelete
  38. Along these lines, you don't look as much humiliation the first run through on the off chance that you are new in the game. togel online

    ReplyDelete
  39. An thrilling question in an age wherein the Internet plays an indispensable a part of ordinary lifestyles, company and society! So what are the results of a life without the net?fax documents online

    ReplyDelete
  40. Much obliged for an intriguing web journal. What else may I get such an information written in such an ideal methodology? I have an endeavor that I am seconds ago working on, and I have been keeping watch for such information


    automated binary

    ReplyDelete
  41. The concept of Internet jurisdiction can be complicated and unclear. What happens when a dispute arises over an item or service purchased from your business through the internet? Learn where your business can potentially be sued! Kickass Proxy

    ReplyDelete
  42. This is really a nice and informative, containing all information and also has a great impact on the new technology. Check it out here:192.168.1.254

    ReplyDelete
  43. SEO - Search Engine Optimisation, is an extremely valuable skill. The job websites - at a time of increasing unemployment - seem to be full of very well paid positions for SEO executives. The reason for this is that actually, there are very few people in the UK who are truly skilled in SEO - and for that reason, those that are tend to choose to work for themselves regardless of the large amount of money they could earn working for an SEO firm.seo small business

    ReplyDelete
  44. Then again, the article "What Screenagers Say About... " High young understudies were gotten some information about what they thought of technology yet most communicated loving it. 20 kg to lbs

    ReplyDelete
  45. I feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it. Business Guru

    ReplyDelete
  46. 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. 1on1

    ReplyDelete
  47. Additionally, the article "I'm not a PC individual" (Lohnes 2013) addresses the way that understudies desires all things considered is altogether different.Yellow Pages Scraper

    ReplyDelete
  48. Storing IP address in MySQL was an intimidating task for me and I was facing a lot of error in this task but after following your instructions, I have fulfilled this complicated task. Dissertation Writing Service

    ReplyDelete
  49. There are a lots of method to do this but I have not much idea about it.
    Law experts

    ReplyDelete
  50. Individuals who practice normally will in general do so on the grounds that it gives them a tremendous feeling of prosperity.
    Learn More

    ReplyDelete
  51. Yet, having some inviting counsel available to you,
    at whatever point and any place you need it, makes it simpler and more fun
    Learn More

    ReplyDelete
  52. www.KhanaParaTeerResult.netYou need to have patience and practice until you have a steady aim. The first thing you should always keep in mind is that the game requires a lot of skill and you need to be patient and learn how to shoot at an arrow.

    ReplyDelete
  53. For example, there is the emailing service which you can acquire to send soft-copy invites instead of spending hours designing and printing hard-copy, handwritten cards. Devops tech events

    ReplyDelete

  54. The wedding is probably the most joyful days of our lives. The vast majority never need to overlook the event and attempt to cause it as vital as they to can either by utilizing interesting structures, showing up to the spot in a stretch limousine or having an excursion to the Bahamas. Be that as it may, Find the best Wedding Photographers. wedding Photographer and planner in indiana

    ReplyDelete
  55. You need to attempt to stay with all quieted pastel tones or all lively immersed colors.besimple.com/

    ReplyDelete
  56. With the efficiency, speed and extensive geographical reach of the Internet, more offline businesses and professionals are going online and taking advantage of Internet marketing. best internet provider toronto

    ReplyDelete
  57. This is such a great resource that you are providing and you give it away for free. I love seeing blog that understand the value of providing a quality resource for free. internet providers calgary

    ReplyDelete
  58. I’m going to read this. I’ll be sure to come back. thanks for sharing. and also This article gives the light in which we can observe the reality. this is very nice one and gives indepth information. thanks for this nice article... fast speed test

    ReplyDelete
  59. Great article with excellent idea!Thank you for such a valuable article. I really appreciate for this great information.. summerpride.it

    ReplyDelete
  60. I was surfing the Internet for information and came across your blog. I am impressed by the information you have on this blog. It shows how well you understand this subject. bandarq

    ReplyDelete
  61. I am continually amazed by the amount of information available on this subject. What you presented was well researched and well worded in order to get your stand on this across to all your readers. seo backlink

    ReplyDelete
  62. Great post, you have pointed out some fantastic points , I likewise think this s a very wonderful website. unique domain

    ReplyDelete
  63. Pretty 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. salad mixer

    ReplyDelete
  64. AdWords is an extremely valuable program by Google where you can put a promotion on different destinations to publicize your own. SEO

    ReplyDelete
  65. Looking for a funny gift ideas for your friend, here you should find an item that will surprise and make them laugh for sure. funny gift ideas for friends

    ReplyDelete
  66. Please give some advice on how to achieve this kind of posts. air conditioning service Berwick

    ReplyDelete
  67. I want to say thanks to you. I have bookmark your site for future updates. https://www.lexmove.org/best-immigration-consultants-in-delhi/

    ReplyDelete
  68. Online cake delivery in Bhopal We deliver cake & flower that comprises of happiness, emotions relations. We serve on time whether it is cake, flower, teddy, chocolates or any gift.

    ReplyDelete
  69. Buy Both Quality and Cheap YouTube Subscribers. Channel proprietors who need to growth the wide variety of views display their channel in higher fine and rank in the top ratings choose to buy subscribers https://bit.ly/3f5c1QZ

    ReplyDelete
  70. Pay per click promoting, web based publicizing and connection trade programs, is marketing, can be utilized to make the Internet business to bring in cash online there are some significant projects. 1 million email list

    ReplyDelete
  71. Wow! 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 nfr live on roku

    ReplyDelete
  72. It is actually all at once a great put which i quite savored surfing. Is not really routine that give the outlook to see items. lpwan

    ReplyDelete
  73. I've proper selected to build a blog, which I hold been deficient to do for a during. Acknowledges for this inform, it's really serviceable! Air Conditioning Service Hampton Park

    ReplyDelete
  74. The article posted was very informative and useful. You people are doing a great job. Keep going. Buy Youtube Views India

    ReplyDelete
  75. Wow, What an Outstanding post. I found this too much informatics. It is what I was seeking for. I would like to recommend you that please keep sharing such type of info.If possible, Thanks. internet of things

    ReplyDelete
  76. Thanks for the tips guys. They were all great. I have been having issues with being fat both mentally and physically. Thanks to you guys i have been showing improvements. Do post more. آدرس جدید سایت گاد بت

    ReplyDelete
  77. This is a great article thanks for sharing this informative information. I will visit your blog regularly for some latest post. I will visit your blog regularly for Some latest post. Agencia SEO

    ReplyDelete
  78. You've completed in excellent work. t suggest to my frtends ind personilly wtll certitnly dtgtt. t'm conftdent they'll be gitned from thts webstte. سایت تتل بت

    ReplyDelete