comparing ip and subnet (ipv4 and ipv6) - mysql
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.)