如何在mysql中匹配IP地址？

Question

例如，我有一个存储数据如下的列。

Apple
12.5.126.40
Smite
Abby
127.0.0.1
56.5.4.8
9876543210
Notes

如何筛选仅包含 IP 格式数据的行？

我已经尝试过 '^[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}$'，但我不知道为什么它也匹配了 9876543210。

- Abby Chau Yu Hoi

Answer 1

你需要使用REGEXP来匹配IP地址的点分十进制模式。

SELECT *
FROM yourtable
WHERE 
  thecolumn REGEXP '^[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}$'

从技术角度来看，这个筛选器将匹配那些无效的IP地址，比如说999.999.999.999，但这可能并不重要。重要的是，你需要修正你的数据，使得IP地址被单独存储在一个列中，而不是和其他类型的数据混杂在同一列。

mysql> SELECT '9876543210' REGEXP '^[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}$';
+---------------------------------------------------------------------------+
| '9876543210' REGEXP '^[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}$' |
+---------------------------------------------------------------------------+
|                                                                         0 |
+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT '987.654.321.0' REGEXP '^[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}$';
+------------------------------------------------------------------------------+
| '987.654.321.0' REGEXP '^[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}$' |
+------------------------------------------------------------------------------+
|                                                                            1 |
+------------------------------------------------------------------------------+

另一种方法是尝试通过MySQL的INET_ATON()函数将IP地址转换为长整型。 无效的地址将返回NULL。

这种方法可能比正则表达式更有效。

您可以将其嵌入到WHERE条件中，例如：WHERE INET_ATON(thecolumn) IS NOT NULL

SELECT INET_ATON('127.0.0.1');
+------------------------+
| INET_ATON('127.0.0.1') |
+------------------------+
|             2130706433 |
+------------------------+

SELECT INET_ATON('notes');
+--------------------+
| INET_ATON('notes') |
+--------------------+
|               NULL |
+--------------------+

SELECT INET_ATON('56.99.9999.44');
+----------------------------+
| INET_ATON('56.99.9999.44') |
+----------------------------+
|                       NULL |
+----------------------------+

- Michael Berkowski

---

我已经尝试使用'^[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}$'，但我不知道为什么它也匹配9876543210。 - Abby Chau Yu Hoi

@AbbyChauYuHoi 将反斜杠加倍。在处理MySQL中的正则表达式时，我总是忘记这一点。 - Michael Berkowski

Answer 2

IS_IPV4() 是一个 MySQL 原生函数，它可以帮助您检查一个数值是否为有效的 IP Version 4 地址。

SELECT *
FROM ip_containing_table
WHERE IS_IPV4(ip_containing_column);

我没有数据，但我认为这一定是最可靠和高效的方法。

还有类似的本地函数可以检查IP版本6等。

- kev

Answer 3

这可能不是最有效的方法，也不是正则表达式，但应该可以工作：

SELECT col1 FROM t1 WHERE col1 LIKE '%.%.%.%';

- Explosion Pills

Answer 4

你还可以使用有用的函数inet_aton()

SELECT *
FROM yourtable
WHERE inet_aton(thecolumn) is not null

- Mohammad M. Shahbazi

Answer 5

冗长但运行良好：

mysql> SELECT '1.0.0.127' regexp '^([0-1]?[0-9]{1,2}|2[0-4][0-9]|25[0-5])\\.([0-1]?[0-9]{1,2}|2[0-4][0-9]|25[0-5])\\.([0-1]?[0-9]{1,2}|2[0-4][0-9]|25[0-5])\\.([0-1]?[0-9]{1,2}|2[0-4][0-9]|25[0-5])$';

- itlunch