Which MySQL Datatype to Use for an Ip Address

Which MySQL datatype to use for an IP address?

Since IPv4 addresses are 4 byte long, you could use an INT (UNSIGNED) that has exactly 4 bytes:

`ipv4` INT UNSIGNED

And INET_ATON and INET_NTOA to convert them:

INSERT INTO `table` (`ipv4`) VALUES (INET_ATON("127.0.0.1"));
SELECT INET_NTOA(`ipv4`) FROM `table`;

For IPv6 addresses you could use a BINARY instead:

`ipv6` BINARY(16)

And use PHP’s inet_pton and inet_ntop for conversion:

'INSERT INTO `table` (`ipv6`) VALUES ("'.mysqli_real_escape_string(inet_pton('2001:4860:a005::68')).'")'
'SELECT `ipv6` FROM `table`'
$ipv6 = inet_pton($row['ipv6']);

What is the ideal datatype to store IP address in a mysql table?

IPv4 addresses use 32 bits (4 bytes), IPv6 addresses use 128 bits (16 bytes). Thus, you can store their binary representation in BINARY(16) or VARBINARY(16) fields.

Also see my answer to the question IP address storing in mysql database. It also provides comments why you would choose one over the other.

What type should I store IP addresses for MySQL?

I presume you're only interested in IPv4 addresses, not IPv6.

I would use an INT UNSIGNED for the column, and then use INET_ATON and INET_NTOA to convert back and forth between the textual representation and the int value.

mysql> SELECT INET_ATON('192.168.10.50');
+----------------------------+
| INET_ATON('192.168.10.50') |
+----------------------------+
|                 3232238130 |
+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT INET_NTOA(3232238130);
+-----------------------+
| INET_NTOA(3232238130) |
+-----------------------+
| 192.168.10.50         |
+-----------------------+
1 row in set (0.00 sec)

IP Address datatype

varchar(15) if you just want to store the IP and you don't have much data.

An IPv4 address is actually a 32 bit integer that can be converted to/from it's numeric form to display form. (The same is true for IPv6: it's a 128-bit integer).

If you stored it as binary(4) it would take up less space (4 bytes instead of 15); you could do IP range checks in your queries; and it would be faster to use as a condition in a select statement since the DB wouldnt have to do string comparisons.

The downside is you would need to convert the IP to it's integer form to use it with the DB; and you would have to convert it to it's display form before showing it to users. If you search a bit, I'm sure you'll find prewritten functions for this in whatever language you're using, but it's an extra step.

I would say either is ideal depending on your circumstances.

Most efficient way to store IP Address in MySQL

For IPv4 addresses, you may want to store them as an int unsigned and use the INET_ATON() and INET_NTOA() functions to return the IP address from its numeric value, and vice versa.

Example:

SELECT INET_ATON('127.0.0.1');

+------------------------+
| INET_ATON('127.0.0.1') |
+------------------------+
|             2130706433 | 
+------------------------+
1 row in set (0.00 sec)

SELECT INET_NTOA('2130706433');

+-------------------------+
| INET_NTOA('2130706433') |
+-------------------------+
| 127.0.0.1               | 
+-------------------------+
1 row in set (0.02 sec)

What is the most appropriate data type for storing an IP address in SQL server?

Storing an IPv4 address as a binary(4) is truest to what it represents, and allows for easy subnet mask-style querying. However, it requires conversion in and out if you are actually after a text representation. In that case, you may prefer a string format.

A little-used SQL Server function that might help if you are storing as a string is PARSENAME, by the way. Not designed for IP addresses but perfectly suited to them. The call below will return '14':

SELECT PARSENAME('123.234.23.14', 1)

(numbering is right to left).

Store IP into mysql database

For handling and storing both IPv4 and IPv6 addresses, you can use datatype VARBINARY(16).

In version 5.6, MySQL (finally!) introduced conversion functions for IPv6 addresses: INET6_ATON, so you don't have to do the conversion in your application.

If you are handling only IPv4 addresses, you can continue to use the INET6_ATON function, and BINARY(4) is a suitable datatype for storing it.

Conveniently, the inverse functions, for converting from the binary representation back to dotted decimal form (for IPv4 addresses) or the hex colon form (for IPv6 addresses) are also available.

Reference: https://dev.mysql.com/doc/refman/5.6/en/miscellaneous-functions.html#function_inet6-aton

Q: How to should I make this column decimal if the ip (version 6) can have more than 15 chars ?

A: Don't make the column DECIMAL, make it VARBINARY(16), and convert both the IPv4 dotted decimal representation, and the IPv6 hex colon representation into a binary representatin to store it.

mySQL Length for a IPv4 Address (INT Field Type)

How to store IPv4 has already been answered on StackOverflow (Storing IP address in MySQL database (IPv4 AND IPv6)), so I'm only going to answer the rest of your questions.

• The length of ints is only relevant if you use zerofill: it will pad with zeros until it's your specified length wide. It will not cut anything off, either. Ints are 4 bytes, so they can store anything between -(2^31) and 2^31-1, regarldess of your length specifier.
• IPv4 and IPv6 are not completely interchangable; many of your users will still connect using IPv4 only, or IPv6 only; there is no 'conversion'. Try and make your implementation such that both can be stored. See the link provided.

How to store an IP in mySQL

I would suggest looking at what type of queries you will be running to decide which format you adopt.

Only if you need to pull out or compare individual octets would you have to consider splitting them up into separate fields.

Otherwise, store it as a 4 byte integer. That also has the bonus of allowing you to use the MySQL built-in INET_ATON() and INET_NTOA() functions.

Performance vs. Space

Storage:

If you are only going to support IPv4 addresses then your datatype in MySQL can be an UNSIGNED INT which only uses 4 bytes of storage.

To store the individual octets you would only need to use UNSIGNED TINYINT datatypes, not SMALLINTS, which would use up 1 byte each of storage.

Both methods would use similar storage with perhaps slightly more for separate fields for some overhead.

More info:

• Numeric Type Overview
• Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT

Performance:

Using a single field will yield much better performance, it's a single comparison instead of 4. You mentioned that you will only run queries against the whole IP address, so there should be no need to keep the octets separate. Using the INET_* functions of MySQL will do the conversion between the text and integer representations once for the comparison.

---

Related Topics