Mysql compare ip address

Question

I have a database which stores IP address with its location. I only store the first 3 network ID of IP address in the table.

+-------------------+-----------------+
|ip_address(varchar)|location(varchar)|
+-------------------+-----------------+
| 1.2.3             |       A         |
| 1.2.4             |       A         |
| 1.22.33           |       B         |
| 1.100.99          |       C         |
+-------------------+-----------------+

Let say my IP is 1.2.3.4 or others 1.100.99.20, how do I compare the IP with the table to get the location?

Answer 1

Here's a solution that should work and be indexable too:

SELECT location
FROM WhateverTable
WHERE ip_address = SUBSTRING_INDEX(?, '.', 3)

Where you supply the value '1.100.99.20' for the ? parameter.

---

You should be aware that the IP addresses you store are misleading. The official IP address format permits shortened strings, but not the way you probably think. For example:

mysql> select inet_ntoa(inet_aton('1.100.99'));
+----------------------------------+
| inet_ntoa(inet_aton('1.100.99')) |
+----------------------------------+
| 1.100.0.99                       |
+----------------------------------+

I bet you would expect it to return 1.100.99.0.