comparing ip and subnet (ipv4 and ipv6) - mysql How can I check if an ipaddress is part of a subnet, given ipaddress can be ipv4 or ipv6?

I currently have it working for ipv4 only:

set @ip = '1.1.1.1';
set @subnet = '1.1.1.0/32';
set @xor_bw_ip_n_subnet = inet_aton(@ip) ^ inet_aton(substring_index(@subnet, '/', 1));
select ((@xor_bw_ip_n_subnet) &gt;&gt; 32-substring_index(@subnet,'/',-1)) = 0;   -- if ip in subnet, then should be 0


For ipv6, how can I use inet6_ntoa/inet6_aton to check if IPaddress is part of a subnet (basically trying to do bitwise operation on ipv6 address)?

I am using mysql v5.6.19

Thanks,
I assume your IPv6 is in BINARY(16)?  Build a 128-row table of the BINARY(16) values for the subnet masks.  (/128 is all 1's.)  JOIN to the table and do masking on each half of the address --

WHERE (LEFT(ip, 8)  &amp; LEFT(mask, 8))  == LEFT(ip, 8)
  AND (RIGHT(ip, 8) &amp; RIGHT(mask, 8)) == RIGHT(ip, 8)


(No shifting.)