Re: Best Fieldtype to store IP address...

[Evan Borgstrom]

	Yep, I know int(10) is just a display width... it's habit, probably a
bad one. And you're right about it being backwards, I used bc and the
top of my head to come up with that, I should've mentioned it in the email.

-Evan

Michael Stassen wrote:
> Cory @ SkyVantage wrote:
>> I'm using MySQL-Cluster 5.0, and we're doing some research.
>> What is everyone's opinion as to what the best fieldtype to store an IP
>> address in?
>>
>> varchar(16)  ?  because 16 is the max chars of an ip address...
>> char(16) ?
>> text(16)
>>
>> Not quite sure how to get the best memory utilization...
> 
> None of the above.
> 
> Peter M. Groen wrote:
>> How about:
>>
>>     Field 1: First octet (int)
>>     Field 2: Second octet (int)
>>     Field 3: Third octet (int)
>>     Field 4: Fourth octet (int)
>>
>> Searching takes less time like this, I reckon..
> 
> Too complicated.
> 
> Evan Borgstrom wrote:
>>     The same way the kernel deals with them; int(10) unsigned. To
> convert a
>> dotted quad string into int(10) use the following:
> 
> Right, use an INT UNSIGNED.  (You do know that the (10) in yourr
> definition is just a display width, right?)
> 
>> Using 192.168.10.50:
>> 192 + (168 * 2^8) + (10 * 2^16) + (50 * 2^24) = 839559360
> 
> You have that backwards.  839559360 is 50.10.168.192.  Which is why it
> is safer as well as easier to use the built-in functions INET_ATON() and
> INET_NTOA().
> 
>   mysql> SELECT INET_ATON('192.168.10.50');
>   +----------------------------+
>   | INET_ATON('192.168.10.50') |
>   +----------------------------+
>   |                 3232238130 |
>   +----------------------------+
>   1 row in set (0.00 sec)
> 
>   mysql> SELECT INET_NTOA(839559360);
>   +----------------------+
>   | INET_NTOA(839559360) |
>   +----------------------+
>   | 50.10.168.192        |
>   +----------------------+
>   1 row in set (0.00 sec)
> 
> See the manual for details
> <http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html>.
> 
>>     This is real handy if you're doing low level socket stuff and storing
>> addresses in the database.
> 
> Even if you're not doing "low level socket stuff", storing IPs as INTs
> is the right solution.  It takes less space than storing as strings, and
> lookups are faster because integer comparisons are faster than string
> comparisons.
> 
> Michael