Using MySQL Bitwise operators to get the network portion of an IP Address

I like to store IP addresses in a database as unsigned 32-bit integers. It is efficient and elegant and is just fun to work with.

I recently had somebody scraping a site using a range of IP addresses from the same subnet to get around our bot-detection which worked at an individual IP level. It was a pretty simple change to summarize that at the network level. Just do a bitwise AND with 4294967040 to mask out the last octet

The number ‘4294967040’ came by calculating 2^32 – 2^8 so the binary number has all ones in the first 24 bits and all zeros in the last 8 bits.

  SELECT  ip, INET_NTOA(ip&4294967040) AS ipaddr, 
  FROM some_table
  GROUP BY ipaddr

Now those poor fools at 208.86.255.0/24 will be getting cached data from who-knows-when and they won’t even know it.