选择IP范围内的CIDR [英] Select CIDR that is in range of IP
问题描述
所以我有一个IP，例如45.76.255.14，并且我有一个表，其中CIDR行存储为单个varchar，
如何选择该IP地址范围内的CIDR。例如45.76.255.14/31
因此，从理论上讲：选择IP地址范围内的CIDR
将点分四进制表示的IP地址存储在 VARCHAR 中不是最佳的存储方式，因为点分四进制是一种对人类友好的表示形式一个不适合数据库索引的32位无符号整数。但是有时从根本上来说更方便，并且在小范围内，查询需要进行表扫描这一事实通常不是问题。
MySQL存储函数是一种将相对复杂的逻辑封装在可以在查询中引用的简单函数后面的好方法，这可能导致更易于理解的查询并减少复制/粘贴错误。
因此，这是我编写的名为 find_ip4_in_cidr4（）的存储函数。它的工作原理与内置函数 FIND_IN_SET（） -您给它一个值，然后给它一个集合（CIDR规范），它返回一个值来指示是否值在集合中。
首先，说明作用中的函数：
如果地址是在块内，返回前缀长度。为什么返回前缀长度？非零整数是 true，因此我们可以返回 1 ，但是如果要对匹配结果进行排序以找到多个匹配前缀中最短或最长的可以 ORDER BY 函数的返回值。
mysql> SELECT find_ip4_in_cidr4（'203.0.113.123'，'203.0.113.0/24'）;
+ --------------------------------------------- -------- +
| find_ip4_in_cidr4（'203.0.113.123'，'203.0.113.0/24'）|
+ --------------------------------------------- -------- +
| 24 |
+ --------------------------------------------- -------- +
设置1行（0.00秒）
mysql> SELECT find_ip4_in_cidr4（'192.168.100.1'，'192.168.0.0/16'）;
+ --------------------------------------------- -------- +
| find_ip4_in_cidr4（'192.168.100.1'，'192.168.0.0/16'）|
+ --------------------------------------------- -------- +
| 16 |
+ --------------------------------------------- -------- +
集合中的1行（0.00秒）
不在街区吗？返回0（假）。
mysql> SELECT find_ip4_in_cidr4（'192.168.100.1'，'203.0.113.0/24'）;
+ --------------------------------------------- -------- +
| find_ip4_in_cidr4（'192.168.100.1'，'203.0.113.0/24'）|
+ --------------------------------------------- -------- +
| 0 |
+ --------------------------------------------- -------- +
设置1行（0.00秒）
mysql> SELECT find_ip4_in_cidr4（'192.168.100.1'，'192.168.0.0/24'）;
+ --------------------------------------------- -------- +
| find_ip4_in_cidr4（'192.168.100.1'，'192.168.0.0/24'）|
+ --------------------------------------------- -------- +
| 0 |
+ --------------------------------------------- -------- +
集合中的1行（0.00秒）
全零地址有一种特殊情况，我们返回-1（仍然为 true，但保留排序顺序）：
mysql> SELECT find_ip4_in_cidr4（'192.168.100.1'，'0.0.0.0/0'）;
+ --------------------------------------------- --- +
| find_ip4_in_cidr4（'192.168.100.1'，'0.0.0.0/0'）|
+ --------------------------------------------- --- +
| -1 |
+ --------------------------------------------- --- +
集合中的1行（0.00秒）
无意义参数返回null：
mysql> SELECT find_ip4_in_cidr4（'234.467.891.0'，'192.168.0.0/24'）;
+ --------------------------------------------- -------- +
| find_ip4_in_cidr4（’234.467.891.0’，‘192.168.0.0/24’）|
+ --------------------------------------------- -------- +
| NULL |
+ --------------------------------------------- -------- +
集合中的1行（0.00秒）
现在，代码解码器：
DELIMITER $$
如果存在`find_ip4_in_cidr4` $$ $
CREATE DEFINER =`mezzell` @`％`函数`find_ip4_in_cidr4`（
_address VARCHAR（15），
_block VARCHAR（18）
）返回TINYINT
确定/ *对于给定的输入，此函数始终返回相同的输出* /
CONTAINS SQL / *该函数不会从表中读取或写入表* /
BEGIN
-给定一个IPv4地址和一个cidr规范，
-对于-1.0.0 / 0.0 / 0内的有效地址返回-1
-如果该地址在块内，则返回前缀长度
- -如果地址在块外，则返回0，否则返回
；否则返回null
声明_ip_aton INT UNSIGNED DEFAULT INET_ATON（_address）;
DECLARE _cidr_aton INT UNSIGNED DEFAULT INET_ATON（SUBSTRING_INDEX（_block，’/’，1））;
DECLARE _prefix TINYINT UNSIGNED DEFAULT SUBSTRING_INDEX（_block，’/’，-1）;
DECLARE _bitmask INT UNSIGNED DEFAULT（0xFFFFFFFF<<（32-_prefix））& 0xFFFFFFFF;
RETURN CASE / *在CASE表达式中使用第一个匹配项，而不是最佳匹配项* /
_ip_aton为NULL或_cidr_aton IS为NULL或/ *健全性检查* /
_prefix为NULL或_bitmask为NULL或
_prefix不介于0和32或
之间（_prefix = 0 AND _cidr_aton！= 0）THEN NULL
当_cidr_aton = 0 AND _bitmask = 0 THEN -1
_ip_aton& _bitmask = _cidr_aton& _bitmask然后_prefix / *这是唯一需要的实际测试* /
ELSE 0 END;
END $$
DELIMITER;
一个问题并不特定于存储函数，而是适用于大多数RDBMS平台上的大多数函数是当在 WHERE 中将一列用作函数的参数时，服务器无法通过该函数向后看以使用索引来优化查询。 / p>
So I have an IP like 45.76.255.14, and I have a table with rows of CIDR stored as a single varchar, how would I select CIDRs that are in the range of that IP address. For example 45.76.255.14/31
So in theory: select CIDR where in range of IP
Storing IP addresses in dotted quad notation in a VARCHAR is not the most optimal way of storing them, since dotted-quad is a human friendly representation of a 32 bit unsigned integer that doesn't lend itself to database indexing. But sometimes it's fundamentally more convenient, and at small scale, the fact that queries require a table scan isn't usually a problem.
MySQL Stored Functions are a good way of encapsulating relatively complex logic behind a simple function that can be referenced in a query, potentially leading to easier-to-understand queries and reducing copy/paste errors.
So, here's a stored function I wrote called find_ip4_in_cidr4(). It works somewhat similarly to the built-in function FIND_IN_SET() -- you give it a value and you give it a "set" (CIDR spec) and it returns a value to indicate whether the value is in the set.
First, an illustration of the function in action:
If the address is inside the block, return the prefix length. Why return the prefix length? Non-zero integers are "true," so we could just return 1, but if you want to sort the matching results to find the shortest or longest of multiple matching prefixes, you can ORDER BY the return value of the function.
mysql> SELECT find_ip4_in_cidr4('203.0.113.123','203.0.113.0/24');
+-----------------------------------------------------+
| find_ip4_in_cidr4('203.0.113.123','203.0.113.0/24') |
+-----------------------------------------------------+
| 24 |
+-----------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT find_ip4_in_cidr4('192.168.100.1','192.168.0.0/16');
+-----------------------------------------------------+
| find_ip4_in_cidr4('192.168.100.1','192.168.0.0/16') |
+-----------------------------------------------------+
| 16 |
+-----------------------------------------------------+
1 row in set (0.00 sec)
Not in the block? That returns 0 (false).
mysql> SELECT find_ip4_in_cidr4('192.168.100.1','203.0.113.0/24');
+-----------------------------------------------------+
| find_ip4_in_cidr4('192.168.100.1','203.0.113.0/24') |
+-----------------------------------------------------+
| 0 |
+-----------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT find_ip4_in_cidr4('192.168.100.1','192.168.0.0/24');
+-----------------------------------------------------+
| find_ip4_in_cidr4('192.168.100.1','192.168.0.0/24') |
+-----------------------------------------------------+
| 0 |
+-----------------------------------------------------+
1 row in set (0.00 sec)
There's a special case for the all-zeroes address, we return -1 (still "true", but preserves the sort order):
mysql> SELECT find_ip4_in_cidr4('192.168.100.1','0.0.0.0/0');
+------------------------------------------------+
| find_ip4_in_cidr4('192.168.100.1','0.0.0.0/0') |
+------------------------------------------------+
| -1 |
+------------------------------------------------+
1 row in set (0.00 sec)
Nonsense arguments return null:
mysql> SELECT find_ip4_in_cidr4('234.467.891.0','192.168.0.0/24');
+-----------------------------------------------------+
| find_ip4_in_cidr4('234.467.891.0','192.168.0.0/24') |
+-----------------------------------------------------+
| NULL |
+-----------------------------------------------------+
1 row in set (0.00 sec)
Now, teh codez:
DELIMITER $$
DROP FUNCTION IF EXISTS `find_ip4_in_cidr4` $$
CREATE DEFINER=`mezzell`@`%` FUNCTION `find_ip4_in_cidr4`(
_address VARCHAR(15),
_block VARCHAR(18)
) RETURNS TINYINT
DETERMINISTIC /* for a given input, this function always returns the same output */
CONTAINS SQL /* the function does not read from or write to tables */
BEGIN
-- given an IPv4 address and a cidr spec,
-- return -1 for a valid address inside 0.0.0.0/0
-- return prefix length if the address is within the block,
-- return 0 if the address is outside the block,
-- otherwise return null
DECLARE _ip_aton INT UNSIGNED DEFAULT INET_ATON(_address);
DECLARE _cidr_aton INT UNSIGNED DEFAULT INET_ATON(SUBSTRING_INDEX(_block,'/',1));
DECLARE _prefix TINYINT UNSIGNED DEFAULT SUBSTRING_INDEX(_block,'/',-1);
DECLARE _bitmask INT UNSIGNED DEFAULT (0xFFFFFFFF << (32 - _prefix)) & 0xFFFFFFFF;
RETURN CASE /* the first match, not "best" match is used in a CASE expression */
WHEN _ip_aton IS NULL OR _cidr_aton IS NULL OR /* sanity checks */
_prefix IS NULL OR _bitmask IS NULL OR
_prefix NOT BETWEEN 0 AND 32 OR
(_prefix = 0 AND _cidr_aton != 0) THEN NULL
WHEN _cidr_aton = 0 AND _bitmask = 0 THEN -1
WHEN _ip_aton & _bitmask = _cidr_aton & _bitmask THEN _prefix /* here's the only actual test needed */
ELSE 0 END;
END $$
DELIMITER ;
An issue that is not specific to stored functions, but rather applies to most functions on most RDBMS platforms is that when a column is used as an argument to a function in WHERE, the server can't "look backwards" through the function to use an index to optimize the query.
这篇关于选择IP范围内的CIDR的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
