Re: Best Fieldtype to store IP address...
Jasper Bryant-Greene:
Rhino wrote:
Isn't there a new way to express IP addresses called IPV6(?) which has a
possibility of 6 distinct parts instead of the traditional 4? I haven't
seen one of these new formats myself yet but for all I know, they will
become soon in the near future. Maybe you'd better choose a field type
that can accomodate those as well as the traditional 123.123.123.123
type
It's not a new way to express IP addresses. It's a new version of IP,
the Internet Protocol, and theoretically if the OP follows the good
advice already given and stores the IP address in an unsigned integer
field, he should be fine (assuming, probably safely, that
INET_{ATON,NTOA}6() functions are made); although he may need a bigger
integer type for IPv6 addresses.
An IPv6 address is 128 bits. That's twice as big as a MySQL bigint, so you
can't store it as an integer. Use binary(16).
Besides, you don't want to mix IPv4 addresses and IPv6 addresses in the same
column unless you have another column that keeps track of which kind of
address it is, but I seem to recall that there is a standard mapping from
IPv4 addresses to IPv6 addresses, so you could use that and store them all as
IPv6 addresses.
Björn Persson
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best Fieldtype to store IP address...
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... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best Fieldtype to store IP address...
On Friday 11 November 2005 21:33, 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... How about: Field 1: First octet (int) Field 2: Second octet (int) Field 3: Third octet (int) Field 4: Fourth octet (int) Fester. Searching takes less time like this, I reckogn.. Fester -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best Fieldtype to store IP address...
The same way the kernel deals with them; int(10) unsigned. To convert a dotted quad string into int(10) use the following: Using 192.168.10.50: 192 + (168 * 2^8) + (10 * 2^16) + (50 * 2^24) = 839559360 This is real handy if you're doing low level socket stuff and storing addresses in the database. -Evan 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... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best Fieldtype to store IP address...
varchar(16) ? because 16 is the max chars of an ip address... char(16) ? text(16) Assuming you want it as text for easy searches, char(16). In 5.0 varchar(16) just uses unnecessary extra 4 bytes. Even in 5.1 (which has true varchar) I'd use char(16). text(16) is a blob and uses 8+256 bytes in 5.0. -- Pekka Nousiainen, Software Engineer MySQL AB, www.mysql.com [EMAIL PROTECTED] +46 (0) 73 068 4978 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best Fieldtype to store IP address...
Peter M. Groen wrote: On Friday 11 November 2005 21:33, 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... How about: Field 1: First octet (int) Field 2: Second octet (int) Field 3: Third octet (int) Field 4: Fourth octet (int) Nah. One field, unsigned int type. Use the inet_aton and inet_ntoa functions to convert the dotted decimal notation to and from a 4-byte number. That will stop you inserting invalid IP addresses like 192.168.329.243, and it means you can do cool things like: select ip from ip_table where ip between inet_aton(192.10.0.0) and inet_aton(192.10.255.255) Make sure you use an UNSIGNED INT column to store the number. http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#id2720025 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Best Fieldtype to store IP address...
You could also use the built in functions INET_ATON and INET_NTOA documented at
http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html
These will do the work for you
Regards
#
INET_ATON(expr)
Given the dotted-quad representation of a network address as a string, returns
an integer that represents the numeric value of the address. Addresses may be
4- or 8-byte addresses.
mysql SELECT INET_ATON('209.207.224.40');
- 3520061480
The generated number is always in network byte order. For the example just
shown, the number is calculated as 209×2563 + 207×2562 + 224×256 + 40.
INET_ATON() also understands short-form IP addresses:
mysql SELECT INET_ATON('127.0.0.1'), INET_ATON('127.1');
- 2130706433, 2130706433
NOTE: When storing values generated by INET_ATON(), it is recommended that you
use an INT UNSIGNED column. If you use a (signed) INT column, then values
corresponding to IP addresses for which the first octet is greater than 127
will be truncated to 2147483647 (that is, the value returned by
INET_ATON('127.255.255.255')). See Section 11.2, Numeric Types.
#
INET_NTOA(expr)
Given a numeric network address (4 or 8 byte), returns the dotted-quad
representation of the address as a string.
mysql SELECT INET_NTOA(3520061480);
- '209.207.224.40'
David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia
+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax
-Original Message-
From: Evan Borgstrom [mailto:[EMAIL PROTECTED]
Sent: Saturday, 12 November 2005 7:13 AM
To: Cory @ SkyVantage
Cc: [EMAIL PROTECTED]; MySql; [EMAIL PROTECTED]
Subject: Re: Best Fieldtype to store IP address...
The same way the kernel deals with them; int(10) unsigned. To convert a
dotted quad string into int(10) use the following:
Using 192.168.10.50:
192 + (168 * 2^8) + (10 * 2^16) + (50 * 2^24) = 839559360
This is real handy if you're doing low level socket stuff and storing
addresses in the database.
-Evan
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...
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best Fieldtype to store IP address...
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: Using 192.168.10.50: 192 + (168 * 2^8) + (10 * 2^16) + (50 * 2^24) = 839559360 This is real handy if you're doing low level socket stuff and storing addresses in the database. -Evan This is how I've stored IP information in the past. It also makes it much easier to sort by IP, etc. However I believe you mean (192 * 2^24) + (168 * 2^16) + (10 * 2^8) + 50 = 3232238130 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best Fieldtype to store IP address...
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best Fieldtype to store IP address...
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best Fieldtype to store IP address...
- Original Message - From: Peter M. Groen [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, November 11, 2005 3:37 PM Subject: Re: Best Fieldtype to store IP address... On Friday 11 November 2005 21:33, 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... Isn't there a new way to express IP addresses called IPV6(?) which has a possibility of 6 distinct parts instead of the traditional 4? I haven't seen one of these new formats myself yet but for all I know, they will become soon in the near future. Maybe you'd better choose a field type that can accomodate those as well as the traditional 123.123.123.123 type Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.12.8/165 - Release Date: 09/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best Fieldtype to store IP address...
On Fri, Nov 11, 2005 at 11:51:52PM -0500, Rhino wrote: Isn't there a new way to express IP addresses called IPV6(?) which has a possibility of 6 distinct parts instead of the traditional 4? I haven't seen one of these new formats myself yet but for all I know, they will become soon in the near future. Maybe you'd better choose a field type that can accomodate those as well as the traditional 123.123.123.123 type I believe both 14v6 and ipv4 can be expressed as decimal numbers too. -Jason Martin -- This Charlie Brown must have been a very wise man. This message is PGP/MIME signed. pgp8sR0zW0B48.pgp Description: PGP signature
Re: Best Fieldtype to store IP address...
Rhino wrote:
Isn't there a new way to express IP addresses called IPV6(?) which has a
possibility of 6 distinct parts instead of the traditional 4? I haven't seen
one of these new formats myself yet but for all I know, they will become
soon in the near future. Maybe you'd better choose a field type that can
accomodate those as well as the traditional 123.123.123.123 type
It's not a new way to express IP addresses. It's a new version of IP,
the Internet Protocol, and theoretically if the OP follows the good
advice already given and stores the IP address in an unsigned integer
field, he should be fine (assuming, probably safely, that
INET_{ATON,NTOA}6() functions are made); although he may need a bigger
integer type for IPv6 addresses.
Jasper
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
