在mySQL中使用填充数据自动创建表或行

Question

I'm looking to create a basic IP management solution using a web front end with PHP and MySQL. I am not too familiar with PHP or MySQL but I would like to know if it would be possible to do the following:

When a user inserts a new subnet range into the database (e.g 192.168.1.0/24) using a HTML form, would it be possible for that to trigger another create table (e.g. ip_addresses) that would auto-populate the table with the subnet range (e.g. 192.168.1.1, 1.2, 1.3 and so on).

Apologies if I'm being too vague - thanks

Answer 1

Though it would be possible to do what you are asking, I would advise against it. In MySQL, it is common to store IP addresses as long integers, rather than as their string representation as a dotted quad. MySQL provides two functions, INET_ATON() and its counterpart INET_NTOA() to convert IP addresses into long integers.

The structure I would recommend would be to store the starting and ending integer representation of the subnets. That way, it becomes possible, if necessary to query for addresses residing between the endpoints. Also it allows you to store incomplete subnets.

From MySQL docs:

mysql> SELECT INET_ATON('10.0.5.9');
        -> 167773449
mysql> SELECT INET_NTOA(167773449);
        -> '10.0.5.9'

You can query with statements like:

SELECT * ipRanges WHERE INET_ATON('192.168.1.99') BETWEEN startIpNum AND endIpNum;