3 ms·
Also you could just store IPv4 as an unsigned INT directly, rather than strings in a different table and an unsigned INT as a foreign key.
by themgt 5y ago
Also you could just store IPv4 as an unsigned INT directly, rather than strings in a different table and an unsigned INT as a foreign key.
- tptacek 5y agoI have regretted every single time I've gotten cute about storing IP addresses as scalars in SQL.
- ryukafalz 5y agoWhat issues did you run into? Having admittedly never done this before, it feels like whenever you need the dotted-quad IP you could just query: SELECT INET_NTOA(ip) FROM ips WHERE... ...in MySQL at least. (Which might be why MySQL has this function built in?) I guess if you ever need to match on a specific prefix storing the numeric IP might be an issue?
- Ginden 5y ago> I guess if you ever need to match on a specific prefix storing the numeric IP might be an issue? If you want to search for range, you can do: SELECT INET_NTOA(ip) FROM ips WHERE ip > INET_NTOA(:startOfRange) AND ip < INET_NTOA(:endOfRange)
- soheil 5y agoBecause as shocking as this might sound like unlike the characters in the movie Matrix I don't have a built-in INET_NTOA function in my retina to see the string form of the IP addresses when glancing at table rows of a large table looking for patterns or a certain IP.
- ryukafalz 5y agoHow often are you looking at the full contents of a table vs. looking at the results of a query that you could throw an INET_NTOA onto?
- soheil 5y agoMany. You also expect me to remember how to spell INET_NTOA and on what fields to use it on. What if I wanted do a quick "SELECT * FROM"? I barely know how to spell English words what makes you think I'm going to remember how to spell INET_NTOA.
- Lammy 5y agoI realize you’re describing a general problem and not just this particular example, but fwiw NTOA and friends got a lot easier for me once I learned enough that I stopped trying to “remember how to spell” them and started thinking about them in terms of the underlying concepts, like “Network (as in big-endianness) to ASCII”. Learning the significance of the term “address family” was a big clue for me for self-directed learning in this space :)
- mixmastamyk 5y agodbeaver CE is free.