Do you SQL

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View


Now what do you suppose might be the most appropriate data type for  
storing an IP-Address?

Thoughts (considered and/or random) most welsome.

William Tasso

Re: Do you SQL

__/ [ William Tasso ] on Friday 17 March 2006 17:49 \__

Quoted text here. Click to load it

IP addresses are essentially bits, but to use some abstracting, go with
numbers. Strings will not allow you to manipulate the data much (unless
converted to numbers, which needs string validation). For example, think of
operations like "find all IP address on the same subnet (e.g. C-block).

Also remember IPv6, so leave enough space for that. Look at existing systems
for inspiration. WordPress is the only one which I am sufficiently familiar
with. I have just run phpMyAdmin. WordPress stores IP addresses as follows:

Field                   Attributes      Null
comment_author_IP       varchar(100)    No              

You are very welsome (sic) *smile*,


Roy S. Schestowitz      |    "In hell, treason is the work of angels"  |    SuSE Linux          PGP-Key: 0x74572E8E
  5:50pm  up 9 days 10:27,  7 users,  load average: 0.25, 0.36, 0.45 - Open Source knowledge engine project

Re: Do you SQL

And lo, William Tasso didst speak in alt.www.webmaster:

Quoted text here. Click to load it

Emphasis on convenience or table space?  Personally, I would just store  
them as text, but zero-fill each octet so that the column can be easily  
sorted.  That would make for 15 bytes an entry, but you could even do away  
with the dots if you zero-fill, leaving 12 bytes.  You could even reduce  
this to a (theoretical) maximum of 39 bits by storing the collapsed IP as  
an integer.

If you're aiming for space, you could convert it natively to decimal and  
store it in a (again theoretical) maximum of 33 bits.  It would still sort  
nicely this way, but you'd need to translate it with a mathematical  
algorithm going in and out.

Just some ideas.


The technical axiom that nothing is impossible sinisterly implies the  
pitfall corollary that nothing is ridiculous.
- - Orca Search: Full-featured  
spider and site-search engine

Re: Do you SQL

On Fri, 17 Mar 2006 17:49:11 -0000, "William Tasso"

Quoted text here. Click to load it

Run length encoded decimal.




What's "welsome" ? I thought I had the copyright on typos here?


More seriously, it depends on how you want to access the data (you
already know that) but I like text, so I'd use a fixed length text

Re: Do you SQL

On Fri, 17 Mar 2006 17:49:11 -0000, William Tasso put finger to
keyboard and typed:

Quoted text here. Click to load it

Depends on what you want to do with the information. If it's primarily
intended to be human-readable (eg, as a log file), then CHAR(16) or
VARCHAR(16) is the obvious choice. If you want to do any kind of
mathematical manipulation, then converting it to decimal before
storing it in an INT UNSIGNED field might be more useful.

Visit: - fun and games with Google!
Listen: - you'll love it!

Re: Do you SQL

On Fri, 17 Mar 2006 17:49:11 -0000
Quoted text here. Click to load it
I store them as decimal equivalents (integer) in MySQL
(INET_ATON(dot-quad-IP)). This provides the most functionality IMO
including indexing on them and sorting them. I reverse them for octet
presentation with INET_NTOA(integer).

                    Displayed Email Address is a SPAM TRAP
           Eliminate Spam:
          Multi-RBL Check:
            Zombie Graphs:

Re: Do you SQL

Quoted text here. Click to load it

I think the other replies have probably covered everything.

For what its worth, I store it as a string, but that's just for convenience
and its for a system with a low number of records. If I needed to store it
for a system with lots of records then I'd use an integer type. No idea how
I'd store an IPv6 address (a string would be very tempting), but I've not
needed to cross that hurdle.
Brian Cryer

Re: Do you SQL

Fleeing from the madness of the Chaos jungle
and said:

Quoted text here. Click to load it

Thanks all - fwiw: I decided (for now, at least) to store them as  

To address some of the points raised ....
o storage space is not a concern here
o usage will be low
o the number of addresses is finite and each will constrained to a  
o IPv6 - I'll do what every other system does - add an extra column

William Tasso

Site Timeline