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.
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!
--
Magnus Holmgren [EMAIL PROTECTED]
(No Cc of list mail needed, thanks)
pgpGDVCiwPODi.pgp
Description: PGP signature
-- ## 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/
