Magnus Holmgren wrote: > On Sunday 26 November 2006 04:58, Wakko Warner wrote: > > I noticed a couple months ago that someone wanted to know how to use CIDR > > notation with mysql. > > > > I came up with a nasty way of doing this. > > > > The following fragment can be used as a conditional expression. > > ((substring_index(@a,'.',1)<<24)|(substring_index(substring_index(@a,'.',2) > >,'.',-1)<<16)|(substring_index(substring_index(@a,'.',-2),'.',1)<<8)|substri > >ng_index(@a,'.',-1))&(~(4294967295>>substring_index(@t,'/',-1))&4294967295)= > >((substring_index(@t,'.',1)<<24)|(substring_index(substring_index(@t,'.',2), > >'.',-1)<<16)|(substring_index(substring_index(@t,'.',-2),'.',1)<<8)|substrin > >g_index(substring_index(@t,'.',-1),'/',1)) > > Urgh. MySQL has INET_ATON and INET_NTOA built-in since version 3.23.15. And > if > you want to store CIDR masks I think it's better to store that as a separate > field and do any splitting outside the database.
I looked for that for some time lastnight and never found it. That will definately simplify the query! Thanks. > So: > > INET_ATON(@a) & (-1 << (32 - @len)) = @t & (-1 << (32 - @len)) > > is my suggestion (assuming that the address to test is already stored in > binary form). > > This is strictly IPv4. Don't forget to make your applications IPv6 ready! I already knew it wasn't IPv6. I don't know much about it. I guess I'll have to come up with something else when the time comes. =) -- Lab tests show that use of micro$oft causes cancer in lab animals Got Gas??? -- ## List details at http://www.exim.org/mailman/listinfo/exim-users ## Exim details at http://www.exim.org/ ## Please use the Wiki with this list - http://www.exim.org/eximwiki/
