Auto-populate table with IP's depending on CIDR netmask

Question

I have 2 tables cidr and ip.

In the first one I store CIDR netmask's. (2 column table, id_cidr, cidr), here is an example (the values are fictional):

 id_cidr |     cidr
---------+----------------
    1    | 14.44.182.0/24
---------+----------------
    2    | 26.232.49.0/22

In the second one I want to store the ip's within each cidr netmask (3 column table, id_ip, cidr_id, ip), here is an example (the values are fictional):

  id_ip  | cidr_id  |     ip
---------+--------------------------
    1    |    1     | 14.44.182.0
---------+--------------------------
    2    |    1     | 14.44.182.1
---------+--------------------------
    3    |    1     | 14.44.182.2
---------+--------------------------
   ...   |    1     | ...
---------+--------------------------
   256   |    1     | 14.44.182.255
---------+--------------------------
   257   |    2     | 26.232.48.0
---------+--------------------------
   258   |    2     | 26.232.48.1
---------+--------------------------
   259   |    2     | 26.232.48.2
---------+--------------------------
   ...   |    2     | ...
---------+--------------------------
  1280   |    2     | 26.232.51.255

What I want to achieve is the following: whenever I addd a cidr netmask into the cidr table I want my ip table to auto-populate the fields with all the range of ip's within that cidr netmask. Is there any way to achieve this in mysql?

Note: To convert a cidr netmask to a range of ip's click here

The first ip address generated under this `ip series = 26.232.49.0/22` is `26.232.48.0`. is it expected? And also tell us do you prefer calling a procedure to populate the second table? — 1000111, Aug 21 '16 at 11:21
You are right, I made a mistake, I corrected that. Yes I guess some procedure or any other solution besides PHP. So from mysql and I ask this because I am not experienced with advanced mysql. — paulalexandru, Aug 21 '16 at 15:00
What you want sounds like you want trigger for that. But calling procedure inside a trigger is discouraged. But if I were you then I would do it this way : 1) Insert into `cidr` table 2) get the last insert id 3) call the procedure providing the last inserted cidr id and the `cidr` itself in order to insert into the `ip` table — 1000111, Aug 21 '16 at 15:52
Is this of use to you [here](http://stackoverflow.com/a/32020220) of mine, in particular the table with 1 row showing [this](http://i.imgur.com/uPXdfRo.jpg) ... less housekeeping — Drew, Aug 21 '16 at 16:28

1000111 · Answer 1 · 2016-08-22T04:30:23.993

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 ;

The procedure above takes a cidr id as an argument.

Then it retrieves the record from cidr table by the given id.

Later it inserts all the ips under this cidr into to the ip table.

Test:

CALL populateProcedure(1);

This will insert 256 entries in ip table under this ip series 14.44.182.0/24

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;

Auto-populate table with IP's depending on CIDR netmask

1 Answers1