From: Dan Nelson
Date: February 11 2003 6:20pm
Subject: Re: IP Addresses -- How to Store
List-Archive: http://lists.mysql.com/mysql/132115
Message-Id: <20030211182032.GB93622@dan.emsphone.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

In the last episode (Feb 11), Aaron Conaway said:
> I'm looking to develop a database of IP addresses for the company and
> have, of course, chosen mySQL as the backend.  I want the app to add
> (remove, etc.) a host, giving its hostname and segment.  The app will
> add the next available address to the database and, looking at the
> segment, provide the subnet mask and default gateway for said new
> host. I've got the db structure written out, but I'm having issues on
> how to store the data like address, subnet mask, default gateway.
> 
> Our network is very diverse, covering many ranges of addresses and,
> more importantly, many subnet masks.  Some are 24bit, some are 16bit,
> and some are 23bit.  What is the best way to store this data in
> mySQL?
> 
> If I do a varchar, then how do I restrict the data to between 0 and
> 255? Also, how would I manipulate any address with a classless mask? 
> I'm thinking storage as binary so I can parse out a byte of binary
> data and present it as a decimal number; this also limits the data to
> numbers between 0 and 255, based on an 8-bit byte.  The problem is
> that I have no clue how to store such.

Store your addresses as INTs, so you would have three fields:
"address", "netmask", and "gateway".  You can either encode the values
yourself, or use mysql's INET_NTOA()/INET_ATON() functions.

-- 
	Dan Nelson
	dnelson@stripped
	sql, query