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 holmgren@???
(No Cc of list mail needed, thanks)
