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.


110 comments:

  1. @nidhi, Thanks for your input!

    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. 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
  17. 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
  18. The point when somebody on the Internet, they might want to download from download facebook video

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

    ReplyDelete
  20. 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
  21. Discover beneath, and get the "Points of interest" recognized! If you don't mind share!best smm panel

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

    ReplyDelete
  23. 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
  24. 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
  25. 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
  26. First discharged in May 23, 1995, MySQL is an open source database programming.Change Site URL Using MySQL

    ReplyDelete
  27. 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
  28. 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
  29. I've been related with both those that succeeded and those that fizzled. cloudways review

    ReplyDelete
  30. 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
  31. Damien has an eternal quest for gaining knowledge and is very interested in places and people, both modern and historical. Natnit Blog

    ReplyDelete
  32. 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
  33. 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
  34. 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
  35. 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
  36. 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
  37. 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
  38. 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
  39. 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
  40. 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
  41. 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
  42. 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
  43. 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
  44. 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
  45. There are a lots of method to do this but I have not much idea about it.
    Law experts

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

    ReplyDelete
  47. 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
  48. 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
  49. 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

  50. 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
  51. You need to attempt to stay with all quieted pastel tones or all lively immersed colors.besimple.com/

    ReplyDelete
  52. 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
  53. 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
  54. 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
  55. Great article with excellent idea!Thank you for such a valuable article. I really appreciate for this great information.. summerpride.it

    ReplyDelete
  56. 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
  57. 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
  58. Great post, you have pointed out some fantastic points , I likewise think this s a very wonderful website. unique domain

    ReplyDelete
  59. 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
  60. AdWords is an extremely valuable program by Google where you can put a promotion on different destinations to publicize your own. SEO

    ReplyDelete
  61. 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
  62. Please give some advice on how to achieve this kind of posts. air conditioning service Berwick

    ReplyDelete
  63. 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
  64. 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
  65. 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
  66. 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
  67. 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
  68. 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
  69. The article posted was very informative and useful. You people are doing a great job. Keep going. Buy Youtube Views India

    ReplyDelete
  70. 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
  71. 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
  72. 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
  73. 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
  74. Thank you so much for sharing this great blog.Very inspiring and helpful too.Hope you continue to share more of your ideas.I will definitely love to read. creare sito web

    ReplyDelete
  75. 훌륭한 기사. 읽기에 매우 흥미 롭습니다. 나는 정말 좋은 기사를 읽는 것을 좋아합니다. 감사! 계속 흔들어. 먹튀커뮤니티

    ReplyDelete
  76. The lone issue with this administration is the point at which the link goes out, so does your web. Link organizations are not dependable ISP's with regards to your association. You are regularly knock off of the web by static in the link line or the refreshing of the link administration that accompanies the link administration. internet

    ReplyDelete
  77. I know this is one of the most meaningful information for me. And I'm animated reading your article. But should remark on some general things, the website style is perfect; the articles are great. Thanks for the ton of tangible and attainable help. increase dr

    ReplyDelete
  78. I truly like you're composing style, incredible data, thankyou for posting. 토토사이트

    ReplyDelete
  79. Interesting blog I must say, the facts and information written here are accurate. black leather motorcycle jacket  토토사이트

    ReplyDelete
  80. After research a couple of of the weblog posts in your website now, and I really like your method of blogging. I bookmarked it to my bookmark website record and will probably be checking back soon. Pls check out my site as effectively and let me know what you think. Computers & Accessories 먹튀검증업체

    ReplyDelete
  81. Regardless of whether you need assistance generating traffic to some big advanced site or just easy methods to use
    professional designer logos

    ReplyDelete
  82. The company has a good focus, the right mentality, and an effective development methodology.
    companies designers

    ReplyDelete
  83. Fact 4 : Most SEO companies get paid whether or not your site gets any rankings Webdesign Kortessem

    ReplyDelete
  84. It is quite beneficial, although we think about the facts when it reaches this target dentist in tijuana mexico

    ReplyDelete
  85. We face a daily reality such that greater is better and this is absolutely plain to see with the uber store. A one quit shopping Valhalla; these retail goliaths have fundamentally cornered the market with regards to our shopping needs. check over here

    ReplyDelete
  86. The website is looking bit flashy and it catches the visitors eyes. Design is pretty simple and a good user friendly interface. 성인

    ReplyDelete
  87. 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. open source voip pbx

    ReplyDelete
  88. Realizing your SEO Organization and doing your own examination on how appropriate SEO is done is incredibly essential in picking the ideal firm. https://www.webdesign-seo-limburg.be/

    ReplyDelete
  89. Your Post is very informative. If you are looking for the new vape subscription box then you must get to our company site and check out new box styles to pack your products in them.


    ReplyDelete
  90. Slow fiber WiFi internet can be incredibly frustrating except that it is worth knowing that it is the speed of your broadband service does not only depend on the package you've signed up for and your Internet Service Provider (ISP) provider but There could be other several reasons why your internet connection might be loading up slowly. 192.168.1.254

    ReplyDelete
  91. Social media marketing, the new tool in the business arena has shown a promising rise, the recent years. The web world is going gaga over it & Social Media has shown a 100% higher lead-to-close rate than outbound marketing. Today nearly 84% of B2B marketers use social media in some form or the other. No matter what you sell and who your target audience is,, using social media as a marketing tool is a sure shot way to you grow your brand! coupon code discount

    ReplyDelete
  92. In different terms, a gathering of understudies disdain innovation since certain educators are not careful about innovation use or they need extra preparing. For instance, a few understudies are disappointed on the grounds that they feel that teachers midsection their time when they are not appropriately prepared to utilize the innovative instruments.TutuApp web

    ReplyDelete
  93. Social media... it's all the rage today! It is increasingly becoming a more integral part of people's everyday lives, especially now that people can carry the world in their pockets with cell phones that are Web enabled and have data packages. 토토먹튀사이트

    ReplyDelete
  94. I feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it. 먹튀폴리스

    ReplyDelete
  95. Thank you again for all the knowledge you distribute,Good post. I was very interested in the article, it's quite inspiring I should admit. I like visiting you site since I always come across interesting articles like this one.Great Job, I greatly appreciate that.Do Keep sharing! Regards 토토사이트

    ReplyDelete
  96. I read that Post and got it fine and informative 메이저놀이터

    ReplyDelete
  97. A debt of gratitude is in order for another magnificent post. Where else would anybody be able to get that kind of data in such a perfect method for composing? 안전놀이터추천

    ReplyDelete
  98. I use only high quality materials - you can see them at 먹튀폴리스

    ReplyDelete
  99. This was among the best posts and episode from your team it let me learn many new things 먹튀검증커뮤니티

    ReplyDelete
  100. Thanks for taking the time to discuss this, I feel strongly about it and love learning more on this topic. 안전놀이터

    ReplyDelete
  101. Nice blog. Found this while searching through 먹튀검증업체

    ReplyDelete
  102. Mükemmel ve çok heyecan verici bir site. İzlemeyi seviyorum. Eğlenmeye devam et. 안전놀이터

    ReplyDelete
  103. These are some incredible apparatuses that i unquestionably use for SEO work. This is an awesome rundown to use later on.. 안전놀이터

    ReplyDelete
  104. You need to take part in a tournament personally of the most useful blogs on the internet. I am going to recommend this page! 먹튀검증

    ReplyDelete
  105. 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 토토사이트

    ReplyDelete
  106. Custom Printing Services is one of the Best Packaging Printing Solution for Custom Boxes and Custom Packaging that fit your budget. Order confidentially with no set-up and die-cut charges.Buy Packaging Boxes Buy Packaging Boxes

    ReplyDelete
  107. Major thankies for the post.Really thank you! Really Great. 먹튀검증

    ReplyDelete
  108. This is a very interesting writing. Thank you for posting a lot of good comments.

    ReplyDelete