Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have to store the IPv4/v6 address and networks(CIDR notation) in a MySQL database. Unfortunately, I am constrained to use Mysql.

So, I'm looking for the equivalent of the inet type of PostgreSql. :

The inet type holds an IPv4 or IPv6 host address, and optionally its subnet, all in one field. The subnet is represented by the number of network address bits present in the host address (the "netmask"). If the netmask is 32 and the address is IPv4, then the value does not indicate a subnet, only a single host. In IPv6, the address length is 128 bits, so 128 bits specify a unique host address.

The input format for this type is address/y where address is an IPv4 or IPv6 address and y is the number of bits in the netmask. If the /y portion is missing, the netmask is 32 for IPv4 and 128 for IPv6, so the value represents just a single host. On display, the /y portion is suppressed if the netmask specifies a single host.

I'm aware of the followings functions of Mysql 5.6 INET_ATON(), INET_NTOA(), INET6_ATON(), INET6_NTOA(). But I use Mysql 5.5... So, forget INET6_xxxx.

I'm also aware of the following question "Storing IP address" but it doesn't deals with network CIDR notation.

How you would implement the equivalent of inet type in Mysql 5.5?

  • 3 columns : Network (2xBIGINT), CIDR (TINYINT) ?
  • string ? (@Phil)
share|improve this question
1  
Do you need to do any kind of calculations with the addresses within the database? If not, just store them as strings. What sort of querying will you be doing on the addresses themselves? –  FreshPhilOfSO Feb 18 '13 at 4:16
    
I need to check if a list of IPs is in one of the network. So, the answer is yes. I can also retrieve all rows and to calculation out of the DB. –  Yohann Feb 18 '13 at 4:41
add comment

1 Answer

First: there is no equivalent of the inet type of PostgreSql in MySQL yet.

You should just use the string type to store all kind of IP addresses. if you need them all in a certain format, convert them before inserting them into the DB.

If you need to do calculations with them, as you mentioned INET_ATON() and INET_NTOA() are the needed functions to be used.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.