如何在mysql中匹配IP地址? [英] How to match an ip address in mysql?

问题描述

例如，我有一列存储这样的数据.

For example, I am having a column storing data like this.

Apple
12.5.126.40
Smite
Abby
127.0.0.1
56.5.4.8
9876543210
Notes

如何仅选择具有IP格式数据的行?

How to select out only the rows with data in IP format?

我尝试过使用'^[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}$' 但我不知道为什么它也可以匹配9876543210

I have tried with '^[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}$' but I have no idea why it also matches 9876543210

推荐答案

您将需要使用REGEXP来匹配IP地址点缀的四边形图案.

You're going to need to use REGEXP to match the IP address dotted quad pattern.

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地址与您在此拥有的其他任何数据分开存储在其自己的列中.在一个列中混合使用数据类型几乎总是一个坏主意.

Technically, this will match values that are not valid IP addresses, like 999.999.999.999, but that may not be important. What is important, is fixing your data such that IP addresses are stored in their own column separate from whatever other data you have in here. It is almost always a bad idea to mix data types in one column.

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的

Another method is to attempt to convert the IP address to a long integer via MySQL's INET_ATON() function. An invalid address will return NULL.

此方法可能比正则表达式更有效.

This method is likely to be more efficient than the regular expression.

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

You may embed it in a WHERE condition like: 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 |
+----------------------------+

这篇关于如何在mysql中匹配IP地址?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！