我仔细研究了MySQL doc页面,然后在那里发现了一个符合你需求的帖子:
SELECT '10.0.0.1' AS IP0,
( SUBSTRING_INDEX( '10.0.0.1', '.', 1 ) * 16777216 +
SUBSTRING_INDEX(SUBSTRING_INDEX( '10.0.0.1', '.', 2 ),'.',-1) * 65536 +
SUBSTRING_INDEX(SUBSTRING_INDEX( '10.0.0.1', '.', -2 ),'.',1) * 256 +
SUBSTRING_INDEX( '10.0.0.1', '.', -1 )
),
'10.0.255.1' AS IP1,
( SUBSTRING_INDEX( '10.0.255.1', '.', 1 ) * 16777216 +
SUBSTRING_INDEX(SUBSTRING_INDEX( '10.0.255.1', '.', 2 ),'.',-1) * 65536 +
SUBSTRING_INDEX(SUBSTRING_INDEX( '10.0.255.1', '.', -2 ),'.',1) * 256 +
SUBSTRING_INDEX( '10.0.255.1', '.', -1 )
) AS IP2Num1返回每个IP的数值:
Ip: | 10.0.0.1 | 10.0.255.1 |
--------------------------------
Num: | 167772161 | 167837441 |换一种说法:
SELECT foo
FROM bar.foobar
WHERE ( SUBSTRING_INDEX( ipField, '.', 1 ) * 16777216 +
SUBSTRING_INDEX(SUBSTRING_INDEX( ipField, '.', 2 ),'.',-1) * 65536 +
SUBSTRING_INDEX(SUBSTRING_INDEX( ipField, '.', -2 ),'.',1) * 256 +
SUBSTRING_INDEX( ipField, '.', -1 )
) BETWEEN 167772161 AND 167837441;
好吧,你可以这样做:
WHERE ipField LIKE '10.0.%.1'
AND (CAST
(REPLACE(SUBSTRING_INDEX(ipField,'.',3),
CONCAT(SUBSTRING_INDEX(ipField ,'.',2),'.')
,'')
AS UNSIGNED) BETWEEN 0 AND 255)我用以下查询测试过:
SELECT IF ('10.0.12.1' LIKE '10.0.%.1'
AND (CAST
(REPLACE( SUBSTRING_INDEX('10.0.12.1','.',3),
CONCAT(SUBSTRING_INDEX('10.0.12.1','.',2),'.')
,'')
AS UNSIGNED) BETWEEN 0 AND 255),
TRUE,
FALSE);并且它返回1,但老实说,这有点不对劲,不是吗?为什么不简单:
WHERE ipField REGEXP '10\\.0\\.[0-9]{1,3}\\.1'你可以调整一下这个表达式,但基本上,在我看来,这是查询这些IP地址的最简单方法。
有关MySQL字符串函数的更多信息(这几乎总是很难写) ,请参阅the docs here