IP regex

I'm sure this is very simple, I just can't seem to learn Regexes:

I have been given the chore of taking a DB table of client records,
which has a field called IP address. Whenever a client logs onto our
site, I'm supposed to display the IP address they logged on from THE
LAST TIME they visited, as a security measure.

This field is a MySQl varchar(30). Since this field was added at table
creation, but not used until recently, many of the fields contain NULLs
or junk text.

Can someone point me in the direction of creating REGEX to test whether
an IP address is valid. (Valid in the sense of syntax i.e.
192.168.24.20 is valid, but
277.22.49.75, or 65.23 is not)?

Thanks in advance

Gregg Allen


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: IP regex

Here's a quick and dirt way:

use strict;

my $ip = '0.0.0.0.0';

my @ips = split(/\./,$ip);

my $classes = @ips;

if ($classes != 5) {
print "IP not valid\n";
}
else {
print "IP Valid\n";
}

Greg

--- "Gregg R.Allen" wrote:

> I'm sure this is very simple, I just can't seem to learn Regexes:
>
> I have been given the chore of taking a DB table of client records,
> which has a field called IP address. Whenever a client logs onto our
> site, I'm supposed to display the IP address they logged on from THE
> LAST TIME they visited, as a security measure.
>
> This field is a MySQl varchar(30). Since this field was added at table
> creation, but not used until recently, many of the fields contain NULLs
> or junk text.
>
> Can someone point me in the direction of creating REGEX to test whether
> an IP address is valid. (Valid in the sense of syntax i.e.
> 192.168.24.20 is valid, but
> 277.22.49.75, or 65.23 is not)?
>
> Thanks in advance
>
> Gregg Allen
>
>
> --
> MySQL Perl Mailing List
> For list archives: http://lists.mysql.com/perl
> To unsubscribe: http://lists.mysql.com/perl?unsub=gregmeckes@yahoo.com
>
>




__________________________________
Do you Yahoo!?
Take Yahoo! Mail with you! Get it on your mobile phone.
http://mobile.yahoo.com/maildemo

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

RE: IP regex

------_=_NextPart_001_01C4BF01.3301E598
Content-Type: text/plain

SELECT columnName FROM tableName WHERE columnName REGEXP
'^[0-9]{3}[.][0-9]{3}[.][0-9]{2}[.][0-9]{2}$';

Explanation:

Substitute columnName with the name of the field holding the IP Address
Substitute tableName with the name of the table holding the IP Addresses.

REGEXP - means use regular expression

^ means start matching at the beginning of the line.
[0-9] means match any number 0-9
{3}|{2} means match 3|2 instances of the preceding element
[.] means match the . character as opposed to the
regular expression meaning of . - which is any character
This prevents something like 1234567890123 from
being accepted as valid.
The 4th, 8th and 11th characters must be a .
$ means stop matching anything else.

The above REGEXP matches 123.123.12.12 but not 1234123812912.

Basically,
Starting at the front of the value match:
3 digits
followed by a period
followed by 3 digits
followed by a period
followed by 2 digits
followed by a period
followed by 2 digits
Ends there.

mysql> select '1231123112112' regexp
"^[0-9]{3}[.][0-9]{3}[.][0-9]{2}[.][0-9]{2}";
+----------------------------------------------------------- ----------+
| '1231123112112' regexp "^[0-9]{3}[.][0-9]{3}[.][0-9]{2}[.][0-9]{2}" |
+----------------------------------------------------------- ----------+
| 0 |
+----------------------------------------------------------- ----------+
1 row in set (0.00 sec)

mysql> select '123.123.12.12' regexp
"^[0-9]{3}[.][0-9]{3}[.][0-9]{2}[.][0-9]{2}";
+----------------------------------------------------------- ----------+
| '123.123.12.12' regexp "^[0-9]{3}[.][0-9]{3}[.][0-9]{2}[.][0-9]{2}" |
+----------------------------------------------------------- ----------+
| 1 |
+----------------------------------------------------------- ----------+
1 row in set (0.00 sec)

-Gui


-----Original Message-----
From: Greg Meckes [mailto:gregmeckes@yahoo.com
]
Sent: Saturday, October 30, 2004 11:53 PM
To: Gregg R.Allen; perl@lists.mysql.com
Subject: Re: IP regex


Here's a quick and dirt way:

use strict;

my $ip = '0.0.0.0.0';

my @ips = split(/\./,$ip);

my $classes = @ips;

if ($classes != 5) {
print "IP not valid\n";
}
else {
print "IP Valid\n";
}

Greg

--- "Gregg R.Allen" wrote:

> I'm sure this is very simple, I just can't seem to learn Regexes:
>
> I have been given the chore of taking a DB table of client records,
> which has a field called IP address. Whenever a client logs onto our
> site, I'm supposed to display the IP address they logged on from THE
> LAST TIME they visited, as a security measure.
>
> This field is a MySQl varchar(30). Since this field was added at table
> creation, but not used until recently, many of the fields contain NULLs
> or junk text.
>
> Can someone point me in the direction of creating REGEX to test
> whether
> an IP address is valid. (Valid in the sense of syntax i.e.
> 192.168.24.20 is valid, but
> 277.22.49.75, or 65.23 is not)?
>
> Thanks in advance
>
> Gregg Allen
>
>
> --
> MySQL Perl Mailing List
> For list archives: http://lists.mysql.com/perl

> To unsubscribe: http://lists.mysql.com/perl?unsub=gregmeckes@yahoo.com

>
>




__________________________________
Do you Yahoo!?
Take Yahoo! Mail with you! Get it on your mobile phone.
http://mobile.yahoo.com/maildemo

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl

To unsubscribe:
http://lists.mysql.com/perl?unsub=guillermo.ramos@ny.email.g s.com


------_=_NextPart_001_01C4BF01.3301E598--

Re: IP regex

--=-iTvm72ycw6uSmjFAdeR4
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

You could use the MySQL function, INET_ATON() to check for a valid
address. This first statement below produces a valid number or string.
The second statement below returns NULL because the IP address given
isn't valid.
SELECT INET_ATON('192.168.24.20');
SELECT INET_ATON('277.22.49.75');

Based on this, you could write a Perl script with error checking; have
it check for NULL or not. Actually, you could put the field for the IP
address inside the function of the SQL statement:

SELECT INET_ATON(ip_address_column);






> --- "Gregg R.Allen" wrote:
>
> > I'm sure this is very simple, I just can't seem to learn Regexes:
> >
> > I have been given the chore of taking a DB table of client records,
> > which has a field called IP address. Whenever a client logs onto our
> > site, I'm supposed to display the IP address they logged on from THE
> > LAST TIME they visited, as a security measure.
> >
> > This field is a MySQl varchar(30). Since this field was added at table
> > creation, but not used until recently, many of the fields contain NULLs
> > or junk text.
> >
> > Can someone point me in the direction of creating REGEX to test whether
> > an IP address is valid. (Valid in the sense of syntax i.e.
> > 192.168.24.20 is valid, but
> > 277.22.49.75, or 65.23 is not)?
> >
> > Thanks in advance
> >
> > Gregg Allen
> >
> >
> > --
> > MySQL Perl Mailing List
> > For list archives: http://lists.mysql.com/perl
> > To unsubscribe: http://lists.mysql.com/perl?unsub=gregmeckes@yahoo.com
> >
> >
>
>
>
>
> __________________________________
> Do you Yahoo!?
> Take Yahoo! Mail with you! Get it on your mobile phone.
> http://mobile.yahoo.com/maildemo

--=-iTvm72ycw6uSmjFAdeR4--

Re: IP regex

That's not quite right, since the fields are all variable length -
1.1.1.1 and 222.222.111.144 are both valid IPs. I'd stick with
INET_ATON();

On Oct 30, 2004, at 9:22 PM, Ramos, Guillermo wrote:

> SELECT columnName FROM tableName WHERE columnName REGEXP
> '^[0-9]{3}[.][0-9]{3}[.][0-9]{2}[.][0-9]{2}$';
>
> Explanation:
>
> Substitute columnName with the name of the field holding the IP Address
> Substitute tableName with the name of the table holding the IP
> Addresses.
>
> REGEXP - means use regular expression
>
> ^ means start matching at the beginning of the line.
> [0-9] means match any number 0-9
> {3}|{2} means match 3|2 instances of the preceding element
> [.] means match the . character as opposed to the
> regular expression meaning of . - which is any character
> This prevents something like 1234567890123 from
> being accepted as valid.
> The 4th, 8th and 11th characters must be a .
> $ means stop matching anything else.
>
> The above REGEXP matches 123.123.12.12 but not 1234123812912.
>
> Basically,
> Starting at the front of the value match:
> 3 digits
> followed by a period
> followed by 3 digits
> followed by a period
> followed by 2 digits
> followed by a period
> followed by 2 digits
> Ends there.
>
> mysql> select '1231123112112' regexp
> "^[0-9]{3}[.][0-9]{3}[.][0-9]{2}[.][0-9]{2}";
> +----------------------------------------------------------- ----------+
> | '1231123112112' regexp "^[0-9]{3}[.][0-9]{3}[.][0-9]{2}[.][0-9]{2}" |
> +----------------------------------------------------------- ----------+
> | 0 |
> +----------------------------------------------------------- ----------+
> 1 row in set (0.00 sec)
>
> mysql> select '123.123.12.12' regexp
> "^[0-9]{3}[.][0-9]{3}[.][0-9]{2}[.][0-9]{2}";
> +----------------------------------------------------------- ----------+
> | '123.123.12.12' regexp "^[0-9]{3}[.][0-9]{3}[.][0-9]{2}[.][0-9]{2}" |
> +----------------------------------------------------------- ----------+
> | 1 |
> +----------------------------------------------------------- ----------+
> 1 row in set (0.00 sec)
>
> -Gui
>
>
> -----Original Message-----
> From: Greg Meckes [mailto:gregmeckes@yahoo.com
>
> ]
> Sent: Saturday, October 30, 2004 11:53 PM
> To: Gregg R.Allen; perl@lists.mysql.com
> Subject: Re: IP regex
>
>
> Here's a quick and dirt way:
>
> use strict;
>
> my $ip = '0.0.0.0.0';
>
> my @ips = split(/\./,$ip);
>
> my $classes = @ips;
>
> if ($classes != 5) {
> print "IP not valid\n";
> }
> else {
> print "IP Valid\n";
> }
>
> Greg
>
> --- "Gregg R.Allen" wrote:
>
>> I'm sure this is very simple, I just can't seem to learn Regexes:
>>
>> I have been given the chore of taking a DB table of client records,
>> which has a field called IP address. Whenever a client logs onto our
>> site, I'm supposed to display the IP address they logged on from THE
>> LAST TIME they visited, as a security measure.
>>
>> This field is a MySQl varchar(30). Since this field was added at table
>> creation, but not used until recently, many of the fields contain
>> NULLs
>> or junk text.
>>
>> Can someone point me in the direction of creating REGEX to test
>> whether
>> an IP address is valid. (Valid in the sense of syntax i.e.
>> 192.168.24.20 is valid, but
>> 277.22.49.75, or 65.23 is not)?
>>
>> Thanks in advance
>>
>> Gregg Allen
>>
>>
>> --
>> MySQL Perl Mailing List
>> For list archives: http://lists.mysql.com/perl
>
>> To unsubscribe:
>> http://lists.mysql.com/perl?unsub=gregmeckes@yahoo.com
>
>>
>>
>
>
>
>
> __________________________________
> Do you Yahoo!?
> Take Yahoo! Mail with you! Get it on your mobile phone.
> http://mobile.yahoo.com/maildemo
>
> --
> MySQL Perl Mailing List
> For list archives: http://lists.mysql.com/perl
>
>
> To unsubscribe:
> http://lists.mysql.com/perl?unsub=guillermo.ramos@ny.email.g s.com
>


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: IP regex

I don't believe a regular expression is the right way to go here. (Note
that neither of the 2 regex solutions previously given do what you want --
they would both accept 999.999.00.00 as valid, for example.) You could
write a horribly complicated regular expression to do what you want, but it
would be hard to make sure you got it right. You'd also be reinventing the
wheel, as there are already functions designed to work with IPs.

Spenser is on the right track, but INET_ATON() does not always return NULL
for invalid IPs. Using Gregg's other example, INET_ATON('65.23') returns
1090519063, which is really 65.0.0.23. The trick is to use both INET_ATON()
and INET_NTOA(), because

ip = INET_NTOA(INET_ATON(ip))

only if ip is valid.

My advice: It makes no sense to keep IPs in a VARCHAR(30) column. (Even if
a string was the right way to go, you'd need at most 15 characters.) The
better way is to use an INT UNSIGNED. Then you store an IP using
INET_ATON(IP) and retrieve it with INET_NTOA(ip_column). That takes 4 bytes
instead of 8 to 16, and reduces the likelihood of garbage values in the
column. Thus, I'd suggest

ALTER TABLE clients ADD new_ip INT UNSIGNED;

UPDATE clients SET new_ip = INET_ATON(old_ip)
WHERE old_ip = INET_NTOA(INET_ATON(old_ip));

Sample results:

SELECT old_ip, INET_NTOA(new_ip) AS ip FROM clients;
+-----------------+-----------------+
| old_ip | ip |
+-----------------+-----------------+
| 192.168.24.20 | 192.168.24.20 |
| 255.255.255.255 | 255.255.255.255 |
| 65.23 | NULL |
| 277.22.49.75 | NULL |
| junk text | NULL |
| 192.168.0.7 | 192.168.0.7 |
+-----------------+-----------------+
6 rows in set (0.00 sec)

Then you could drop column old_ip and rename new_ip to old_ip. (Of course,
you'd change "clients" and "old_ip" to fit your actual table.) Going
forward, you should modify your apps to only store valid IPs in the first place.

Michael

Spenser wrote:
> You could use the MySQL function, INET_ATON() to check for a valid
> address. This first statement below produces a valid number or string.
> The second statement below returns NULL because the IP address given
> isn't valid.
> SELECT INET_ATON('192.168.24.20');
> SELECT INET_ATON('277.22.49.75');
>
> Based on this, you could write a Perl script with error checking; have
> it check for NULL or not. Actually, you could put the field for the IP
> address inside the function of the SQL statement:
>
> SELECT INET_ATON(ip_address_column);
>
>
>>--- "Gregg R.Allen" wrote:
>>
>>
>>>I'm sure this is very simple, I just can't seem to learn Regexes:
>>>
>>>I have been given the chore of taking a DB table of client records,
>>>which has a field called IP address. Whenever a client logs onto our
>>>site, I'm supposed to display the IP address they logged on from THE
>>>LAST TIME they visited, as a security measure.
>>>
>>>This field is a MySQl varchar(30). Since this field was added at table
>>>creation, but not used until recently, many of the fields contain NULLs
>>>or junk text.
>>>
>>>Can someone point me in the direction of creating REGEX to test whether
>>>an IP address is valid. (Valid in the sense of syntax i.e.
>>>192.168.24.20 is valid, but
>>>277.22.49.75, or 65.23 is not)?
>>>
>>>Thanks in advance
>>>
>>>Gregg Allen
>>>
>>>
>>>--
>>>MySQL Perl Mailing List
>>>For list archives: http://lists.mysql.com/perl
>>>To unsubscribe: http://lists.mysql.com/perl?unsub=gregmeckes@yahoo.com
>>>
>>>
>>
>>
>>
>>
>>__________________________________
>>Do you Yahoo!?
>>Take Yahoo! Mail with you! Get it on your mobile phone.
>>http://mobile.yahoo.com/maildemo
>
>

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: IP regex

On Sun, Oct 31, 2004 at 02:02:26PM -0500, Michael Stassen wrote:
> My advice: It makes no sense to keep IPs in a VARCHAR(30) column. (Even if
> a string was the right way to go, you'd need at most 15 characters.) The
> better way is to use an INT UNSIGNED. Then you store an IP using
> INET_ATON(IP) and retrieve it with INET_NTOA(ip_column). That takes 4
> bytes instead of 8 to 16, and reduces the likelihood of garbage values in

It's very common mistake, --
to think integer occupies in databases 4 bytes.
Please look DESC on your table, and you can find
it takes 11 bytes, because is stored in character form.

> >>>I have been given the chore of taking a DB table of client records,
> >>>which has a field called IP address. Whenever a client logs onto our
> >>>site, I'm supposed to display the IP address they logged on from THE
> >>>LAST TIME they visited, as a security measure.

> >>>Can someone point me in the direction of creating REGEX to test whether
> >>>an IP address is valid. (Valid in the sense of syntax i.e.
> >>>192.168.24.20 is valid, but
> >>>277.22.49.75, or 65.23 is not)?
> >>>Gregg Allen

Gregg, --

Why you need to check IP addr, anyway?
If it comes from apache cgi's $ENV{REMOTE_ADDR},
and user input does not affect on it,
what's the reason for regex-ing?


--w

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: IP regex

w@sfgate.com (Vladimir V. Kolpakov) writes:

> On Sun, Oct 31, 2004 at 02:02:26PM -0500, Michael Stassen wrote:
> > Then you store an IP using INET_ATON(IP) and retrieve it with
> > INET_NTOA(ip_column). That takes 4 bytes instead of 8 to 16
>=20
> It's very common mistake, -- to think integer occupies in
> databases 4 bytes. Please look DESC on your table, and you can
> find it takes 11 bytes, because is stored in character form.

You misunderstand the output of DESCRIBE. INT is stored in 4 bytes.
The "(11)" is the display width, which doesn't really affect anything
done through the perl interface.
--=20
Jamie McCarthy
http://mccarthy.vg/
jamie@mccarthy.vg

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

Re: IP regex

The storage requirements for each column type are documented in the manual
, which
confirms that INTs take 4 bytes.

Michael

Jamie McCarthy wrote:

> w@sfgate.com (Vladimir V. Kolpakov) writes:
>
>>On Sun, Oct 31, 2004 at 02:02:26PM -0500, Michael Stassen wrote:
>>
>>>Then you store an IP using INET_ATON(IP) and retrieve it with
>>>INET_NTOA(ip_column). That takes 4 bytes instead of 8 to 16
>>
>>It's very common mistake, -- to think integer occupies in
>>databases 4 bytes. Please look DESC on your table, and you can
>>find it takes 11 bytes, because is stored in character form.
>
> You misunderstand the output of DESCRIBE. INT is stored in 4 bytes.
> The "(11)" is the display width, which doesn't really affect anything
> done through the perl interface.

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: IP regex

On Sun, Oct 31, 2004 at 11:31:33PM -0500, Jamie McCarthy wrote:
> w@sfgate.com (Vladimir V. Kolpakov) writes:
> > On Sun, Oct 31, 2004 at 02:02:26PM -0500, Michael Stassen wrote:
> > > Then you store an IP using INET_ATON(IP) and retrieve it with
> > > INET_NTOA(ip_column). That takes 4 bytes instead of 8 to 16
> >
> > It's very common mistake, -- to think integer occupies in
> > databases 4 bytes. Please look DESC on your table, and you can
> > find it takes 11 bytes, because is stored in character form.
>
> You misunderstand the output of DESCRIBE. INT is stored in 4 bytes.
> The "(11)" is the display width, which doesn't really affect anything
> done through the perl interface.

It's true,... but only for field data either, -- unless index is not created.


--w

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: IP regex

Vladimir V. Kolpakov wrote:

> On Sun, Oct 31, 2004 at 11:31:33PM -0500, Jamie McCarthy wrote:
>
>>w@sfgate.com (Vladimir V. Kolpakov) writes:
>>
>>>On Sun, Oct 31, 2004 at 02:02:26PM -0500, Michael Stassen wrote:
>>>
>>>>Then you store an IP using INET_ATON(IP) and retrieve it with
>>>>INET_NTOA(ip_column). That takes 4 bytes instead of 8 to 16
>>>
>>>It's very common mistake, -- to think integer occupies in
>>>databases 4 bytes. Please look DESC on your table, and you can
>>>find it takes 11 bytes, because is stored in character form.
>>
>>You misunderstand the output of DESCRIBE. INT is stored in 4 bytes.
>>The "(11)" is the display width, which doesn't really affect anything
>>done through the perl interface.
>
>
> It's true,... but only for field data either, -- unless index is not created.
>
>
> --w

I don't understand. Are you saying that an INT column will be more than 4
bytes if you take its index into account? Well, yes, but the same will be
true for an indexed VARCHAR(15). Furthermore, an index on a VARCHAR(15)
will be larger than an index on an INT. Try it. You'll see that an index
on an INT column has key_len=5, and an index on a VARCHAR(15) has
key_len=16. Together, a VARCHAR(15) column + index is at least twice the
size of an INT column + index.

Michael


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: IP regex

On Mon, Nov 01, 2004 at 09:51:43AM -0500, Michael Stassen wrote:
> Vladimir V. Kolpakov wrote:
> >On Sun, Oct 31, 2004 at 11:31:33PM -0500, Jamie McCarthy wrote:
> >>w@sfgate.com (Vladimir V. Kolpakov) writes:
> >>>On Sun, Oct 31, 2004 at 02:02:26PM -0500, Michael Stassen wrote:
> >>>>Then you store an IP using INET_ATON(IP) and retrieve it with
> >>>>INET_NTOA(ip_column). That takes 4 bytes instead of 8 to 16

> I don't understand. Are you saying that an INT column will be more than 4
> bytes if you take its index into account? Well, yes, but the same will be
> true for an indexed VARCHAR(15). Furthermore, an index on a VARCHAR(15)
> will be larger than an index on an INT. Try it. You'll see that an index
> on an INT column has key_len=5, and an index on a VARCHAR(15) has
> key_len=16. Together, a VARCHAR(15) column + index is at least twice the
> size of an INT column + index.

Sorry for confusion,
my focus was slightly different: what's retrieval requirements are,
rather then storage requirements. In general they are opposite
in data schema design process.
IP address is not integer by it's nature and is not string also.
It's closer to bits string representation, which can involve
creation of bitmap index (or maked up with SET type),
but quite often can be effectively worked around using fulltex index.

Original author didn't respond on my question about usage
of IP address, so further discussion in that focus becomes meaningless.

As of "integer versus character" question,
I can also show example where storage requirements are equal
for both types,
thus virtually we can call those data whatever way we like.

open (MYSQL,'|mysql -utest test');
print MYSQL <<"EoD";
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (i INT(11) UNSIGNED);
CREATE INDEX i ON t1 (i);
DROP TABLE IF EXISTS t2;
CREATE TABLE t2 (c CHAR(4) BINARY);
CREATE INDEX c ON t2 (c);
EoD
for $i (1 .. 255) {
my $n = (($i * 256 + $i) * 256 + $i) * 256 + $i;
my $c = pack('CCCC',$i,$i,$i,$i);
print MYSQL "INSERT INTO t1 VALUES ($n);";
print MYSQL "INSERT INTO t2 VALUES ('$c');";
}
close MYSQL;
0;

--w

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: IP regex

In article <20041102054559.GA17567@sfgate.com>,
"Vladimir V. Kolpakov" writes:

> IP address is not integer by it's nature and is not string also.

Huh? An IPv4 address is a sequence of 32 bits. Although MySQL does
not have a column type for an IPv4 address, it does have support
functions for them, namely INET_ATON and INET_NTOA. Since these
functions operate on integers, what's wrong with using INT4?

> It's closer to bits string representation, which can involve
> creation of bitmap index (or maked up with SET type),
> but quite often can be effectively worked around using fulltex index.

If you're talking about indexed searching, I doubt there's anything
faster than

SELECT whatever FROM mytable WHERE ipv4 = INET_ATON(myinput)

with a normal index on the ipv4 column.

> As of "integer versus character" question,
> I can also show example where storage requirements are equal
> for both types,

Of course also CHAR(4) BINARY would be able to hold an IPv4 address,
but you gain nothing with that and lose the support functions.


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org