MySQL比较IP地址

Question

我有一个数据库，存储了IP地址及其位置。在表格中，我只存储了IP地址的前3个网络ID。

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

假设我的IP是1.2.3.4或其他的1.100.99.20，我该如何将IP与表格进行比较以获取位置？

- Amirul Fahmi

---

你是编造那些地址的吗？它看起来几乎像/23。这可以通过一些布尔掩码来解决。 - Rick James

Answer 1

这里有一个既可行又可被索引的解决方案：

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

当你为?参数提供值“1.100.99.20”时，请注意。

---

你应该意识到，你存储的IP地址是具有误导性的。官方的IP地址格式允许缩短字符串，但不是你可能想象的方式。例如：

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

我打赌你期望它返回1.100.99.0。

- Bill Karwin

---

谢谢您的回答。上面的IP地址我只是随意输入的，请不要在意。 - Amirul Fahmi

Answer 2

SELECT location
FROM address_table
WHERE AND INET_ATON(@my_address) BETWEEN INET_ATON(CONCAT(ip_address, '.0'))
                                     AND INET_ATON(CONCAT(ip_address, '.255'))

当然，即使索引存在，这个查询也无法使用索引...

---

我建议您不要修剪ip_address列的值。最好的方法是以数字形式在两列中存储未修剪的子网地址而不是修剪后的IP地址范围（从-到）- 这可以防止拼写错误（并减少行数）。如果需要，您可以使用生成的列来表示IP地址范围的字符串表示。

- Akina

Answer 3

这不是最实用的方法，但它可以解决问题。

我们可以使用LOCATE()函数，该函数需要两个参数，表示要查找的子字符串和要在其中查找的字符串。(locate函数参考: https://www.oreilly.com/library/view/mysql-cookbook/0596001452/ch04s06.html)

将传递的第一个参数是列ip_address，第二个参数将是您的IP（例如：1.2.3.4）。

SELECT location
FROM that_table
WHERE LOCATE(ip_address, 'the_ip') >= 1;

如果我们得到的数字大于或等于一，那么就意味着找到了匹配，因此该IP地址将会有一个位置。

- Victor Santizo

---

你忽略了一个重要的细节。如果他与IP地址进行比较，将找不到匹配项。OP指出该表仅包含前3个八位字节。你的答案应该反映出一种去掉IP地址最后一个八位字节的方法。 - gview

修改了答案，没有注意到那个细节。现在应该可以工作了。 - Victor Santizo

谢谢，它有效。我以前从未尝试过locate()函数。 - Amirul Fahmi