Hi. I've got IP addresses stored in the database as packed binaries.
i.e. binary(4). I put them in there that way so that I could do bitwise
logic on them to do subnet searches. e.g. ...WHERE `ip` & 'mask' =
'network'. Only it turns out that as far as I can tell MySQL can't do
bit logic on strings, only integers. I came up with an onerous SQL call
that eats the 4 bytes and makes an integer out of them but there's got
to be a better way. The worst case is that I redo the database to have
all the IP's as integers.

Anybody know of a way to make MySQL either do the bit logic on the
string or convert/cast the string as an integer? Thanks.


--
Chris Wagner
CBTS
GE Aircraft Engines
Chris.Wagner@ae.ge.com

Those functions concern dotted quad IP addresses, not packed binaries.

Anybody know of some obscure MySQL functions to do bit logic on strings
or get MySQL to recognize a char sequence as an integer?

"Gerald L. Clark" wrote:
>
> Wagner, Chris (GEAE, CBTS) wrote:
> > Hi. I've got IP addresses stored in the database as packed binaries.
> > i.e. binary(4). I put them in there that way so that I could do bitwise
> > logic on them to do subnet searches. e.g. ...WHERE `ip` & 'mask' =
> > 'network'. Only it turns out that as far as I can tell MySQL can't do
> > bit logic on strings, only integers. I came up with an onerous SQL call
> > that eats the 4 bytes and makes an integer out of them but there's got
> > to be a better way. The worst case is that I redo the database to have
> > all the IP's as integers.
> >
> > Anybody know of a way to make MySQL either do the bit logic on the
> > string or convert/cast the string as an integer? Thanks.
> >
> >
> Try INET_ATON() and INET_NTOA().
>
> --
> Gerald L. Clark
> Supplier Systems Corporation

--
Chris Wagner
CBTS
GE Aircraft Engines
Chris.Wagner@ae.ge.com

I think Gerald was suggesting you convert the IP's into integers and
then do bitwise stuff on them. I don't know of a way to do what you're
asking.

Baron

Wagner, Chris (GEAE, CBTS) wrote:
> Those functions concern dotted quad IP addresses, not packed binaries.
>
> Anybody know of some obscure MySQL functions to do bit logic on strings
> or get MySQL to recognize a char sequence as an integer?
>
> "Gerald L. Clark" wrote:
>> Wagner, Chris (GEAE, CBTS) wrote:
>>> Hi. I've got IP addresses stored in the database as packed binaries.
>>> i.e. binary(4). I put them in there that way so that I could do bitwise
>>> logic on them to do subnet searches. e.g. ...WHERE `ip` & 'mask' =
>>> 'network'. Only it turns out that as far as I can tell MySQL can't do
>>> bit logic on strings, only integers. I came up with an onerous SQL call
>>> that eats the 4 bytes and makes an integer out of them but there's got
>>> to be a better way. The worst case is that I redo the database to have
>>> all the IP's as integers.
>>>
>>> Anybody know of a way to make MySQL either do the bit logic on the
>>> string or convert/cast the string as an integer? Thanks.
>>>
>>>
>> Try INET_ATON() and INET_NTOA().
>>
>> --
>> Gerald L. Clark
>> Supplier Systems Corporation
>

--
Baron Schwartz
Xaprb LLC
http://www.xaprb.com/

Hi,

You can actually unpack them using some fairly cryptic stuff. I would
only use this to unpack them once to re-store them as INT. Here's an
example:

CREATE TABLE ip (packed CHAR(4));
INSERT INTO ip (packed) VALUES (0xB16212C);

mysql> SELECT * FROM ip;
+--------+
| packed |
+--------+
|
!, |
+--------+
1 row in set (0.00 sec)

mysql> SELECT
-> INET_NTOA(
-> (ord(substring(packed, 1, 1)) << 24) +
-> (ord(substring(packed, 2, 2)) << 16) +
-> (ord(substring(packed, 3, 3)) << 8) +
-> (ord(substring(packed, 4, 4)))
-> ) AS unpacked
-> FROM ip;
+-------------+
| unpacked |
+-------------+
| 11.22.33.44 |
+-------------+
1 row in set (0.00 sec)

Regards,

Jeremy

Baron Schwartz wrote:
> I think Gerald was suggesting you convert the IP's into integers and
> then do bitwise stuff on them. I don't know of a way to do what you're
> asking.
>
> Baron
>
> Wagner, Chris (GEAE, CBTS) wrote:
>> Those functions concern dotted quad IP addresses, not packed binaries.
>>
>> Anybody know of some obscure MySQL functions to do bit logic on strings
>> or get MySQL to recognize a char sequence as an integer?
>>
>> "Gerald L. Clark" wrote:
>>> Wagner, Chris (GEAE, CBTS) wrote:
>>>> Hi. I've got IP addresses stored in the database as packed binaries.
>>>> i.e. binary(4). I put them in there that way so that I could do bitwise
>>>> logic on them to do subnet searches. e.g. ...WHERE `ip` & 'mask' =
>>>> 'network'. Only it turns out that as far as I can tell MySQL can't do
>>>> bit logic on strings, only integers. I came up with an onerous SQL call
>>>> that eats the 4 bytes and makes an integer out of them but there's got
>>>> to be a better way. The worst case is that I redo the database to have
>>>> all the IP's as integers.
>>>>
>>>> Anybody know of a way to make MySQL either do the bit logic on the
>>>> string or convert/cast the string as an integer? Thanks.
>>>>
>>>>
>>> Try INET_ATON() and INET_NTOA().
>>>
>>> --
>>> Gerald L. Clark
>>> Supplier Systems Corporation
>

--
high performance mysql consulting
www.provenscaling.com