使用IP自动填充表，具体取决于CIDR网络掩码

1 回答

• 0

  Procedure:

  DELIMITER $$
  CREATE PROCEDURE populateIpProcedure(cidrID INT)
  BEGIN
    DECLARE my_cidr_id INT;
    DECLARE my_cidr_str VARCHAR(25);
    DECLARE my_initial_ip VARCHAR(15);
    DECLARE my_initial_ip_int INT;
    DECLARE my_ip_limit INT;
    DECLARE my_loop_variable INT DEFAULT 0;
  
    SET my_cidr_id = cidrID;
    SELECT cidr INTO my_cidr_str FROM cidr WHERE id_cidr = my_cidr_id;  
    SELECT 
      SUBSTRING_INDEX(my_cidr_str,'/',1),SUBSTRING_INDEX(my_cidr_str,'/',-1)+0 INTO my_initial_ip, my_ip_limit;
  
    SET my_ip_limit = (SELECT POWER(2, (32-my_ip_limit)));
    SELECT INET_ATON(my_initial_ip) INTO my_initial_ip_int;
  
  
      WHILE my_loop_variable < my_ip_limit DO 
          INSERT INTO ip(cidr_id,ip) SELECT my_cidr_id, INET_NTOA(my_initial_ip_int+my_loop_variable);
      SET my_loop_variable := my_loop_variable + 1;
    END WHILE;
  END$$
  DELIMITER ;
  

  上面的过程将 cidr id作为参数 .

  然后它通过给定的id从 cidr 表中检索记录 .

  稍后它会将此cidr下的所有ips插入到 ip 表中 .

  Test:

  CALL populateProcedure(1);
  

  这将在此ip系列 14.44.182.0/24 下的 ip 表中插入256个条目

  Test Schema & Data:

  DROP TABLE IF EXISTS `cidr`;
  CREATE TABLE `cidr` (
    `id_cidr` int(11) NOT NULL AUTO_INCREMENT,
    `cidr` varchar(25) NOT NULL,
    PRIMARY KEY (`id_cidr`)
  ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
  
  INSERT INTO `cidr` VALUES ('1', '14.44.182.0/24');
  INSERT INTO `cidr` VALUES ('2', '26.232.49.0/22');
  
  DROP TABLE IF EXISTS `ip`;
  CREATE TABLE `ip` (
    `id_ip` int(11) NOT NULL AUTO_INCREMENT,
    `cidr_id` int(11) NOT NULL,
    `ip` char(15) NOT NULL,
    PRIMARY KEY (`id_ip`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  

  回复于 2024-04-20T18:04:52+08:00